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

Question posted 2011 · +3 upvotes

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