Figure 1. How to automate repetitive emails (made by the author)

Have you ever sent emails whose content is relatively similar daily? If yes, how did you do that? Did you copy the email content and list of recipients of the email yesterday to the one today with an updated attachment? Doing that day by day doesn’t sound fun as well as efficient and effective. That’s why I’d like to introduce you to an alternative method which makes your work life easier and more enjoyable!! It’s called “email automation in Outlook with Visual Basic for Applications (VBA) Excel code (macro)”. A macro in Excel is a set of actions that can be recorded, saved, and repetitively executed. I’ve tried to learn this by myself as a beginner and succeed in applying it to my daily tasks, so don’t worry too much if you don’t have experience with coding 😉

#1: Familiarize yourself with the VBA language

Before creating something, you must know the fundamentals. First, let’s open your Excel and go to the tab “Developer”. Click “Insert” and select a “button (form control)”. Draw the button in the active Excel sheet. Click “New”. Voila! The pop-up allows you to assign a script to your button. Please refer to Part 1 in Figure 2 below. The idea here is to click the button whenever you need to send a repetitive email instead of copying and pasting all the time.

• Tip: Quick access to the code screen: Press ALT+F11

Figure 2. Automate repetitive email in Outlook (made by the author)

So, it’s time you learned the coding world. Technically, there are mainly two parts in the VBA code: Declare and Function. First, you need to declare something you would like to say in VBA language. For example, if you want to say “file name”, it’s written as “Dim filename As String” in VBA. The string is a sequence of characters, texts, numbers, or special characters. I highly recommend you to search for a “list of VBA variables” which are names for different kinds of data the VBA macro will be working with. To automate an email, below is an example of the declared variables (Explanation in green).

Dim eApp As Object            ‘standard as creating an email in Outlook

Example how to use eAPP:

Set eApp = CreateObject(“Outlook.Application”) where Outlook is an Object (an app), so you need to declare it as an Object rather than String or Integer (Outlook isn’t a piece of text or number).

Dim eMail As Object            ‘standard as creating an email in Outlook

Example how to use eMail:

Dim eMail As Object            Set eMail = eApp.CreateItem(0) where CreateItem is used to create the automated email. Again, it’s an Object.

Dim eBody As String           ‘standard as creating an email in Outlook

Dim sendto As String          ‘declare the group of recipients

Dim Filepath As String        ‘declare the file path to your file (attachment)

• Tip: To avoid errors (e.g. early-binding), please refer to a quick fix in Part 2, Figure 2.

#2: Let’s write a piece of code

After declaring your variables, you can now write the function that you’d like the macro to perform. There are several functions, which are similar to Excel’s ones, namely VLOOKUP, IF, Copy, etc. The way of display may be distinct from what you get used to knowing in Excel; however, it’s the same way of logical thinking. The following script is an example of a macro that automates sending repetitive Outlook emails. This script is meant for sending daily reports to a group of recipients (which could be a list of contacts saved in your Outlook) and CC to a recipient joe@gmail.com. 

Set eApp = CreateObject(“Outlook.Application”)           ‘standard as creating an email in Outlook

Set eMail = eApp.CreateItem(0)       ‘standard as creating an email in Outlook

sendto = “Group of recipients”          ‘define your group of recipients

Filepath = “D:Daily report.xlsx”         ‘define where your file locates

On Error Resume Next

 With eMail

.To = sendto        ‘function .To: send an email to a defined recipient/ group of recipients

.CC = “joe@gmail.com”    ‘function .CC: CC an email to a recipient/ group of recipients

.Subject = “Daily report”     ‘function .Subject: name the subject of the email

.Attachments.Add Filepath                ‘function .Attachments.Add: add an attachment to the email

.Display                ‘function .Display: display the email content before sending

.Font.Name = “Calibri (Body)”           ‘function .Font.Name: define font preferred type

.HTMLBody = “<BODY style=’font-family:calibri;font-size:15′>Dear all,<p></BODY>” & “<br>” _   ‘start email body

& “Please find attached the daily report.” & “<br>” & “<br>” _                 ‘email body line 1, <br> indicates new line

& “<b>Kind regards,</b>” & “<br>” & “<br>” _                           ‘email body line 2, <b> indicates bold text

& “Ngoc Tran” & “<br>” _                               ’email body line 3

& .HTMLBody                  ‘end email body

.Send                     ‘function .Send: send the email

End With

On Error GoTo 0

Set eMail = Nothing            ‘standard as creating an email in Outlook

Set eApp = Nothing             ‘standard as creating an email in Outlook

• Tip: When you start writing “With”, it always ends by “End With”

#3: Result

Explanations:

On Error Resume Next means that when a run-time error occurs, the macro goes to the statement immediately following the statement where the error occurred and execution continues.
On Error GoTo 0 disables error handling in the current procedure.

The key purpose of those two statements is to help the code run smoothly.

Now, you close the script screen and press the button. Within a few seconds, your repetitive email in Outlook will be sent automatically to the indicated receivers. Please see in Figure 3 the final results of our macro (start, body, end) and email.

Figure 3. Automated email and full VBA scripts from step 2 (made by the author)

A few things to note down:

• Save the excel file where the macro locates as .xlsm (Microsoft Excel Macro-Enabled Worksheet). Or else, you may lose all your codes.
• Ensure that the file path and file name are correct as hard coded in the script. If either one of their names changes, you can (1) override the code, or (2) be creative to find a flexible solution 😉 (it’s possible!).
• The example macro is the basis, from which you can customize as you wish (e.g. add a link, photo, bullet points, etc.). To be able to do this, combine your creativity and research skills on Google.

Thank you very much for reading. Please share if you find it useful!

Useful articles:

https://corporatefinanceinstitute.com/resources/excel/vba-variables-dim/

https://www.automateexcel.com/learn-vba-tutorial/

https://www.automateexcel.com/vba/send-emails-outlook/

https://stackoverflow.com/questions/668255/excel-macro-send-email

Series Excel skills