Automating emails from Excel: The easiest way for HR to send employee bonus emails

“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. Say farewell to tedious manual sending and embrace this time-saving solution to effortlessly communicate bonus details to your employees.

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.

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: 

• Name
• First Name
• Send To
• Salary
• Max Bonus Pct
• Bonus Pct Achieved
• Bonus Award $
• Email Subject
• Email Body
• Single-Send Link

Step 2: Convert your range to an Excel Table *

1. Click anywhere along the range A1:J1
2. From the Excel ribbon, click Insert 
3. Click Table
4. 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.

5. The Table Tools Design ribbon tab appears (If it does not, make sure you are still clicking inside the range A1:J2).
6. 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:

Columns:

• 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

=LEFT([@Name],FIND(" ",[@Name])-1)

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:

1. Using the keyboard shortcut Alt + F11
2. 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

1. From your Excel Ribbon, click Insert > Shapes
2. Click your preferred Shape. Here we use Rectangle: Rounded Corners
3. 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

1. Right click on the Shape.
2. Click ‘Assign Macro’
3. The Assign Macro dialog appears. Select the ‘This Workbook’ option, then click the name of the macro.
4. 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

Sub EmailAll()
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   
    With oMail
        .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
    
     .Display
    '.Send
    End With
Next c
End Sub

– – –

Thank you for reading our tutorial; we hope it’s been helpful. If you run into any issues while putting these steps into practice, be sure to drop us a line in our comments section, where we’ll get back to you as soon as possible. The more details you can provide, the better. Please use this site to upload any screenshots you’d like to share with your question.

RELATED POST

Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal

Read more

—

Photo by Ed Hardie on Unsplash

Facebook

Twitter

Linkedin

Whatsapp

Reddit

Telegram

Szilvia Juhasz

Author, speaker, consultant, and Excel-enthusiast. Szilvia brings a heaping dose of wit, experience, and education to her mission: To rescue her profession out of the daily chaos of spreadsheet madness, and into a more enlightened world where the joys of modern data analytics can inform, explain, tell stories, and transform businesses.