I have a list of data, let’s say client information (Name, Email, Amount Owing etc.), stored in an Excel worksheet . My aim is to click a button in Excel and send each client their information in an Outlook Template.
- create a mail object
- set the mail object to the template file
- setting and then filling in the template with data about the current client – mostly stuck here, not sure how to specify variables in a template and then relate to them in VBA
- save to drafts for later review/send
eg. Dear << clientname >> = Dear John Smith
My code thus far:
Dim myOlApp As Outlook.Application Dim MyItem As Outlook.MailItem Set myOlApp = CreateObject("Outlook.Application") Set MyItem = myOlApp.CreateItemFromTemplate("C:\egTemplate.oft") With MyItem .To = Worksheets("Clients").Range(1, 2) .Subject = "Monthly bill" 'Refer to and fill in variable items in template .Save End With Set MyItem = Nothing Set MyOlApp = Nothing
I need to know if this is even possible, can I do it without plugins and if so, does anyone know a good link/tutorial I can follow?
Thanks in advance and kind regards
Update Added my code and made some changes to the request
Here is what you can do :
With MyItem 'Refer to and fill in variable items in template .Body = Replace(.Body, "<< clientname >>", Worksheets("Clients").Range(1, 2)) End With
or, if your mail is in HTML:
With MyItem 'Refer to and fill in variable items in template .HTMLBody = Replace(.HTMLBody, "<< clientname >>", Worksheets("Clients").Range(1, 2)) End With
Tested successfully on Excel / Outlook 2007
This is a perfect job for mail merge. If you want to do it programmatically, see
Or you could simply do it manually (from Word), inserting merge fields and then selecting your workbook as the data source. You can merge to email and Outlook will send out personalized emails to each recipient’s email using the information from each row/record.