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.
CreateEmail
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)
OutlookEmailForSignature.Display
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> </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| Parameter | Description |
|---|---|
| EmailProperties | The 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:
| Property | Description |
|---|---|
| To | Recipient of the email. Separate multiple recipients with a semicolon. |
| Cc | Email addresses being copied on the email. Separate multiple recipients with a semicolon. |
| Bcc | Email addresses being blind-copied on the email. Separate multiple recipients with a semicolon. |
| Subject | The subject of the email. |
| Body | The contents of the email. Should use HTML for pixel-perfect output. |
| Signature | If set to True, your Outlook signature is included. |
| Attachments | Expects a collection of path(s) to attachment(s). |
| Action | Valid 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.
Example
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", "email_to1@domain.com;email_to2@domain.com"
.Add "Cc", "email_cc@domain.com"
.Add "Bcc", "email_bcc@domain.com"
.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 SubHere’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.