By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Merging Access Contact Info Fields into Outlook VBA

P: n/a
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
-------------------------------------------

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
Thank you very much, it works perfectly. That was exactly what I needed

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.