Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22

    Emailing Last Record & exit handling

    Hello.



    I need to be able to send an email with the most recently updated record every time a form is submitted, and save the record. Then, when the form is opened again, I need it to open a new record with blank fields.


    I was using DoCmd.SendObject to email the entire Form, but that sends ALL the records, and I need it to send only the most recent one each time the form is submitted.

    I have non-working code that I've been playing with... I will post it here but I think a complete re-write is necessary, and I'm fine with that.

    Code:
    Private Sub Submit_Click()
     
    'Mandates data entry
    Dim cCont As Control
        For Each cCont In Me.Controls
            If TypeName(cCont) = "TextBox" Then
                If IsNull(cCont) Then
                    MsgBox "Please provide a description for " & cCont.Name & " field(s)!"
                    Exit Sub
                    End If
                End If
            Next cCont
        
        For Each cCont In Me.Controls
            If TypeName(cCont) = "ComboBox" Then
                If IsNull(cCont) Then
                    MsgBox "Please provide a description for " & cCont.Name & " field(s)!"
                    Exit Sub
                    End If
                End If
            Next cCont
     
    'Traps error when form closed without sending email
      Dim db As DAO.Database   
      Dim rs As DAO.Recordset  
          On Error GoTo ErrorHandler
    
      'Code here is the problem.  Goal is to send email of most recent record, then move to a blank record (so the next time form opens its ready to edit), then save and exit the form.
     
    
        DoCmd.GoToRecord acDataForm, "Entry Log", acLast
            DoCmd.SendObject acSendModule, , acFormatXLSX, "email.name@company.com", "", "", "Incident Report", "", True
        DoCmd.GoToRecord , , acNewRec
        DoCmd.Close acForm, "Entry Log", acSaveYes
    
    ExitHandler:   'Clean up as necessary and exit
      Set rs = Nothing  
      Set db = Nothing  
      Exit Sub
    ErrorHandler:
      Select Case Err  'Specific Case statements for errors we can anticipate, the "Else" catches any others
        Case 2501       'Action OpenReport was cancelled.
          
          DoCmd.Hourglass False
          Resume ExitHandler
        Case Else
          MsgBox Err.Description
          DoCmd.Hourglass False
          Resume ExitHandler
      End Select
     
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Possible SendObject is not going to work for you. Here is how I generate and send email with VBA. http://forums.aspfree.com/microsoft-...ro-447084.html

    How many data fields are involved? Maybe build a string of the fields to be body of the email?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    I would need to know how to build that string of fields. I'm fairly new to VBA code, but that looks like it might work.

    There are roughly 20 fields involved.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Build a string with concatenation. It is fairly simple, although tedious. But only have to do it once. Example:

    strBodyString = Me.fieldname1 & " " & Me.fieldname2 & vbCrLf _
    & Me.fieldname3 & " " & Me.fieldname4
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    Ok. What is the "& vbCrLf _" contained in your code and how do I use it?

    Also do you have thoughts regarding my exit handling question? I need it to save the record, move to new record and close the form.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    vbCrLf is carriage return line feed

    Hopefully, this will produce multi-line string so the data isn't just one long line.

    The _ is line continuation character. It just tells the code compiler that this line of code continues on next line. Just makes the code easier to read, otherwise the line of code could continue off to the right out sight and have to scroll to read the entire line.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    Quote Originally Posted by June7 View Post
    vbCrLf is carriage return line feed

    Hopefully, this will produce multi-line string so the data isn't just one long line.

    The _ is line continuation character. It just tells the code compiler that this line of code continues on next line. Just makes the code easier to read, otherwise the line of code could continue off to the right out sight and have to scroll to read the entire line.

    Would
    Code:
     
    
    strBodyString = Me.fieldname1 & " " & Me.fieldname2 & vbCrLf _
    & Me.fieldname3 & " " & Me.fieldname4
    Go under
    Code:
    .HTMLBody = "text here"
    ?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    .HTMLBody = strBodyString

    or

    .HTMLBody = Me.fieldname1 & " " & Me.fieldname2 & vbCrLf _
    & Me.fieldname3 & " " & Me.fieldname4

    or if you want to say more

    .HTMLBody = "Here is latest data: " & vbCrLf & vbCrLf & strBodyString
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    I am getting an error I can't seem to resolve.

    "Compile error: User-defined type not defined."

    It highlights the code section "appOutlook As Outlook.Application."

    Since I don't know how your code was originally intended to run, and am fairly novice with VBA in general, I'm not sure how to resolve this.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Need to set a Reference. From the VBA editor menu Tools > References scroll down to Microsoft Outlook 14.0 Object Library and check it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    Ok. What does that do? The error did not go away afterwards.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Post your revised procedure. Let's see how you implemented my sample code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    Code:
     
    Private Sub Submit_Click()
     
    'Mandates data entry before running remaining code
    Dim cCont As Control
        For Each cCont In Me.Controls
            If TypeName(cCont) = "TextBox" Then
                If IsNull(cCont) Then
                    MsgBox "Please provide a description for " & cCont.Name & " field(s)!"
                    Exit Sub
                    End If
                End If
            Next cCont
        
        For Each cCont In Me.Controls
            If TypeName(cCont) = "ComboBox" Then
                If IsNull(cCont) Then
                    MsgBox "Please provide a description for " & cCont.Name & " field(s)!"
                    Exit Sub
                    End If
                End If
            Next cCont
     
    
    'Traps error when form closed without sending email
      Dim db As DAO.Database   'These two lines are just an example
      Dim rs As DAO.Recordset  'your code may not include them
          On Error GoTo ErrorHandler
    
      'Code here
      
      
      
      'open Outlook, attach zip folder or file, send e-mail
    Dim appOutlook As Outlook.Application
    Dim MailOutlook As Outlook.MailItem
    Set appOutlook = CreateObject("Outlook.Application")
    Set MailOutlook = appOutlook.CreateItem(olMailItem)
    With MailOutlook
        .BodyFormat = olFormatRichText
        .To = "email addresses"
        ''.cc = ""
        ''.bcc = ""
        .Subject = "Incident Report"
        .HTMLBody = Me.[Incident Date] & " " & Me.[Incident Start Time] & vbCrLf _
    & Me.[Location] & " " & Me.[Incident End Time]
        .Attachments.Add ("path\filename")
       ' .DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
        .send
    End With
     
     
    ExitHandler:   'Supposed to Clean up as necessary and exit.  This code needs reworking
      Set rs = Nothing  'These two lines are just an example
      Set db = Nothing  'your code may not include them
      Exit Sub
    ErrorHandler:
      Select Case Err  'Specific Case statements for errors we can anticipate, the "Else" catches any others.
        Case 2501       'Action OpenReport was cancelled.
          
          DoCmd.Hourglass False
          Resume ExitHandler
        Case Else
          MsgBox Err.Description
          DoCmd.Hourglass False
          Resume ExitHandler
      End Select
     
    End Sub

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That looks like my working code. Don't know why it is erroring on that line. I don't think any other References are needed but try adding this one if not already (I think Access loads it by default with new project) - Microsoft Office 14.0 Object Library. I have not run this in 2010, it is from my project at work where I am in 2007.

    Unrelated to the error, probably want to comment out the .Attachments.Add line and I assume you left "email addresses" in just for this post instead of real addresses.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    malamute20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    22
    Yes, I pulled the email out as I don't want my corporate leaders' info being publicized, due to the potential for security compromise.

    Attachments add line I thought may be useful if my boss wants any other information linked with the reports being sent out, so I left it in just to make it easier to develop the functionality later, I don't see it harming anything as it simply skips through with nothing to attach.

    I don't know why it's popping an error either! Hopefully someone can help.

    *Edit: Yes, already had that lib. added.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-02-2011, 07:25 AM
  2. Replies: 7
    Last Post: 07-15-2011, 08:14 AM
  3. Date And Time Of Member's Entry And Exit.
    By bhavik1978 in forum Access
    Replies: 4
    Last Post: 09-20-2010, 06:40 AM
  4. Replies: 3
    Last Post: 08-08-2010, 05:33 PM
  5. Replies: 0
    Last Post: 05-17-2008, 01:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums