Merging Access Contact Info Fields into Outlook VBA

I am trying to use the code below but would like to use an Access table
to feed the 120 individual "email", "subject", "body" and "attachment"
that I need.

What would be the easiest solution to not have to copy this code for my
120 contacts?

Any help greatly appreciated. Thank you in advance.


--------------------------------------------------
On Error GoTo Error_Handler
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = "Email"
.Subject = "Subject"
.body = "Body"
.Attachments.Add "attachment"
.Send
' .ReadReceiptRequested
End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub
Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here
End Sub
-------------------------------------------

re: Merging Access Contact Info Fields into Outlook VBA

you need to open a recordset of your recipients from Access
- something like
dim rsRecipients as dao.recordset

set rsRecipients =
dbengine(0)(0).openrecordset("tblAddresses",dbForw ardOnly)

do until rsRecipients.EOF
set objEMail=objOutlook.CreateItem(olMailItem)
with objEMail
.To=rsRecipients("EMailAddress")
.Subject = "Subject of EMail" '--this is static
.body = strBody
.attachments.add(rsRecipients("SendFileName")
.Send
End with
rsRecipients.MoveNext
Loop

basically, you open the recordset (based on query/table/SQL), and then
you loop through it, pass the data to your mailing function, execute
the .Send method of the message for each recipient in the recordset.

Basically, all you're missing is enclosing your code inside more code
that loops through your recordset of recipients.

With objEmail
'---Modify here from recordset!
.To = rsRecipients("EmailAddress")
.Subject = "Subject" '---this will be the same for everyone
.body = rsRecipients("Message") '---each recipient will get the
message in the "Message" field of the recipients table
.Attachments.Add rsRecipients("FileToAttach")
.Send
' .ReadReceiptRequested
End With

Hope that clears things up a little.

re: Merging Access Contact Info Fields into Outlook VBA

Thank you very much, it works perfectly. That was exactly what I needed