Easily send email using VBA Mailer

Same as sending mail using Outlook VBA, but much improved

With the new Outlook version the ability to send emails from Office applications is lost - a big problem for users who previously relied on VBA macros to automate sending of emails. VBA Mailer not only fills in the gap, but adds important new features:

• A wizard to easily set up the email processes for common scenarios in Excel and MS Access.
• Easy writing of HTML emails in the template editor.
• A transparent way of encoding the HTML message body in VBA.

VBA Mailer has implemented its COM object model following its precursor with the objective of allowing easy automated migration of macros that currently use Outlook VBA.

Creating an email merge 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.