{"id":8049,"date":"2021-04-20T14:00:36","date_gmt":"2021-04-20T12:00:36","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=8049"},"modified":"2025-02-05T15:48:20","modified_gmt":"2025-02-05T14:48:20","slug":"automating-emails-from-excel-employee-bonus","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/automating-emails-from-excel-employee-bonus","title":{"rendered":"Automating emails from Excel: The easiest way for HR to send employee bonus emails"},"content":{"rendered":"\n<p>\u201cAnnual compensation and bonus planning is easy, \u201d said no human resource professional, ever. That&#8217;s precisely why we&#8217;re going to have a look at <strong>automating emails from Excel<\/strong>. Say farewell to tedious manual sending and embrace this time-saving solution to effortlessly communicate bonus details to your employees.<\/p>\n\n\n\n<p>Indeed, compensation and bonus planning can be <em>hard. <\/em>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.&nbsp;<\/p>\n\n\n\n<p>Today, our tutorial focuses on one specific part of <a href=\"https:\/\/beebole.com\/blog\/annual-compensation-reviews-google-sheets-free-template\/\">compensation and bonus<\/a> planning: communication of employee bonus awards.<\/p>\n\n\n\n<p>Using your familiar Microsoft Excel application, <strong>you will learn all about automating emails from Excel<\/strong>: how to generate and send tailored, employee-specific emails to different recipients in a single click, right from your Excel spreadsheet.<\/p>\n\n\n\n<p>All it takes is:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A simple Excel Table&nbsp;<\/li>\n\n\n\n<li>A plug and play (VBA) script<\/li>\n\n\n\n<li>The click of a button!<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"why-learn\">Why learn about automating emails from Excel? If not, it&#8217;s too much manual work<\/h2>\n\n\n\n<p>As a busy HR manager tasked with sending out bonus notification emails, you <em>could<\/em> fire up your Outlook application and send out emails one by one. <strong>But this would be extremely time consuming, and&nbsp;error prone.<\/strong>&nbsp;<\/p>\n\n\n\n<p>One complicating factor is that we have to tailor each employee\u2019s email message with very specific information. One accidental copy-paste move after a long afternoon, and you might accidentally send <em>Julia<\/em>\u2019s salary information to <em>Joe<\/em>\u2014a nightmare scenario to say the least!<\/p>\n\n\n\n<p>Among the automation challenges we face:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You have many different individual <em>recipients<\/em><\/li>\n\n\n\n<li>You have a few different possible <em>subject lines<\/em>, for example, some employees may not receive a bonus this year<\/li>\n\n\n\n<li>The <em>message body<\/em> needs to incorporate employee-specific bonus and payout data<\/li>\n<\/ul>\n\n\n\n<p>We need a way to streamline this!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"solution\">The solution? Automating emails from Excel<\/h2>\n\n\n\n<p><em>\u201cWe are all programmers now\u201d<\/em><\/p>\n\n\n\n<p>Fun fact: Excel has evolved significantly over its rich 35-year history. This underscores one of Excel\u2019s biggest competitive advantages? We can take the best of the old <em>and<\/em> the new, and make it work in almost any given context.<\/p>\n\n\n\n<p>To illustrate, let\u2019s compare two very different Excel technologies we <em>could<\/em> 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 <strong>Excel\u2019s powerful adaptability across a wide range of constraints you might encounter<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-table\">\n<div class=\"bbl-block-table\"><table>\n<thead>\n<tr>\n<td class=\"has-text-align-center\" data-align=\"center\"><strong>Method<\/strong><\/td>\n<td class=\"has-text-align-center\" data-align=\"center\"><strong>Pros<\/strong><\/td>\n<td class=\"has-text-align-center\" data-align=\"center\"><strong>Cons<\/strong><\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"has-text-align-center\" data-align=\"center\"><strong>VBA macros<\/strong><\/td>\n<td class=\"has-text-align-center\" data-align=\"center\">\u2022 \u201cOld\u201d = tried and true ?<br \/>\u00a0<br \/>\u2022 The ultimate DIY approach<br \/>\u00a0<br \/>\u2022 <em>Near-zero<\/em> IT intervention required since this is \u201cprepackaged\u201d with Excel. A huge advantage, especially in certain large corporate environments<br \/>\u00a0<br \/>\u2022 <em>Loads<\/em> of free information online, likely <em>because<\/em> it has been around for so long<\/td>\n<td class=\"has-text-align-center\" data-align=\"center\">\u2022 A dated look and feel to the interface<br \/>\u00a0<br \/>\u2022 Macros cannot run in the cloud<br \/>\u00a0<br \/>\u2022 <em>May<\/em> get replaced \/ discontinued by Microsoft\u2026 someday (or so the rumors keep threatening)<br \/>\u00a0<br \/>\u2022 Some might poke fun at you for using it \u00af\\_(\u30c4)_\/\u00af #okboomer<br \/>\u00a0<br \/>\u2022 The learning curve after a few simple successful macros becomes steeper<\/td>\n<\/tr>\n<tr>\n<td class=\"has-text-align-center\" data-align=\"center\"><strong>Power Automate (aka \u201cFlow\u201d)<\/strong><\/td>\n<td class=\"has-text-align-center\" data-align=\"center\">\u2022 Modern, cloud technology<br \/>\u00a0<br \/>\u2022 Intuitive interface, with a modern look and feel<br \/>\u00a0<br \/>\u2022 Collaboration-friendly<br \/>\u00a0<br \/>\u2022 Quick &amp; easy to set up as a small solo operation<br \/>\u00a0<br \/>\u2022 Little IT intervention required since this is \u201cprepackaged\u201d with Excel (as long as you have the right version)<br \/>\u00a0<\/td>\n<td class=\"has-text-align-center\" data-align=\"center\">\u2022 Not all versions of Excel are compatible<br \/>\u00a0<br \/>\u2022 Can be confusing to navigate \/ setup permissions in a corporate environment, especially in tightly controlled (by IT ) ones<br \/>\u00a0<br \/>\u2022 Can be confusing to navigate \/ setup in a solo environment if you\u2019re not skilled at understanding all the subscriptions \/ versions available<br \/>\u00a0<br \/>\u2022 Less information found online, likely because it is still relatively undiscovered<br \/>\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/div>\n<figcaption class=\"wp-element-caption\">VBA macros vs. Power Automate (aka &#8220;Flow&#8221;)<\/figcaption>\n<\/figure>\n\n\n\n<p>It bears repeating that <em>context<\/em> is everything. <em>Tradeoffs<\/em> must be evaluated in every situation. <strong>For our tutorial today, we\u2019ll focus on automating emails from Excel using a tried-and-true VBA solution.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"success\">Assumptions for tutorial success<\/h2>\n\n\n\n<p>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.)<\/p>\n\n\n\n<p>You have an intermediate to low-advanced level grasp of Excel.<\/p>\n\n\n\n<p>You&#8217;ve downloaded the <a class=\"free-download-link\" href=\"https:\/\/docs.google.com\/uc?id=1RTNiDhfTShpW5LhVUFu37zFxMOfbJrTI&amp;export=download\" target=\"_blank\" rel=\"noopener\">example Excel file to see this tutorial<\/a> in action.<\/p>\n\n\n\n<p>Now, let\u2019s get to it!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"prepare\">Prepare your employee data table<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-1-enter-10-column-headers-going-across-the-range-a1-j1-as-follows\">Step 1: Enter 10 column headers going across the range A1:J1 as follows:&nbsp;<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Name<\/li>\n\n\n\n<li>First Name<\/li>\n\n\n\n<li>Send To<\/li>\n\n\n\n<li>Salary<\/li>\n\n\n\n<li>Max Bonus Pct<\/li>\n\n\n\n<li>Bonus Pct Achieved<\/li>\n\n\n\n<li>Bonus Award $<\/li>\n\n\n\n<li>Email Subject<\/li>\n\n\n\n<li>Email Body<\/li>\n\n\n\n<li>Single-Send Link<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"81\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Column-headers-for-Excel-tutorial-700x81.jpg\" alt=\"Automate emails from Excel with this tutorial\" class=\"wp-image-8057\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Column-headers-for-Excel-tutorial-700x81.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Column-headers-for-Excel-tutorial-768x89.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Column-headers-for-Excel-tutorial.jpg 807w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Basic columns for Excel Table<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-2-convert-your-range-to-an-excel-table\">Step 2: Convert your range to an Excel Table *<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Click anywhere along the range A1:J1<\/li>\n\n\n\n<li>From the Excel ribbon, click Insert&nbsp;<\/li>\n\n\n\n<li>Click Table<\/li>\n\n\n\n<li>Make sure the \u2018My table has headers\u2019 option is checked and click OK<\/li>\n<\/ol>\n\n\n\n<p><em>* While a <\/em><strong><em>Table<\/em><\/strong><em> is not technically required for the VBA solution presented in this article, <a href=\"https:\/\/beebole.com\/blog\/excel-formulas-not-working-fixing-workbooks\/\">Excel Tables are a good habit to master<\/a>, especially if you plan to segue to more modern approaches like Power Automate later on.<\/em><\/p>\n\n\n\n<ol start=\"5\" class=\"wp-block-list\">\n<li>The Table Tools Design ribbon tab appears (If it does not, make sure you are still clicking inside the range A1:J2).<\/li>\n\n\n\n<li>Look for the Properties group along the ribbon. Click inside the Table Name and type over \u2018Table1\u2019 to rename your Table to \u2018Employees\u2019<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"544\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Convert-range-to-an-Excel-Table-700x544.jpg\" alt=\"To automate emails from Excel, we must convert these ranges to an Excel Table first.\" class=\"wp-image-8058\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Convert-range-to-an-Excel-Table-700x544.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Convert-range-to-an-Excel-Table-768x597.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Convert-range-to-an-Excel-Table.jpg 832w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Converting a range to an Excel Table<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-3-add-your-employee-data-inputs-to-your-table\">Step 3:&nbsp; Add your employee data inputs to your Table<\/h3>\n\n\n\n<p>Note, our Table columns are a mix of formula and non-formula columns. In today\u2019s example, we will assume the following data columns are the <em>non<\/em>-formula columns, which you have copied in or entered manually:<\/p>\n\n\n\n<p>Columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A:&nbsp; Name<\/li>\n\n\n\n<li>C:&nbsp; Send To<\/li>\n\n\n\n<li>D:&nbsp; Salary<\/li>\n\n\n\n<li>E:&nbsp; Max Bonus Pct<\/li>\n\n\n\n<li>F:&nbsp; Bonus Pct Achieved<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"111\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Employee-data-inputs-700x111.jpg\" alt=\"Manual data input needs to be included before sending out automated emails via Excel\" class=\"wp-image-8059\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Employee-data-inputs-700x111.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Employee-data-inputs-768x122.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Employee-data-inputs.jpg 828w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Employee data, manual inputs<\/figcaption><\/figure>\n\n\n\n<p class=\"has-text-align-center\"><em><strong>Top Tip:<\/strong> 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 <\/em><strong><em>structured referencing<\/em><\/strong><em>) 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.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-4-enter-the-following-formula-to-calculate-column-b-first-name\">Step 4: Enter the following formula to calculate column B, first name<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282A36\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"=LEFT([@Name],FIND(&quot; &quot;,[@Name])-1)\" style=\"color:#f6f6f4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dracula-soft\" style=\"background-color: #282A36\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F286C4\">=<\/span><span style=\"color: #62E884\">LEFT<\/span><span style=\"color: #F6F6F4\">([@Name],<\/span><span style=\"color: #62E884\">FIND<\/span><span style=\"color: #F6F6F4\">(<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\"> <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F6F6F4\">,[@Name])<\/span><span style=\"color: #F286C4\">-<\/span><span style=\"color: #BF9EEE\">1<\/span><span style=\"color: #F6F6F4\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><em>Explanation: Gets the first name by extracting all characters that are left of the first space found in the full Name.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-5-enter-the-following-formula-to-calculate-column-g-bonus-award\">Step 5: Enter the following formula to calculate column G, bonus award $<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282A36\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"=[@Salary]*[@[Bonus Pct Achieved]]\" style=\"color:#f6f6f4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dracula-soft\" style=\"background-color: #282A36\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F286C4\">=<\/span><span style=\"color: #F6F6F4\">[@Salary]<\/span><span style=\"color: #F286C4\">*<\/span><span style=\"color: #F6F6F4\">[@[Bonus Pct Achieved]]<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><em>Explanation: Multiplies Salary $ by Bonus Pct Achieved to calculate total bonus dollars to be paid.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-6-enter-the-following-formula-to-calculate-column-h-email-subject\">Step 6: Enter the following formula to calculate column H, email subject<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282A36\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"=IF([@[Bonus Award $]]&gt;0,\u201dCongratulations\u201d,\n\n\u201cA note from your manager\u201d)\" style=\"color:#f6f6f4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dracula-soft\" style=\"background-color: #282A36\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F286C4\">=<\/span><span style=\"color: #62E884\">IF<\/span><span style=\"color: #F6F6F4\">([@[Bonus Award $]]<\/span><span style=\"color: #F286C4\">&gt;<\/span><span style=\"color: #BF9EEE\">0<\/span><span style=\"color: #F6F6F4\">,\u201dCongratulations\u201d,<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">\u201cA note from your manager\u201d)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><em>Explanation: IF calculated bonus $ &gt; 0, use \u201cCongratulations\u201d as the email subject line. Otherwise use \u201cA note from your manager\u201d<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-7-enter-the-following-formula-to-calculate-column-i-email-body\">Step 7: Enter the following formula to calculate column I, email body<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282A36\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"=IF([@[Bonus Award $]]&gt;0, &quot;Hi &quot;&amp;[@[First Name]] &amp;&quot;. Your bonus this year will be &quot;&amp;TEXT([@[Bonus Award $]],&quot;$0,000&quot;)&amp;&quot; Congratulations!&quot;, &quot;Hello &quot; &amp;[@[First Name]]&amp;&quot;. Your manager will be contacting you soon to set up a review meeting. Thank you.&quot;)\" style=\"color:#f6f6f4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dracula-soft\" style=\"background-color: #282A36\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F286C4\">=<\/span><span style=\"color: #62E884\">IF<\/span><span style=\"color: #F6F6F4\">([@[Bonus Award $]]<\/span><span style=\"color: #F286C4\">&gt;<\/span><span style=\"color: #BF9EEE\">0<\/span><span style=\"color: #F6F6F4\">, <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">Hi <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #F6F6F4\">[@[First Name]] <\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">. Your bonus this year will be <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #62E884\">TEXT<\/span><span style=\"color: #F6F6F4\">([@[Bonus Award $]],<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">$0,000<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F6F6F4\">)<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\"> Congratulations!<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F6F6F4\">, <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">Hello <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F6F6F4\"> <\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #F6F6F4\">[@[First Name]]<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">. Your manager will be contacting you soon to set up a review meeting. Thank you.<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F6F6F4\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><em>Explanation: IF bonus is applicable, send a congratulations message. Otherwise send a message regarding an upcoming review meeting.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-8-enter-the-following-formula-to-calculate-column-j-single-send-link\">Step 8: Enter the following formula to calculate column J, single send link<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282A36\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"=HYPERLINK(&quot;mailto: &quot;&amp;[@[Send To]]&amp;&quot;?subject=&quot;&amp;[@[Email Subject]]&amp;&quot;&amp;body=&quot;&amp;[@[Email Body]],&quot;SEND&quot;)\" style=\"color:#f6f6f4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dracula-soft\" style=\"background-color: #282A36\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F286C4\">=<\/span><span style=\"color: #62E884\">HYPERLINK<\/span><span style=\"color: #F6F6F4\">(<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">mailto: <\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #F6F6F4\">[@[Send To]]<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">?subject=<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #F6F6F4\">[@[Email Subject]]<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">&amp;body=<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F286C4\">&amp;<\/span><span style=\"color: #F6F6F4\">[@[Email Body]],<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #E7EE98\">SEND<\/span><span style=\"color: #DEE492\">&quot;<\/span><span style=\"color: #F6F6F4\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><em>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.<\/em><\/p>\n\n\n\n<p><em>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.<\/em><\/p>\n\n\n\n<p>You now have a fully populated table:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"98\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/populated-table-700x98.jpg\" alt=\"Combined data (both manual inputs plus formulas) necessary for automating emails from Microsoft Excel\" class=\"wp-image-8061\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/populated-table-700x98.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/populated-table-768x108.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/populated-table-1536x215.jpg 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/populated-table-2048x287.jpg 2048w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Employee data, inputs plus formulas<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"vba\">Adding the VBA script<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-1-access-the-visual-basic-editor-window-either\">Step 1: Access the Visual Basic Editor Window, either:<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Using the keyboard shortcut Alt + F11<\/li>\n\n\n\n<li>Clicking your Developer tab, then clicking the Visual Basic icon. If you don\u2019t see your Developer tab, follow the steps below:\n<ul class=\"wp-block-list\">\n<li>Right click anywhere on the ribbon.&nbsp;<\/li>\n\n\n\n<li>Click Customize the Ribbon.<\/li>\n\n\n\n<li>Select Main Tabs<\/li>\n\n\n\n<li>Check Developer<\/li>\n\n\n\n<li>Click OK<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"526\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-emails-through-Excel-700x526.png\" alt=\"How to access the developer tab in Microsoft Excel\" class=\"wp-image-8062\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-emails-through-Excel-700x526.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-emails-through-Excel-400x300.png 400w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-emails-through-Excel-768x577.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-emails-through-Excel.png 968w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Enabling up the Excel Developer tab<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-2-find-your-file-name-in-the-project-explorer-area-in-the-upper-left-corner-of-the-window\">Step 2: Find your file name in the Project Explorer area in the upper left corner of the window<\/h3>\n\n\n\n<p>(As long as your file is open in Excel and was saved as a .xlsm file, it will appear in the list).<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"537\" height=\"411\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-HR-emails-through-Microsoft-Excel.jpg\" alt=\"The Visual Basic Editor window, Project Explorer area for Excel tutorial to automate emails\" class=\"wp-image-8063\" title=\"\"><figcaption class=\"wp-element-caption\">The Visual Basic Editor window, Project Explorer area<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-3-right-click-the-file-name-and-choose-insert-module\">Step 3: Right click the file name and choose Insert &gt; Module<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"575\" height=\"349\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Tutorial-on-how-to-automate-sending-emails-with-Microsoft-Excel.jpg\" alt=\"Adding a module to VBA project\" class=\"wp-image-8064\" title=\"\"><figcaption class=\"wp-element-caption\">Adding a Module to a VBA project<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-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 4: In the blank area on the right, copy paste the code that appears in <a href=\"#copy\">the appendix at the bottom of this tutorial<\/a><\/h3>\n\n\n\n<p>Once you copy it into the VBA Module it should look like the screenshot below (including boldfont for any code comments):<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"512\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-to-automate-sending-emails-with-Excel-700x512.jpg\" alt=\"Adding code to your VBA project Module in order to automate sending emails\" class=\"wp-image-8065\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-to-automate-sending-emails-with-Excel-700x512.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-to-automate-sending-emails-with-Excel-768x562.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-to-automate-sending-emails-with-Excel-1536x1124.jpg 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-to-automate-sending-emails-with-Excel.jpg 1538w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Adding code to your VBA project Module<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-5-click-the-x-in-the-upper-right-corner-to-return-to-the-main-excel-window\">Step 5: Click the X in the upper right corner to return to the main Excel window<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"572\" height=\"189\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Automating-sending-emails-with-VBA-in-Excel.jpg\" alt=\"Click the X to return to the main Excel window\" class=\"wp-image-8066\" title=\"\"><figcaption class=\"wp-element-caption\">Closing the Visual Basic Editor window<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"send\">Send your emails<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-1-add-a-shape-you-can-use-as-a-macro-button\">Step 1: Add a shape you can use as a macro button<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>From your Excel Ribbon, click Insert &gt; Shapes<\/li>\n\n\n\n<li>Click your preferred Shape. Here we use Rectangle: Rounded Corners<\/li>\n\n\n\n<li>The + symbol appears. From here you can \u201cdraw\u201d your shape, then right click to add some descriptive text to your shape. In this example we use \u201cSend to Select Employees\u201d<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"783\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Adding-macro-button-to-send-automated-emails-from-Microsoft-Excel-700x783.jpg\" alt=\"How to add a shape to your Excel worksheet\" class=\"wp-image-8067\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Adding-macro-button-to-send-automated-emails-from-Microsoft-Excel-700x783.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Adding-macro-button-to-send-automated-emails-from-Microsoft-Excel-768x859.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Adding-macro-button-to-send-automated-emails-from-Microsoft-Excel.jpg 1084w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Adding a Shape to your Excel worksheet<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-2-assign-your-new-macro-to-your-shape\">Step 2: Assign your new macro to your shape<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Right click on the Shape.<\/li>\n\n\n\n<li>Click \u2018Assign Macro\u2019<\/li>\n\n\n\n<li>The Assign Macro dialog appears. Select the \u2018This Workbook\u2019 option, then click the name of the macro.<\/li>\n\n\n\n<li>Click OK to close the Assign Macro dialog.<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"441\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Microsoft-Excel-tutorial-to-automate-sending-emails-700x441.jpg\" alt=\"Assign a macro to the shape in Microsoft Excel\" class=\"wp-image-8068\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Microsoft-Excel-tutorial-to-automate-sending-emails-700x441.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Microsoft-Excel-tutorial-to-automate-sending-emails-768x484.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Microsoft-Excel-tutorial-to-automate-sending-emails-1536x969.jpg 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Microsoft-Excel-tutorial-to-automate-sending-emails.jpg 1673w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Assigning a macro to a Shape on an Excel worksheet<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-3-highlight-to-select-one-or-more-employee-names-in-your-table\">Step 3: Highlight to select one or more employee names in your Table<\/h3>\n\n\n\n<p>You can select from any column in the Table. To select non-contiguous cells, use Ctrl + Click<\/p>\n\n\n\n<p>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!<\/p>\n\n\n\n<p>*Remember, you also can click the \u2018Single Send Link\u2019 column (Col J) hyperlink to compose a <em>single <\/em>email.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"310\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-macro-to-send-automated-emails-in-Microsoft-Excel-700x310.jpg\" alt=\"Click the macro button to automatically send emails\" class=\"wp-image-8069\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-macro-to-send-automated-emails-in-Microsoft-Excel-700x310.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-macro-to-send-automated-emails-in-Microsoft-Excel-768x340.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-macro-to-send-automated-emails-in-Microsoft-Excel-1536x680.jpg 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2021\/01\/Using-VBA-macro-to-send-automated-emails-in-Microsoft-Excel.jpg 2000w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">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.<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"conclusion\">Automating emails from Excel: Parting thoughts<\/h2>\n\n\n\n<p>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 <em>may<\/em> 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\u2019t be surprised if you earn a nice bonus for your contributions!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"copy\">Appendix A: Copy pasteable VBA code<\/h2>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282A36\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"Sub EmailAll()\nDim oApp As Object\nDim oMail As Object\nDim SendToName As String\nDim theSubject As String\nDim theBody As String\n\nFor Each c In Selection 'loop through (manually) selected records\n'''For each row in selection, collect the key parts of \n'''the email message from the Table\n \n    SendToName = Range(&quot;C&quot; &amp; c.Row)\n    theSubject = Range(&quot;H&quot; &amp; c.Row)\n    theBody = Range(&quot;I&quot; &amp; c.Row)\n        \n'''Compose emails for each selected record\n  '''Set object variables.\n    Set oApp = CreateObject(&quot;Outlook.Application&quot;)\n    Set oMail = oApp.CreateItem(0)\n    \n  '''Compose the customized message   \n    With oMail\n        .To = SendToName\n        .Subject = theSubject\n        .Body = theBody\n\n    ''' If you want to send emails automatically, use the Send option.\n    ''' If you want to generate draft emails and review before sending, use the Display option.\n    ''' Do not use both!\n    '''To activate your chosen option: Remove the single quote from the beginning of the code line, then\n    '''add the single quote back to the option you didn't choose\n    \n     .Display\n    '.Send\n    End With\nNext c\nEnd Sub\n\" style=\"color:#f6f6f4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dracula-soft\" style=\"background-color: #282A36\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F6F6F4\">Sub <\/span><span style=\"color: #62E884\">EmailAll<\/span><span style=\"color: #F6F6F4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">Dim oApp As Object<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">Dim oMail As Object<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">Dim SendToName As String<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">Dim theSubject As String<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">Dim theBody As String<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">For Each c In Selection <\/span><span style=\"color: #DEE492\">&#39;<\/span><span style=\"color: #E7EE98\">loop through (manually) selected records<\/span><\/span>\n<span class=\"line\"><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #F6F6F4\">For each row in selection, collect the key parts of <\/span><\/span>\n<span class=\"line\"><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #E7EE98\">the email message from the Table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    SendToName = Range(&quot;C&quot; &amp; c.Row)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    theSubject = Range(&quot;H&quot; &amp; c.Row)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    theBody = Range(&quot;I&quot; &amp; c.Row)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">        <\/span><\/span>\n<span class=\"line\"><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #F6F6F4\">Compose emails <\/span><span style=\"color: #F286C4\">for<\/span><span style=\"color: #F6F6F4\"> each selected record<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">  <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #E7EE98\">Set object variables.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    Set oApp = CreateObject(&quot;Outlook.Application&quot;)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    Set oMail = oApp.CreateItem(0)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">  <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #F6F6F4\">Compose the customized message   <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">    With oMail<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">        .To <\/span><span style=\"color: #F286C4\">=<\/span><span style=\"color: #F6F6F4\"> SendToName<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">        .Subject <\/span><span style=\"color: #F286C4\">=<\/span><span style=\"color: #F6F6F4\"> theSubject<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">        .Body <\/span><span style=\"color: #F286C4\">=<\/span><span style=\"color: #F6F6F4\"> theBody<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">    <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #E7EE98\"> If you want to send emails automatically, use the Send option.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #F6F6F4\"> If you want to generate draft emails and review before sending, use the Display option.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">    <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #E7EE98\"> Do not use both!<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #F6F6F4\">To activate your chosen option: Remove the single quote from the beginning of the code line, then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">    <\/span><span style=\"color: #DEE492\">&#39;&#39;&#39;<\/span><span style=\"color: #E7EE98\">add the single quote back to the option you didn<\/span><span style=\"color: #DEE492\">&#39;<\/span><span style=\"color: #F6F6F4\">t choose<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">     .Display<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F6F6F4\">    <\/span><span style=\"color: #DEE492\">&#39;<\/span><span style=\"color: #E7EE98\">.Send<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">    End With<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">Next c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E7EE98\">End Sub<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"has-text-align-center\">&#8211; &#8211; &#8211;<\/p>\n\n\n\n<p class=\"has-text-align-center\"><em>Thank you for reading our tutorial; we hope it&#8217;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&#8217;ll get back to you as soon as possible. The more details you can provide, the better. <a href=\"https:\/\/postimages.org\/\" target=\"_blank\" rel=\"noopener\">Please use this site to upload any screenshots you&#8217;d like to share with your question.<\/a><\/em><\/p>\n\n\n\n<div  class=\"mx-auto bbl_cta_block bk-light\">\n\t<a class=\"bbl_cta_block-blockcontent bbl_cta_block-link d-block overflow-hidden position-relative rounded-4 text-decoration-none\" href=\"https:\/\/beebole.com\/blog\/excel-power-query-for-business-intelligence\" title=\"Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal\">\n\t\t\t\t\t<div class=\"bbl-blue-dot object-fit-cover position-absolute start-0 top-0\" style=\"background-image: url(https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/blue-dot.a385a5.svg)\"><\/div>\n\t\t\t\t<div class=\"bottom-0 end-0 object-fit-cover position-absolute bbl-orange-dot\" style=\"background-image: url(https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/orange-dot.47ecad.svg)\"><\/div>\n\n\t\t<div class=\"bbl_cta_block-row align-items-center d-flex flex-md-row justify-content-center mx-0 no-gutters position-relative row\">\n\t\t\t<div class=\"bbl_cta_block-img-col col d-flex justify-content-start pe-md-2 pe-lg-4 px-0\">\n\t\t\t\t<img\n\t\t\t\t\talt=\"Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal\"\n\t\t\t\t\tclass=\"d-block h-auto mw-lg-100\"\n\t\t\t\t\tloading=\"lazy\"\n\t\t\t\t\theight=\"240\"\n\t\t\t\t\tsrc=\"https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/promotion-post.9422b6.png\"\n\t\t\t\t\twidth=\"360\"\n\t\t\t\t\/>\n\t\t\t<\/div>\n\t\t\t<div class=\"bbl_cta_block-text-col col mt-md-0 ps-0\">\n\t\t\t\t\t\t\t\t\t<div class=\"mb-1\"><div class=\"bbl_cta_block-label lh-base mb-2 mb-md-4\">RELATED POST<\/div><\/div>\n\t\t\t\t\t\t\t\t<div class=\"bbl_cta_block-title lh-base\">Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal<\/div>\n\t\t\t\t\t\t\t\t\t<div>\n\t\t\t\t\t\t<div class=\"bbl_cta_block-button h6 lh-1 mb-0 mt-3\">\n\t\t\t\t\t\t\tRead more\t\t\t\t\t\t\t<svg class=\"ms-2\" width=\"15\" height=\"14\" viewBox=\"0 0 15 14\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n\t\t\t\t\t\t\t\t<path d=\"M5.9375 1.09375L6.625 0.40625C6.9375 0.125 7.40625 0.125 7.6875 0.40625L13.7812 6.46875C14.0625 6.78125 14.0625 7.25 13.7812 7.53125L7.6875 13.625C7.40625 13.9062 6.9375 13.9062 6.625 13.625L5.9375 12.9375C5.65625 12.625 5.65625 12.1562 5.9375 11.8438L9.71875 8.25H0.75C0.3125 8.25 0 7.9375 0 7.5V6.5C0 6.09375 0.3125 5.75 0.75 5.75H9.71875L5.9375 2.1875C5.65625 1.875 5.625 1.40625 5.9375 1.09375Z\" fill=\"#313358\" \/>\n\t\t\t\t\t\t\t<\/svg>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t<\/a>\n<\/div>\n\n\n<p>\u2014<\/p>\n\n\n\n<p>Photo by Ed Hardie on Unsplash<\/p>\n<div class=\"bbl-post-disclaimer\">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.<\/div>","protected":false},"excerpt":{"rendered":"<p>\u201cAnnual compensation and bonus planning is easy, \u201d said no human resource professional, ever. That&#8217;s precisely why we&#8217;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. [&hellip;]<\/p>\n","protected":false},"author":31,"featured_media":10786,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[123],"tags":[2890,3989,4012,4013],"class_list":["post-8049","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-productivity-time-management","tag-hr","tag-excel","tag-tutorials","tag-templates"],"acf":[],"_links":{"self":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/8049","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/users\/31"}],"replies":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/comments?post=8049"}],"version-history":[{"count":21,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/8049\/revisions"}],"predecessor-version":[{"id":13405,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/8049\/revisions\/13405"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media\/10786"}],"wp:attachment":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media?parent=8049"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/categories?post=8049"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/tags?post=8049"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}