Emailing Last Record & exit handling

[malamute20]

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.

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

[June7]

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?

[malamute20]

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.

[June7]

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

[malamute20]

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.

[June7]

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.

[malamute20]

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

 

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

Go under

.HTMLBody = "text here"

?

[June7]

.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

[malamute20]

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.

[June7]

Need to set a Reference. From the VBA editor menu Tools > References scroll down to Microsoft Outlook 14.0 Object Library and check it.

[malamute20]

Ok. What does that do? The error did not go away afterwards.

[June7]

Post your revised procedure. Let's see how you implemented my sample code.

[malamute20]

 
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

[June7]

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.

[malamute20]

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.