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.)
3. In the On_Click event of your Command Button put...

Call SendeMail
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.
The difficult I do immediately, the impossible takes a little bit longer.
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));
2. Then copy and paste the below in the Forms Module (watch those field names)...
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.
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.