Automating emails from Excel: The easiest way for HR to send employee bonus emails
Table of Contents
Table of Contents
- Why learn about automating emails from Excel? If not, itโs too much manual work
- The solution? Automating emails from Excel
- Assumptions for tutorial success
- Prepare your employee data table
- Step 1: Enter 10 column headers going across the range A1:J1 as follows:
- Step 2: Convert your range to an Excel Table *
- Step 3: Add your employee data inputs to your Table
- Step 4: Enter the following formula to calculate column B, first name
- Step 5: Enter the following formula to calculate column G, bonus award $
- Step 6: Enter the following formula to calculate column H, email subject
- Step 7: Enter the following formula to calculate column I, email body
- Step 8: Enter the following formula to calculate column J, single send link
- Adding the VBA script
- Step 1: Access the Visual Basic Editor Window, either:
- Step 2: Find your file name in the Project Explorer area in the upper left corner of the window
- 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
- Step 5: Click the X in the upper right corner to return to the main Excel window
- Send your emails
- Automating emails from Excel: Parting thoughts
- Appendix A: Copy pasteable VBA code
โ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.
| Method | Pros | Cons |
| 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 |
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 *
- 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:
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:
- 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
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.
โ
Photo by Ed Hardie on Unsplash
The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company/tool or their past/present organizations, employers, or associates.
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
Related posts
The CFO Journal with Alex Sotelo of Open Education: โLoyalty, trust and laughterโ
Published: 2018/6/12 | Helen PoliquinWho is Alex Sotelo? Where: Open Education has its roots in Venezuela, where in 2002 CEO Andrรฉs Moreno founded Optimal English. His goal was to provide English classes taught by native speakers to Latin American executives. Optimal English became Open English in 2008, with a shift to virtual classes available 24/7. 10 years later, Open [โฆ]
![Tips for effective employee performance reviews [4 HR experts weigh in]](https://beebole.com/blog/wp-content/uploads/2020/10/employee-performance-reviews-700x332.jpg)
Tips for effective employee performance reviews [4 HR experts weigh in]
Published: 2020/11/17 | Helen PoliquinWe all know that employees yearn for constructive performance reviews. We also know that managers often find feedback hard to give. The fact is, whether it comes in the form of a positive evaluation or constructive criticism, reviewing performance is key to employee engagement, development and the growth of your business. It can even help [โฆ]
The boom of remote working: A review of the trends and legal issues for companies
Published: 2020/1/30 | Carlos QuintanaIn the last decade, remote working has been on the rise. Whether partially or on a full-time basis, the number of contractors and employees working from home or elsewhere has exploded. Such a trend has been further accelerated by the coronavirus pandemic, as many full-time employees who previously worked in an office have now been [โฆ]
Thatโs great to send emails for free via a small little bit complicated setup.
But what is the maximum limit of no of emails sended via vba and outlook integration.
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, just wonder that if I have multiple emails to send to, how can i use cc to 2 different email?
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! I want to know how I can also add to my e-mails attachments?
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?