Automating Emails from Excel: The Easiest Way for HR to Send Employee Bonus Emails

Published: 2021/4/20 | Updated: 2022/9/1 | Szilvia Juhasz
Automating emails from Microsoft Excel

[get-meta-box style=”blue-box”]

“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.

MethodProsCons
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
 
• Collaboration-friendly
 
• 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
 
VBA macros vs. Power Automate (aka “Flow”)

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
Automate emails from Excel with this tutorial
Basic columns for Excel Table

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.

  1. The Table Tools Design ribbon tab appears (If it does not, make sure you are still clicking inside the range A1:J2).
  2. Look for the Properties group along the ribbon. Click inside the Table Name and type over ‘Table1’ to rename your Table to ‘Employees’
To automate emails from Excel, we must convert these ranges to an Excel Table first.
Converting a range to an Excel Table

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
Manual data input needs to be included before sending out automated emails via Excel
Employee data, manual inputs

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.

=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:

Combined data (both manual inputs plus formulas) necessary for automating emails from Microsoft Excel
Employee data, inputs plus formulas

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
How to access the developer tab in Microsoft Excel
Enabling up the Excel Developer tab

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).

The Visual Basic Editor window, Project Explorer area for Excel tutorial to automate emails
The Visual Basic Editor window, Project Explorer area

Step 3: Right click the file name and choose Insert > Module.

Adding a module to VBA project
Adding a Module to a VBA project

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):

Adding code to your VBA project Module in order to automate sending emails
Adding code to your VBA project Module

Step 5: Click the X in the upper right corner to return to the main Excel window.

Click the X to return to the main Excel window
Closing the Visual Basic Editor 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”
How to add a shape to your Excel worksheet
Adding a Shape to your Excel worksheet

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.
Assign a macro to the shape in Microsoft Excel
Assigning a macro to a Shape on an Excel worksheet

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.

Click the macro button to automatically send emails
Click the new macro button to automatically send emails or have them automatically composed in Outlook as a draft, depending on the option chosen in the code.

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.

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.

Comments

Hi Ayush,

Thanks so much for reading and for bringing up this question.

As far as we know, there is no official limitation of number of emails that be sent via Excel/Outlook integration.

Katie – you are correct! I have not personally tested the limits so I cannot say for sure. Any limitations that do exist might be more about hardware resources versus inherent limitations in Excel or Outlook itself.

Hi Katie,
Love the simple and clear instructions – your code worked first-time – which for me is a real bonus – Question I have, if you don’t mind extending the paradigm. I want to send, in the body, both a number of specific individual data items from defined columns in the data table, and/or optionally, a block of text that comprises a cell / column range. Is it possible to add a code extension to accomplish that? Otherwise, the system, as you have coded it, works beautifully.

Hi Stephen.

Your ideas sound very do-able, but a bit more information would be helpful. I will do my best here to try to imagine what you’re after and give you some things to try out…. 🙂

As far as modifying what gets sent in the body of the email, you have a couple of options:

1/ On the table itself, you could modify the formula used in column I such that it captures the additional information you want, ie the “specific data items from defined columns…” and / or the extra block of text. Of course this formula could get very long if you have complex requirements, so a second option….

2/ Add some new columns in your table to capture your new requirements. For example, let’s say you used columns K and L to capture additional data / text to include in the body. In the VBA code, you could change the following line:

theBody = Range(“I” & c.Row)

to the following:

theBody – Range(“I” & c.Row) & ” ” & Range(“K” & c.Row) & ” ” & Range(“L” & c.Row)

The modified line above will concatenate columns I, K, and L, and include a space between each (the ” ” returns a space).

I hope this gives you some ideas to start. Good luck!

Correction:

theBody – Range(“I” & c.Row) & ” ” & Range(“K” & c.Row) & ” ” & Range(“L” & c.Row)

should be an equals sign immediately following theBody, so…

theBody = Range(“I” & c.Row) & ” ” & Range(“K” & c.Row) & ” ” & Range(“L” & c.Row)

is the correct syntax!

Thank you so much for this! Almost everything worked, but for some reason, when I click the VBA button “Send to Selected Contacts,” only a blank email to that person shows up without the body our subject. When I use the single-send link, the email does populate. Any chance you could point out what could be going wrong here? Thank you!!!

Hi Vee!

The only way I was able to reproduce the blank email behavior you describe was if I clicked the ‘Send to Selected Employees’ button without first selecting any employees. In other words – your cursor must be somewhere inside your table BEFORE you click the ‘Send to Selected Employees’ button (otherwise the macro doesn’t “know” which employee you are concerned with, so it creates a blank email). The correct way to do this is described in detail in Step 3 (“Highlight to select one or more Employee Names in your Table”) in the “Send Your Emails!” section of this article.

IF you’ve confirmed you are following this step correctly and it’s still not working, some other things to confirm:

1/ Make sure there is something in the ‘Email Body’ column, and that this is column I

2/ Make sure there is something in the ‘Email Subject’ column, and that this is column H

I hope this helps… Keep us posted!

EDIT: Regarding employee selections. You can actually position your cursor OUTSIDE the table, as long as it is positioned in one of rows occupied by the table data. In the example for this article, that would mean rows 2:6!

Thank you very much Szilvia for this great tutorial!

Do you know if there is a way to send out these emails on a daily basis at a given time without hitting the send button everyday?

Hi Matthew. The short answer is YES, it’s possible! Scheduled tasks can be accomplished with Power Automate (too much explainery for one comment), or VBA. Both have pros & cons, similar to the ones I describe in this article.

If you’re looking to use VBA macros to do this, I would suggest exploring something called the “OnTime” VBA method. For example, the following line of code would run a macro called “MyMacro” (stored in Module 1 of a workbook named ‘Emailer.xlsx”) at 4:00PM every day:

Application.OnTime TimeValue(“4:00 PM”), “C:\MyStuff\Emailer.xlsx!Module1.MyMacro”

Some gotchas: Application.OnTime can be tricky. If you happen to be doing something else in Excel at exactly 4PM, you might get an error because Excel has too much going on. There are other approaches you could consider, but I think it would be simplest to just click the button! It’s only one click after all, how long does THAT take, really??? 😉

Hope this is helpful to get started.

Hi Ty! Yes, this is possible to do, it would require two steps: 1/ Insert a new column in your table to input your cc email addresses. 2/ Modify the VBA code to recognize the new column and the fact that you want to include a cc recipient.

Here is a detailed example of how to do this:

Let’s say you have inserted a new column next to ‘Send To’. You will use this column (D) to enter the address(es) of the cc recipients. (Note, if you have multiple recipients in your new ‘CC’ column (D), you can add them all, using semicolon to separate them, the same way you would if you were typing directly in the Outlook email. You can do the same for the ‘Send To’ column, if you have more than one main recipient you want to include)

Now that you have this new column D, note that all subsequent columns have shifted over by one column to the right. Because of this, and because we have new CC recipients, we need to modify the VBA code in two sections:

1/ In this part….note I added the new row for a new variable called “CCName”:

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)
CCName = Range(“D” & c.Row)
theSubject = Range(“I” & c.Row)
theBody = Range(“J” & c.Row)

2/ In this part, note I have once again added a new row to include the cc recipient in my email:

”’Compose the customized message
With oMail
.To = SendToName
.cc = CCName
.Subject = theSubject
.Body = theBody

That’s about it! This will allow you to now send to cc recipients.

Good luck!

Hi Alina,
Do you need to send the same attachment to every email? If it’s just a few emails you want to add the attachment to, the easiest approach would be to simply attach the file once you have the draft emails created. Add the attachments the way you normally would in any email. This is the easiest way – but not the fastest. Big difference, take your pick!

However if you are up for the challenge, and you want to do this in a more automated way, the approach is very similar to what I described in an earlier question from TY in this blog post! In your case, you could create a new column in your table to store the source file information. But instead of CCName, you can name your column “Attachment”. In this new column you would then add the full path / filename of the desired attachment.

For example, let’s say this new column is now D. The modified code sections would look something like this:

SendToName = Range(“C” & c.Row)
AttachmentsInfo = Range(“D” & c.Row) ‘NEW line of code
theSubject = Range(“I” & c.Row)
theBody = Range(“J” & c.Row)

With oMail
.To = SendToName
.Subject = theSubject
.Body = theBody
.Attachments.Add AttachmentsInfo ‘NEW line of code

I also found this article which explains in more detail, which you can try to adapt to your file:
https://wellsr.com/vba/2018/excel/excel-vba-send-email-with-attachment/

Good luck!

Hi Szilvia Juhasz, really amazing job.
I want to send an automated email, not for every cell that I change, because normally I will change a lot of cells, and I do not want to send a lot of email, but just one. So to send an automated email, after making changes in the workbook, after 1 hour. Is it possible? TIA.

Hi ILIR DANAJ

I am hearing two questions here, so I’ll address them separately.

1/ “I don’t want to send a lot of email, but just one”
Please revisit Step 3 in my original post, and note the part about single-send options when you only have one email to send.

2/ “… after 1 hour”
I think you are asking whether it’s possible to do a scheduled send? If so, see my answer to Matthew’s question above (Feb 8) . In my experience using VBA to do scheduled sends based on time isn’t really the best option. There are better / more modern methods that Microsoft has introduced like Power Automate.. but that’s a topic for another whole post and beyond the scope of this one!

Good luck!

Hi Svilvia, thank you for answering to my question.
This is the scrip that I’m trying to apply. So for any change that I will make in the file, I want to send and automated email only once after 1 hour, only if there are changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgSel As Range
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.OnTime TimeValue(“20:00:00”), “SendEmail”
‘Application.OnTime Now + TimeValue(“00:01:00”)
Set xRg = Range(“A2:NF33”)
Set xRgSel = Intersect(Target, xRg)
ActiveWorkbook.Save
If Not xRgSel Is Nothing Then
Set xOutApp = CreateObject(“Outlook.Application”)
Set xMailItem = xOutApp.CreateItem(0)
xMailBody = “Hello people” & “.” & vbNewLine & vbNewLine & _
“Monthly schedule has been updated” & “.” & ” ” & vbNewLine & _
“Cell(s) ” & xRgSel.Address(False, False) & _
” in the worksheet ‘” & Me.Name & “‘ were modified on ” & _
Format$(Now, “mm/dd/yyyy”) & ” at ” & Format$(Now, “hh:mm:ss”) & _
” by ” & Environ$(“username”) & “.” & ” ” & vbNewLine & _
“Please check your schedule for any changes” & “.”
With xMailItem
.To = “mail1@myemail.al”
.CC = “mail@myemail.al”
.Subject = “Worksheet modified in ” & ThisWorkbook.FullName
.Body = xMailBody
.Send
End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hello..Based on what I think you’re asking I’m not sure what I can advise in a quick post here. I am curious to know whether you have tested this code out? If so, did it work in it’s current form?

Regarding your remark: “only if there are changes in the last hour..”: Did you already devise a mechanism to actually detect whether changes have occurred? I’m guessing not, because this is precisely where it gets complicated: Change tracking on Excel desktop files is not easy. In Excel online it’s much easier – but the ‘gotcha’ here is, macros cannot run in the cloud. So you’d have two platforms to juggle: Excel desktop and Excel online. Yet another blog post if you really wanted to go that route.

Before you go there: I really think Power Automate is a better alternative if you want BOTH things simultaneously: 1/ an efficient way to track when changes have been made, AND 2/ the ability to automatically send emails from Excel based on whether things HAVE changed. One big advantage with Power Automate is that it is more aligned with the “future” of Excel. It is a fact that VBA will become less and less important for many tasks in the near / long term future, even though VBA is still used in A LOT of spreadsheets right now, including certain models I create for myself and my clients!

I suggest check out the following tutorial by Mynda Treacy on sending emails from Excel with Power Automate : https://youtu.be/1g7NA5hYYSo to see if this might be an option for you!

Good luck

Hi,
Thank you for this explanation!
Everything works great, except that once the email is created, my signature is not included (I need to manually add it).
Is it possible to add it to the macro?
Or am I doing something wrong?

Thank you
Daniele

Hi Daniele
There are several ways to add a signature but the code gets a bit more complicated! I’m looking into whether there is an “easy” way… first question is: do you have a picture in your default signature? Let me know. Thanks.

I am attempting to do the same thing and there is an image in our organization signature. I was wondering, if workbook is on a shared drive, could multiple employees use it at separate times to generate an email with their own default signature? – The only real difference in signature would be status and name.

I was also wondering if it were possible to create a PDF out of one of the spreadsheets on the workbook and attach that to the email. If you require any more information, please let me know.

Hi Szilvia,

Thank you for this super helpful and easy to understand tutorial. I have a question – The body of my email has slightly longer and has a touchbase table template where I am trying to get responses to specific questions from my employees. When I try to copy past that templte, excel gives me an error saying “Text values in formula are limited to 255 characters. To create longer text values in the formula, use the CONCENATE function or the concatenation operator(&).” How can I resolve this. Is there a way I can send you the email template I am trying to paste? It won’t let me attach a screenshot here.

Any help will be super appreciated.

Thank you!!

Hi Bhumi
Yes there is a built in limitation in Excel for character count in any cell. Unfortunately there isn’t an easy workaround on the Excel side in the example we’ve illustrated here. A couple of approaches to explore:
1. Consider using a more feature rich email program like MailChimp!
2. Create two (or three, depending on how long the replies are) separate columns for ‘Email Body’ and do the “concatenation” on the VBA code side instead. I’m not sure this makes your life easier, however, as you would still be faced with the challenge of copying part of the employee response into one column, and the other part into subsequent columns… Then then code would look something like this:

ub EmailAllTEST()
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)
CCName = Range(“D” & c.Row)
theSubject = Range(“I” & c.Row)
theBodyParagraphOne = Range(“J” & c.Row) ‘reads columns J and K for the two paragraphs
theBodyParagraphTwo = Range(“K” & c.Row)

”’Compose emails for each selected record
”’Set object variables.
Set oApp = CreateObject(“Outlook.Application”)
Set oMail = oApp.CreateItem(0)

Signature = oMail.body

”’Compose the customized message
With oMail
.To = SendToName
.cc = CCName
.Subject = theSubject
.body = theBodyParagraphOne & vbNewLine & theBodyParagraphTwo ”splits the email text into two paragraphs

”’ rest of the code stays the same as original…

I think using an email marketing program is going to be easier. Alternatively, see my previous answers regarding Power Automate. While this is arguably a “no code required” type approach, there is still a learning curve there as well.

I don’t know if that was helpful but I hope you can appreciate my candor ¯\_(ツ)_/¯

Hi Bhumi,

Fantastic tutorial, thank you so much. I was wondering if it is possible to put a formula in place to consolidate data from multiple rows into one email if the ‘send to’ email address is the same?

Any help would be much appreciated.

Regards
Mike

Hi, very well explained, thanks for this information. Was just wondering whether you could help with explaining how to change the account from which the emails are automatically sent as I have more than one mailbox. Thanks a lot 🙂

Hi there,

This is a great tutorial. I’ve managed to get it all functioning using the send hyperlinks on a modified spreadsheet. I can’t get the macro to run. The debugger stops here:

Set oApp = CreateObject(“Outlook.Application”)

I have another program set as the default email app on my iMac. I also use outlook version 2016 for Mac.

Is there a setting I need to change?

Hi there,

Thanks for this fantastic walkthrough. I’ve managed to get everything working up to the send single link point for a student results spreadsheet. I’m having problems with the macro though and hope that you might be able to help. Running the debugger highlights this line:

Set oApp = CreateObject(“Outlook.Application”)

I’m running excel Mac 2016 with a 365 log in.

Hope you can help, thanks

Nev

Hey there,

Great tutorial as i managed to extract this technique and to use it on the workbook im currently using.

although i have a question.

in the list of employee, i have some row that are blank because i dont have an employees.

it seems that it is a problem for me to send all the emil in once even though i use i uncheck the blank box in the send to to only have the email rows.

i received an error message that says :

” Outlook doesnt recognize one or more names ”

Could you help me with this please ?

Hi,

Just wondering how to add line breaks into the BODY text to achieve:

Dear recipient,

This is the body of the email – first sentence.

Mank thanks

Me

Thanks

Hi Szilvia,
I don’t know why I’m not able to send the mail automatically even after ‘Remove the single quote from the beginning of the SEND, then add the single quote back to the option DISPLAY’.

Still, always it comes to review before sending, or else Run-time error ‘287’ for debugging.

‘.Display
.Send

Hi! Is there any way for me to use this to automatically send an email monthly? I want to use the same idea to email vendors on a job a reminder to submit their billing to us.

I imagine it would be easy enough to add a yes/no and an if else statement to show if they have already submitted this to us, but I am not sure of a way to make it so that this email could potentially be send once a month automatically.

Any idea?

Related posts

The Complete Guide to Annual Compensation Reviews Using Google Sheets

Published: 2020/2/19 | Updated: 2020/2/19 | Andrew Childress

Annual compensation reviews are a must. Reviewing and resetting employee salaries help you ensure that your employees are paid fairly. ...

Read more

Excel Formulas Not Working: 5 Critical Examples of Checking & Fixing Workbooks

Published: 2021/3/16 | Updated: 2021/3/16 | Oz du Soleil

When it comes to working with data in Microsoft Excel, especially if it wasn’t your data to begin with, things ...

Read more

Excel Power Query: The 5 Hacks for Business Intelligence You Should Have in Your Arsenal

Published: 2020/4/7 | Updated: 2020/4/7 | Richard Shepherd

What is Excel Power Query? Using Power Query in Microsoft Excel opens up the possibility of unlocking invaluable Business Intelligence ...

Read more