title: Send an email from Excel 2007 VBA using an Outlook Template & Set Variables
body: <p>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.</p>
  
  <ol>
  <li>create a mail object  </li>
  <li>set the mail object to the template file  </li>
  <li>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  </li>
  <li>save to drafts for later review/send</li>
  </ol>
  
  <p>e.g. Dear &lt;&lt; clientname >> = Dear John Smith</p>
  
  <p>My code thus far:</p>
  
  <pre><code>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
  </code></pre>