sending multiple emails wth vba

[baronqueefington]

Good evening again,

I have a piece of code that allows me to send multiple emails from a query or table, my only issue is that get an error when it comes across a record without an email address. This shouldn't be the norm as all records should have email addresses but I want to prepare for all eventualities.

I have tried using not Is null (MyRS![Email Address]) then to continue else re loop, but I don't seem to be able to get it right.

Any help would be great.
Private Sub cmdmulti_email_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryLapse")




MyRS.MoveFirst


' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

TheAddress = MyRS![Email Address]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Set the Subject, the Body
.Subject = "subject"
.Body = "test"

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Display

End With
MyRS.MoveNext
Loop



Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End If
End Sub

[rpeare]

you can add an if loop

if isnull(theaddress) then
    'do whatever you're going to do on a blank email
else
    'do your current code
endif

[June7]

And/or select only records that have email address.

Set MyRS = MyDB.OpenRecordset("SELECT * FROM qryLapse WHERE Not [Email Address] Is Null;")