CreateEmail: Draft an Email in Excel

Now that you’ve prepared your analyses, let your audience know about it with an email! Let’s automate that step. Two approaches – you can have Excel draft/send an email to your audience or take it one step further and distribute a PDF version of it.


Dim OutlookApp As Object
Dim OutlookEmailForSignature As Object
Dim Signature As String

Sub CreateEmail(ByRef EmailProperties As Object)

    If OutlookApp Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application")
    If OutlookEmailForSignature Is Nothing Then
        ' Capture signature
        Set OutlookEmailForSignature = OutlookApp.CreateItem(0)
        Signature = OutlookEmailForSignature.HTMLBody
        OutlookEmailForSignature.Close 1
        ' Clean up signature
        Dim PreSignature As String: PreSignature = Mid(Signature, 1, InStr(1, Signature, "_MailAutoSig"))
        Signature = Replace(Signature, PreSignature, "")
        PreSignature = Replace(PreSignature, "<o:p>&nbsp;</o:p>", "")
        Signature = PreSignature & Signature
    End If

    ' Create email
    Dim OutlookEmail As Object: Set OutlookEmail = OutlookApp.CreateItem(0)
    With OutlookEmail
        .To = EmailProperties("To")
        .Cc = EmailProperties("Cc")
        .Bcc = EmailProperties("Bcc")
        .Subject = EmailProperties("Subject")
        .HTMLBody = EmailProperties("Body")
        If EmailProperties("Signature") = True Then .HTMLBody = .HTMLBody & Signature
        If EmailProperties.Exists("Attachments") Then
            Dim AttachmentPath As Variant
            For Each AttachmentPath In EmailProperties("Attachments")
                .Attachments.Add AttachmentPath
            Next AttachmentPath
        End If
        Select Case EmailProperties("Action")
            Case "Send": .Send
            Case "Draft", "Hide": .Close 0
            Case "Display", "Show": .Save: .Display
            Case Else: .Save: .Display
        End Select
    End With
    Set OutlookEmail = Nothing
End Sub
EmailPropertiesThe Subroutine CreateEmail expects a Dictionary object named EmailProperties (akin to kwargs in Python). Each property is optional.

Properties (items in the EmailProperties dictionary) that CreateEmail can use:

ToRecipient of the email. Separate multiple recipients with a semicolon.
CcEmail addresses being copied on the email. Separate multiple recipients with a semicolon.
BccEmail addresses being blind-copied on the email. Separate multiple recipients with a semicolon.
SubjectThe subject of the email.
BodyThe contents of the email. Should use HTML for pixel-perfect output.
SignatureIf set to True, your Outlook signature is included.
AttachmentsExpects a collection of path(s) to attachment(s).
ActionValid options are:
Send: Sends the email immediately without any further prompt. Recommend using only after testing.
Draft (or Hide): Creates the email and stores it in the Draft folder. Does not display on your screen.
Display (or Show): Creates the email, stores it in the Draft folder, and displays it on your screen. [Default behavior if not specified.]

Here is what this Excel Microservice is doing:

  • We define 3 module-level variables – When you’re sending multiple emails, we don’t need to create a reference to the Outlook App or “get” your Outlook signature each and every email.
  • Within the subroutine, a OutlookApp instance is created if not already instantiated.
  • Outlook email signature is stored in case you would like to include it. Note there is clean-up so the signature is not prefixed with two paragraphs, just one. Makes it look more professional.
  • An email is created with the provided EmailProperties.
  • Action is taken per EmailProperties, i.e. email is sent, saved to draft, displayed on your screen.


Here is some illustrative VBA showing how to interact with CreateEmail:

Sub TestCreateEmail()

    Dim MyEmailProperties As Object: Set MyEmailProperties = CreateObject("Scripting.Dictionary")
    Dim MyEmailAttachments As Collection: Set MyEmailAttachments = New Collection
    With MyEmailAttachments
        .Add "C:\Users\Username\Desktop\Attachment 1.txt"
        .Add "C:\Users\Username\Desktop\Attachment 2.txt"
        .Add "C:\Users\Username\Desktop\Attachment 3.txt"
    End With

    With MyEmailProperties
        .Add "To", ";"
        .Add "Cc", ""
        .Add "Bcc", ""
        .Add "Subject", "The subject goes here."
        .Add "Body", "<body style="font-family: Calibri; font-size: 11pt;"><p>The body of the email goes here.</p></body>"
        .Add "Signature", True
        .Add "Attachments", MyEmailAttachments
        .Add "Action", "Display"
    End With
    CreateEmail MyEmailProperties
    Set MyEmailProperties = Nothing
    Set MyEmailAttachments = Nothing

End Sub

Here’s what’s going on in this example:

  • Create a dictionary that will hold properties.
  • Create a collection that will hold paths to attachments.
  • Store paths to attachments in a collection. For this example, we’re going to attach 3 files, but could be any (or none).
  • Store email properties in a dictionary. For this example, we’re including:
    • Two individuals we’re emailing To.
    • One individual we’re Ccing and one individual we’re Bccing.
    • Both Subject and Body are provided.
    • We’ve set Signature to True, meaning we’d like to include our Outlook signature.
    • Our Attachments are defined in the MyEmailAttachments collection.
    • We would like to display as the Action, a sensible choice when we’re testing our Excel App.
  • Then we actually call CreateEmail and pass it the MyEmailProperties.Add dictionary.
  • Finally we clear the references to MyEmailProperties and MyEmailAttachments to ensure there’s nothing left behind in memory.

You will need to adapt the TestCreateEmail to your own Excel App. As you can see, it includes hardcoded values – preferred practice would be to include references to, for example, values on your sheet (e.g. MyEmailProperties.Add "To", Range("A2").Value2)

Anticipated questions:

  • What if I don’t want to cc or bcc anyone?
    Then there’s no need to .Add "Cc" or .Add "Bcc" in your Excel App
  • What if I don’t have any attachments?
    Then there’s no need to Dim the MyEmailAttachments collection / no need to add any file paths to that collection / no need .Add "Attachments" in your Excel App

Feel free to reach out with questions or comments.

Scroll to Top