VBA access code – Passing a selection of e-mail addresses to Outlook

This is similar to the previous post except uses the inbuilt SendObject object to pass the string sBcc directly to Outlook. Outlook picks up the variables and so there is no need to set up public variables.

Private Sub CommandGroupEmail_Click()
On Error GoTo Err_CommandGroupEmail_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sBcc As String
Dim sSubject As String
Dim sMessageBody As String

Set rsEmail = MyDB.OpenRecordset("SELECT ... STATEMENT", dbOpenSnapshot)

With rsEmail
Do Until rsEmail.EOF
If IsNull(![E-mail]) = False Then
sBcc = sBcc & rsEmail![E-mail] & ";"
sSubject = ""
sMessageBody = ""
End If
End With

DoCmd.SendObject , , , , , sBcc , sSubject, sMessageBody, True

Set MyDB = Nothing
Set rsEmail = Nothing

Exit Sub

If (Err = 2467) Or (Err = 91) Or (Err = 2483) Then
Resume Next
End If
Resume Exit_CommandGroupEmail_Click

End Sub

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, Database Design, E-mail, MS Access, VBA Code MS Access. Bookmark the permalink.