Send an email from Excel 2007 VBA using an Outlook Template & Set Variables

Question

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.

1. create a mail object
2. set the mail object to the template file
3. 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
4. save to drafts for later review/send

e.g. 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

Answer 1

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

Answer 2

This is a perfect job for mail merge. If you want to do it programmatically, see

Mail Merge in Word+Excel using VBA

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.