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

By | January 12, 2018

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

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
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, "&lt;&lt; clientname &gt;&gt;",  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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *