Access Diva Send eMail to Multiple Recipients

Send eMail to Multiple Recipients...

A way to send eMail to multiple recipients using DoCmd.SendObject and a little code. (I use this code behind each form because the same forms are used to filter specific reports to specific Users.)

1. Set up your Table and your Query, see examples below, changing the field names to match your own. (You can use the same query to create a form shown in Step 3.)

Note, this method uses DoCmd.SendObject which has a message limitation of 255 characters. If you need to send more you will need to use Outlook Automation.

Private Sub SendeMail()
 
     Dim rs As Recordset
     Dim vRecipientList As String
     Dim vMsg As String
     Dim vSubject As String
 
 
          Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryYourQueryWitheMailAddresses ")
          If rs.RecordCount > 0 Then
              rs.MoveFirst
              Do
                  If Not IsNull(rs!YoureMailAddressField) Then
                      vRecipientList = vRecipientList & rs!FieldThatHoldsTheeMailAddresses & ";"
                      rs.MoveNext
                 Else
                      rs.MoveNext
                 End If
 
             Loop Until rs.EOF
 
             vMsg = "Your Message here..."
             vSubject = "Your Subject here..."
 
             DoCmd.SendObject acSendReport, "rptYourReport", acFormatPDF, vRecipientList, , , vSubject, vMsg, False
             MsgBox ("Report successfully eMailed!")
 
     Else
             MsgBox "No contacts."
     End If
 
End Sub

SELECT tblUsers.ueMail
FROM tblUsers INNER JOIN tbleMailRecipients ON tblUsers.uUserID = tbleMailRecipients.erUserID
WHERE (((tbleMailRecipients.erReportID=5));

Putting an eMail Recipients command button on your Filter Report form will make it easier for Users to add or subtract eMail recipients so they don't have to leave this Form to update the recipients.