Easily set up email merge using VBA Mailer

Creating email merge VBA using the Email Template Editor

This page gives an overview of the email merge process creation using VBA Mailer. Below diagram shows the email merge process creation using the email template editor and the VBA to be run. Edit and Save represent the steps in setting up of the process, where Run is the use of the VBA macro to create an email.

With three tools working together VBA Mailer lets you achieve the ideal email merge.

• VBA lets you gather the data to be merged in variables
• Create email macro sets up the VBA email merge boilerplate VBA procedure including handling of recipients source.
• The email template editor to drag & drop variables on the required location in To, Subject and message text.

Drag and drop VBA variables in the email template editor

The ideal email merge lets you control all content elements and what can be inserted in the email:

• Recipients
• The Subject line
• The HTML Body content

Recipients - To/CC/BCC

In the example below the recipient email address can be dropped in the To line

Subject

The Subject field can be filled with a required combination of text and variable content using drag and drop from the variables pane.

HTML Body content

The template editor is a full fledged WYSIWYG HTML editor. In addition to the normal editer features it has a Variable pane

Save the template as VBA

When you press Save all content of the email template is saved to the email macro fully encoded as VBA. As an example here the part of the code that concerns the email content.

Do While Not .EOF
    frm.Bookmark = .Bookmark
    Set mi = appMailer.CreateItem(OlItemType.olMailItem)
    Dim strFirstName As String: strFirstName = ControlValue(frm.Controls("First Name"))
    Dim strEmailAddress As String: strEmailAddress = ControlValue(frm.Controls("E-mail Address"))
    With mi
        .To = strEmailAddress
        .Subject = "Invitation for " & strFirstName & " to celebrate completion of " & ProjectName
        Dim n(27) As String
        n(2) = p("Hi " & strFirstName & ",")
        n(3) = p("We've been working together on our project " & ProjectName & " for nearly a year, but it has been worth it!")
        n(5) = li("Great design,")
        n(6) = li("Much attention to details in implementation and testing.")
        n(4) = ol(n(5) & n(6), "style=list-style-type: decimal;")
        n(10) = th("Phase")
        n(11) = th("Actual")
        n(12) = th("Planned")
        n(9) = tr(n(10) & n(11) & n(12))
        n(8) = thead(n(9))
        n(15) = td("Design")
        n(16) = td("February 15")
        n(17) = td("February 26")
        n(14) = tr(n(15) & n(16) & n(17))
        n(19) = td("Version 1")
        n(20) = td("July 3")
        n(21) = td("June 25")
        n(18) = tr(n(19) & n(20) & n(21))
        n(23) = td("Version 2")
        n(24) = td("November 29")
        n(25) = td("December 3")
        n(22) = tr(n(23) & n(24) & n(25))
        n(13) = tbody(n(14) & n(18) & n(22))
        n(7) = table(n(8) & n(13), "style=border-color: black;")
        n(26) = br()
        n(27) = p("To celebrate we will have a dinner with music on Friday, I hope to see you all then!")
        n(1) = n(2) & n(3) & n(4) & n(7) & n(26) & n(27)
        .HTMLBody = n(1)
        .Send
    End With
    .MoveNext
Loop

You will notice the use of HTML Element functions instead of tag strings which

• Makes code more readable.
• Supports two way editing, in VBA and in the template editor by reflecting the HTML body structure.

Run the email macro

When running the macro to send mail The VBA code fills all parts of the new email. If you selected .Display, the email is opened in an editor for review. From there it can be Send or Saved to the Drafts folder on your email server.

Editing the VBA encoded email template

The template content can be converted from HTML to VBA and visa versa (round trip). Minor changes to the mail process such as simple text changes can be done directly in the VBA, For major changes using the email editor again is preferred.