Send a formatted email using VBA and Outlook

Using HTML for your email content makes it much more expressive, including the ability to use fonts and bold, hyperlinks and even images. This article shows how to code a VBA macro to send a formatted email using VBA and Outlook. To make the email and the HTML content for its body fragments and code are inserted by Code VBA add-in. The HTML is created using a combination of fixed tag strings and class HTMLElement (automatically inserted in the project by the add-in when using fragments that rely on it)

  1. Create the email object in Outlook;
  2. Make an email body with customized salutation.

In a module, create an empty Sub 'HtmlMailDemo' for the macro

Using the Outlook MailItem

We want to send an email to each recipient. To achieve this we make an empty line before .MoveNext to create the email. Now we will use the Code VBA add-in to connect to Outlook and create the mail: down the Code VBA menu, select Outlook followed by Create...

menu Create Outlook MailItem

This opens a Fragment Builder dialog to specify the use of the MailItem object:

dialog to specify the use of the MailItem object
  • Enter text for subject line;
  • Agree to have a new String variables strBody and strTo created to set the .To and .Body properties;
  • Accept the default value .Display for the MailItem's Action setting. You will probably change this to change this to .Send once things are woking to your satisfaction.

After OK, the following code block is inserted


Dim mimEmail As Outlook.MailItem
Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
Dim strBody As String: strBody =
Set mimEmail = appOutlook.CreateItem(olMailItem)
With mimEmail
    .To = strTo
    .Subject = "Taskforce meeting"
    '.Body = strBody 'use .HTMLBody in case of HTML!'
    .HTMLBody = strBody
    .Display
End With

Here, we want a formatted body (using HTML), so we change .Body to .HTMLBody.

Make a formatted body with customized salutation

We now need to make suitable HTML for the body. After putting the cursor behind strBody = (part after : moved to the next line), from the Code VBA menu, we now select Outlook » HTML Body » _HTMLEmail » strBody. This inserts the following boilerplate HTML and adds a new class HTMLElement to your body.


Dim html As New HTMLElement
With html
    .Tag = "html"
    .AddAttribute "lang", "en"
    Dim head As New HTMLElement
    With head
        .Tag = "head"
        'see Setting the Viewport on https://www.w3schools.com/tags/tag_meta.asp
        'see https://www.w3schools.com/tags/tag_style.asp
        .AppendContentValue "<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />" & vbNewLine & _
            "<meta name="viewport" content="width=device-width, initial-scale=1"/>" & vbNewLine & _
            "<meta name="x-apple-disable-message-reformatting">"
    End With
    Dim body As New HTMLElement
    With body
        .Tag = "body"
        .EndWithNewLine = True
        'continue here defining the (HTML)Elements that build the content
        'and add them using .AppendContentElement (and/or .AppendContentValue)
        
    End With
    .AppendContentElement head
    .AppendContentElement body
    strBody = "" & vbNewLine & .Text
End With

Dissecting the body HTML code creation:

  • First, HTMLElement html is created with the attribute lang="en";
  • Nested herein, the standard head and body elements are built up, which are at the end added using .AppendContentElement head and .AppendContentElement body statements;
  • The head element in this case is considered a boilerplate set of metatags and is added as a fixed string using .AppendContentValue;
  • The body element obviously is the interesting part which is added before this element's End With and is elaborated below;
  • Rounding up, the body code is obtained from the HTMLElement html using the .Text property and assigned to the body variable:strBody = "" & vbNewLine & .Text

Writing body code

Follow the link to learn more about Code VBA tools for creating body HTML.

You can now run the macro (F5). The emails are opened in draft. If you extended this code to your satisfaction and all's good, change .Draft to .Send for future enjoy.

automated customized emails 

Sub HtmlMailDemo()
    Dim strEmailAddess As String
    Dim strFirstName As String
    Dim strLastName As String
    Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
    strEmailAddess = "t.axen@rivm.nl"
    strFirstName = "Thomas"
    strLastName = "Axen"
    Dim mimEmail As Outlook.MailItem
    Dim strBody As String
    Dim html As New HTMLElement
    With html
        .Tag = "html"
        .AddAttribute "lang", "en"
        Dim head As New HTMLElement
        With head
            .Tag = "head"
            'see Setting the Viewport on https://www.w3schools.com/tags/tag_meta.asp
            'see https://www.w3schools.com/tags/tag_style.asp
            .AppendContentValue "<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />" & vbNewLine & _
                "<meta name="viewport" content="width=device-width, initial-scale=1"/>" & vbNewLine & _
                "<meta name="x-apple-disable-message-reformatting">"
        End With
        Dim body As New HTMLElement
        With body
            .Tag = "body"
            .EndWithNewLine = True
            'continue here defining the (HTML)Elements that build the content
            'and add them using .AppendContentElement (and/or .AppendContentValue)
            
        End With
        .AppendContentElement head
        .AppendContentElement body
        strBody = "" & vbNewLine & .Text
    End With    
    Set mimEmail = appOutlook.CreateItem(olMailItem)
    With mimEmail
        .Subject = "Taskforce meeting"
        '.Body = strBody 'use .HTMLBody in case of HTML!'
        .HTMLBody = strBody
        .Display
    End With
End Sub