“Annual compensation and bonus planning is easy, ” said no Human Resource professional, ever. That’s precisely why we’re going to have a look at automating emails from Excel.
Indeed, compensation and bonus planning can be hard. You need to successfully balance many moving parts: establish meaningful metrics, collect/manage complex and sensitive data for decision support, plan and conduct review sessions, send very personal employee communications, and so much more.
Today, our tutorial focuses on one specific part of compensation and bonus planning: communication of employee bonus awards.
Using your familiar Microsoft Excel application, you will learn all about automating emails from Excel: how to generate and send tailored, employee-specific emails to different recipients in a single click, right from your Excel spreadsheet.
All it takes is:
- A simple Excel Table
- A plug and play (VBA) script
- The click of a button!
Why learn about automating emails from Excel? If not, it’s too much manual work
As a busy HR manager tasked with sending out bonus notification emails, you could fire up your Outlook application and send out emails one by one. But this would be extremely time consuming, and error prone.
One complicating factor is that we have to tailor each employee’s email message with very specific information. One accidental copy-paste move after a long afternoon, and you might accidentally send Julia’s salary information to Joe—a nightmare scenario to say the least!
Among the automation challenges we face:
- You have many different individual recipients
- You have a few different possible subject lines, for example, some employees may not receive a bonus this year
- The message body needs to incorporate employee-specific bonus and payout data
We need a way to streamline this!
The Solution? Automating Emails from Excel
“We are all programmers now”
Fun fact: Excel has evolved significantly over its rich 35-year history. This underscores one of Excel’s biggest competitive advantages? We can take the best of the old and the new, and make it work in almost any given context.
To illustrate, let’s compare two very different Excel technologies we could adapt to help us with automating emails from Excel: VBA or Power Automate. It is not a comprehensive comparison, but it does remind us of Excel’s powerful adaptability across a wide range of constraints you might encounter.
|VBA macros||• “Old” = tried and true 😉|
• The ultimate DIY approach
• Near-zero IT intervention required since this is “prepackaged” with Excel. A huge advantage, especially in certain large corporate environments
• Loads of free information online, likely because it has been around for so long
|• A dated look and feel to the interface|
• Macros cannot run in the cloud
• May get replaced / discontinued by Microsoft… someday (or so the rumors keep threatening)
• Some might poke fun at you for using it ¯\_(ツ)_/¯ #okboomer
• The learning curve after a few simple successful macros becomes steeper
|Power Automate (aka “Flow”)||• Modern, cloud technology|
• Intuitive interface, with a modern look and feel
• Quick & easy to set up as a small solo operation
• Little IT intervention required since this is “prepackaged” with Excel (as long as you have the right version)
|• Not all versions of Excel are compatible|
• Can be confusing to navigate / setup permissions in a corporate environment, especially in tightly controlled (by IT ) ones
• Can be confusing to navigate / setup in a solo environment if you’re not skilled at understanding all the subscriptions / versions available
• Less information found online, likely because it is still relatively undiscovered
It bears repeating that context is everything. Tradeoffs must be evaluated in every situation. For our tutorial today, we’ll focus on automating emails from Excel using a tried-and-true VBA solution.
Assumptions for Tutorial Success
You have Microsoft Office installed, up and running, including Excel and Outlook. (Note that this tutorial does not cover how to integrate with other email providers)
You have an intermediate to low-advanced level grasp of Excel.
You’ve downloaded the example Excel file to see this tutorial in action.
Now, let’s get to it!
Prepare Your Employee Data Table
Step 1: Enter 10 column headers going across the range A1:J1 as follows:
- First Name
- Send To
- Max Bonus Pct
- Bonus Pct Achieved
- Bonus Award $
- Email Subject
- Email Body
- Single-Send Link
Step 2: Convert your range to an Excel Table *
- Click anywhere along the range A1:J1
- From the Excel ribbon, click Insert
- Click Table
- Make sure the ‘My table has headers’ option is checked and click OK
* While a Table is not technically required for the VBA solution presented in this article, Excel Tables are a good habit to master, especially if you plan to segue to more modern approaches like Power Automate later on.
- The Table Tools Design ribbon tab appears (If it does not, make sure you are still clicking inside the range A1:J2).
- Look for the Properties group along the ribbon. Click inside the Table Name and type over ‘Table1’ to rename your Table to ‘Employees’
Step 3: Add your employee data inputs to your Table.
Note, our Table columns are a mix of formula and non-formula columns. In today’s example, we will assume the following data columns are the non-formula columns, which you have copied in or entered manually:
- A: Name
- C: Send To
- D: Salary
- E: Max Bonus Pct
- F: Bonus Pct Achieved
Top Tip: As you go through steps 4-8 you will notice a special syntax that uses column names surrounded by brackets, instead of cell references. This syntax (called structured referencing) is standard for Excel Tables. The syntax auto-generates, so as you type the formula, and you point and click to the columns in question, the brackets and the column name are generated for you.
Step 4: Enter the following formula to calculate column B, First Name:
Explanation: Gets the first name by extracting all characters that are left of the first space found in the full Name.
Step 5: Enter the following formula to calculate column G, Bonus Award $:
=[@Salary]*[@[Bonus Pct Achieved]]
Explanation: Multiplies Salary $ by Bonus Pct Achieved to calculate total bonus dollars to be paid.
Step 6: Enter the following formula to calculate column H, Email Subject:
=IF([@[Bonus Award $]]>0,”Congratulations”,
“A note from your manager”)
Explanation: IF calculated bonus $ > 0, use “Congratulations” as the email subject line. Otherwise use “A note from your manager”
Step 7: Enter the following formula to calculate column I, Email Body:
=IF([@[Bonus Award $]]>0, “Hi “&[@[First Name]] &”. Your bonus this year will be “&TEXT([@[Bonus Award $]],”$0,000″)&” Congratulations!”, “Hello ” &[@[First Name]]&”. Your manager will be contacting you soon to set up a review meeting. Thank you.”)
Explanation: IF bonus is applicable, send a congratulations message. Otherwise send a message regarding an upcoming review meeting.
Step 8: Enter the following formula to calculate column J, Single Send Link:
=HYPERLINK(“mailto: “&[@[Send To]]&”?subject=”&[@[Email Subject]]&”&body=”&[@[Email Body]],”SEND”)
Explanation: Creates a hyperlink you can click if you want to automatically compose an email to a single recipient without using VBA. By clicking the Hyperlink, the formula creates an email is automatically composed.
This creates a hyperlink that you can click if you want to automatically compose an email to a single recipient without using VBA. By clicking the Hyperlink, the formula creates an email that is automatically composed.
You now have a fully populated table:
Adding the VBA Script
Step 1: Access the Visual Basic Editor Window, either:
- Using the keyboard shortcut Alt + F11
- Clicking your Developer tab, then clicking the Visual Basic icon. If you don’t see your Developer tab, follow the steps below:
- Right click anywhere on the ribbon.
- Click Customize the Ribbon.
- Select Main Tabs
- Check Developer
- Click OK
Step 2: Find your file name in the Project Explorer area in the upper left corner of the window.
(As long as your file is open in Excel and was saved as a .xlsm file, it will appear in the list).
Step 3: Right click the file name and choose Insert > Module.
Step 4: In the blank area on the right, copy paste the code that appears in the appendix at the bottom of this tutorial.
Once you copy it into the VBA Module it should look like the screenshot below (including boldfont for any code comments):
Step 5: Click the X in the upper right corner to return to the main Excel window.
Send your Emails!
Step 1: Add a shape you can use as a macro button:
- From your Excel Ribbon, click Insert > Shapes
- Click your preferred Shape. Here we use Rectangle: Rounded Corners
- The + symbol appears. From here you can “draw” your shape, then right click to add some descriptive text to your shape. In this example we use “Send to Select Employees”
Step 2: Assign your new macro to your shape:
- Right click on the Shape.
- Click ‘Assign Macro’
- The Assign Macro dialog appears. Select the ‘This Workbook’ option, then click the name of the macro.
- Click OK to close the Assign Macro dialog.
Step 3: Highlight to select one or more Employee Names in your Table.
You can select from any column in the Table. To select non-contiguous cells, use Ctrl + Click
Once your selections are done, click your new macro button. Your emails will automatically SEND (if you choose this option in the code), or they will be automatically composed in Outlook, in draft mode, so you can inspect them first!
*Remember, you also can click the ‘Single Send Link’ column (Col J) hyperlink to compose a single email.
Automating Emails from Excel: Parting Thoughts
The email automation problem is just one example of many similar challenges faced by HR and other business professionals. While there are scores of apps, software, and services available on the market that may help, flexibility, adaptability, familiarity, and extreme customizability are a few of the key advantages Excel offers. I hope this tutorial has inspired you to explore further. With a bit of creativity and willingness to apply some new (or old!) Excel techniques, you can automate thousands of manual, tedious tasks. Don’t be surprised if you earn a nice bonus for your contributions!
Appendix A: Copy Pasteable VBA Code
Dim oApp As Object
Dim oMail As Object
Dim SendToName As String
Dim theSubject As String
Dim theBody As String
For Each c In Selection ‘loop through (manually) selected records
”’For each row in selection, collect the key parts of
”’the email message from the Table
SendToName = Range(“C” & c.Row)
theSubject = Range(“H” & c.Row)
theBody = Range(“I” & c.Row)
”’Compose emails for each selected record
”’Set object variables.
Set oApp = CreateObject(“Outlook.Application”)
Set oMail = oApp.CreateItem(0)
”’Compose the customized message
.To = SendToName
.Subject = theSubject
.Body = theBody
”’ If you want to send emails automatically, use the Send option.
”’ If you want to generate draft emails and review before sending, use the Display option.
”’ Do not use both!
”’To activate your chosen option: Remove the single quote from the beginning of the code line, then
”’add the single quote back to the option you didn’t choose