Do you need to send a lot of emails every day to supervisors, clients, vendors, and many more entities that are linked with the business?

Are you tired of copying and pasting content from Excel manually into Outlook or Gmail and then investing hours in formatting your emails? Get rid of such trouble and send emails smartly and effortlessly by learning how to send emails from Excel.

Microsoft Excel and Outlook or Gmail are indispensable tools for any business. These apps are also equally useful in your personal and freelancing life. Excel, Excel VBA, Office Scripts, and Power Automate come with the necessary features so you can automate business or personal emailing tasks.

Read this article until the end to learn the best steps to inject automation in Excel and send emails directly from the Excel workbook.

Reasons to Send Email From Excel

The primary reason behind using Excel to send emails is to automate the process when you need to send many emails during your workday. Suppose, there’s a table of clients with their invoice amounts and you must send a reminder so the clients pay on time.

Do you go back and forth between the Excel and Outlook apps to copy data from the former to the latter, and draft an email separately for all the emails you must send? Absolutely not! You follow the methods mentioned below in this article to automate the whole emailing process when some of the content of the Email is in an Excel workbook.

A few other major reasons that I can think of are as below:

1. You can use Excel to generate automated reports and send them via email to ensure timely delivery to relevant recipients in a single click.
2. Also, you can share the results of data analysis or calculations with colleagues or clients easily.
3. You can always use Excel to schedule and send reminders for meetings, appointments, or deadlines.
4. For small-scale email marketing, Excel can be used to manage contact lists and send promotional materials.
5. Excel can help you manage guest lists and send out invitations or updates for events.
6. Automate HR processes like sending offer letters, onboarding information, or training materials to new hires.
7. You can schedule automatic emailing of performance reports to managers or clients to showcase achievements and progress.

How to Send Email From Excel Using Excel VBA

This is one of the easiest and most intuitive ways to automate bulk email sending by referring to datasets in an Excel worksheet. Here are the instructions you must follow:

Create A VBA Script and Save the Workbook in XLSM Format

1. Press Alt + F11 to bring up the Excel VBA Editor tool.
2. There, click the Insert button on the toolbar.
3. Create a new module by clicking on the Module option.
4. Inside the new module, copy and paste the following VBA script:

Sub SendEmails()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long

    ' Create a reference to the Outlook application
    Set OutApp = CreateObject("Outlook.Application")

    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to your sheet name

    ' Find the last row with data in column A
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Loop through each row with data
    For i = 2 To LastRow ' Assuming the header is in row 1
        ' Create a new email
        Set OutMail = OutApp.CreateItem(0)

        ' Set email properties
        With OutMail
            ' Set the email address from Column D
            .To = ws.Cells(i, "D").Value
            ' Set the subject with the Invoice ID from Column A
            .Subject = "Pending payment for invoice: Invoice ID " & ws.Cells(i, "A").Value
            ' Create the email body with the requested information
            .Body = "Dear " & ws.Cells(i, "C").Value & "," & vbNewLine & vbNewLine & _
                    "We hope this message finds you well. This is a reminder regarding the pending payment for the following invoice:" & vbNewLine & vbNewLine & _
                    "Invoice ID: " & ws.Cells(i, "A").Value & vbNewLine & _
                    "Date of Invoice: " & ws.Cells(i, "B").Value & vbNewLine & _
                    "Client Name: " & ws.Cells(i, "C").Value & vbNewLine & _
                    "Amount Due: $" & ws.Cells(i, "E").Value & vbNewLine & vbNewLine & _
                    "Please make the payment at your earliest convenience." & vbNewLine & vbNewLine & _
                    "Thank you for your prompt attention to this matter." & vbNewLine & vbNewLine & _
                    "Sincerely," & vbNewLine & "Your Company Name"

            ' Send the email
            .Send
        End With

        ' Release the email object
        Set OutMail = Nothing
    Next i

    ' Release the Outlook application object
    Set OutApp = Nothing
End Sub

5. Click the Save button.
6. Choose No on the pop-up that shows up.

7. On the Save As dialog box, choose a destination for the Excel file.
8. Expand the Save as type drop-down menu and choose the Excel Macro-Enabled Workbook option.
9. Click Save.

Run the VBA Script

1. Press Alt + F8 to launch the Macro dialog box.
2. Choose the SendEmails macro from the list.
3. Click Run to execute.

Excel shall instruct the Outlook app to create the email drafts and send them in the background. This is a dynamic code so when you change the table or data set, the emails will update accordingly the next time you send emails.

You must modify the code to match the cell references to your Excel worksheet. Refer to the comments in the above code to know how to customize the script.

For example the comment “Change "Sheet2" to your sheet name” tells you how to put your own worksheet name in the above script in place of "Sheet2" in the code element “Set ws = ThisWorkbook.Sheets("Sheet2").”

The above image shows a bunch of emails I sent in a second using the above Excel VBA script.

How to Send Email From Excel Using Office Scripts

Here’s how you can use Office Scripts to create a mailto: hyperlink to draft emails in Outlook or default email client and then send emails from the email client app:

1. Go to the Automate tab in the Excel ribbon.
2. Click on the New Script option inside the Scripting Tools command block.
3. Copy and paste the following Office Scripts code inside the Code Editor panel:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range G2 on selectedSheet
	selectedSheet.getRange("G2").setFormulaLocal("=HYPERLINK(\"mailto:\"&D2&\"?subject=Pending Invoice ID\"&A2&\"&body=Hi the mentioned invoice ID is pending\"&\" \"&A2&\" \"&F2,\"Send Email\")");
	// Paste to range G3:G6 on selectedSheet from range G2 on selectedSheet
	selectedSheet.getRange("G3:G6").copyFrom(selectedSheet.getRange("G2"), ExcelScript.RangeCopyType.all, false, false);
}

4. Click the Save script button to save the code.
5. Click the Run button to execute the script.

The above script will create Send Email hyperlinks beside the row of table data from which you want to send emails. Click the Send Email link and a draft email will show up in the Outlook app. Now, hit Send on Outlook to send the email to each recipient.

Here’s how you can personalize the code so that it works on your own dataset:

• D2 should be replaced with the cell address of the recipient’s email.
• A2 should also be replaced with another cell address and the value of that cell will show up on the subject line.
• If you want to show a specific value in the email body, enter the cell address for that value in place of F2.
• The Send Email hyperlink will show up in the cell G2. Change this if you want.
• The cell reference "G3:G6" in selectedSheet.getRange("G3:G6") copies the formula from G2 to the range mentioned. Change the range reference according to your dataset and also extend the range if your dataset is larger than the example.

How to Send Email From Excel Using Power Automate

Find below the steps to send an email using Power Automate from an Excel table:

Install and Activate Required Add-Ins

1. Click the Developer tab on the Excel ribbon menu.
2. Go to the Add-ins block and click on the Add-ins command.
3. On the Office Add-ins dialog box, click the Store button.
4. Type Power Automate in the Search box and hit Enter.
5. Click the Add button on the right side of the add-in.

6. The Power Automate Excel add-in will show up in the right side panel.
7. Click the Sign in link to choose your Microsoft 365 subscription email from the popup.

You’re all set! Next, you’ll learn to create a Flow in Power Automate by integrating data from the table in your Excel worksheet.

Create a Flow in Power Automate Add-In

1. Click New on the top border of the add-in to start creating a Flow.
2. Choose Instant cloud flow from the context menu.
3. Give a new name for the Flow by clicking and typing in place of the Untitled name field.

4. Click the following fields in the Power Automate add-in to add the relevant data so the tool can recognize your Excel worksheet and its tables:
   • Location
   • Document Library
   • File
   • Table
5. Once done adding the worksheet and table, click the header to collapse the item.
6. Now, click the Next step button.

7. The Choose an operation dialog will open in the add-in.
8. Type Email in the Search box to get a few email connectors for Power Automate.
9. Choose the email connector of your choice from the list, like Office 365 Outlook.
10. Now, click on the Send an email (V2) option in the Actions menu.

Send the Emails

1. The Send an email (V2) action will show up the following email elements:
   • To
   • Subject
   • Body
2. Click the Add dynamic content hyperlink.
3. Now, you’ll be able to quote data from the Excel worksheet table wherever needed within the Email.

4. Inside the To field, add the Email ID column of the table.
5. Write down a subject line text and add relevant parameters, like Invoice ID.

6. Similarly, go to the Body of the email and type the whole email.
7. Here, add parameters from the Excel worksheet table as and when required.

8. Click Save to complete the email-creating process.
9. Now, go back to the main window of the Power Automate add-in.

10. You should see the Flow you just created just below the Name section.
11. Highlight the rows against which you want to send emails except the column headers.
12. Click the Run button close to the Send an email by row Flow.
13. Click the Continue and Run flow buttons on the next two screens to complete sending the emails.

The above image shows the automatic emails I sent using Outlook from Email using a Power Automate Flow.

How to Send Email From Excel Using a Mailto Hyperlink

The HYPERLINK function in Excel also allows you to automate sending emails from Excel. However, you need to write complex formulas to achieve complete automation. Find below a basic HYPERLINK function that pops up the email client of your PC and drafts an email for sending

=HYPERLINK("mailto:"&D2&"?subject=Pending Invoice ID"&A2&"&body=Hi the mentioned invoice ID is pending"&" "&A2&" "&F2,"Send Email")

Here’s how you can use this function in your worksheet:

• The recipient’s email should be in the cell D2 or change it to another cell reference according to your worksheet.
• A2 lets you insert an invoice ID or anything else important that you want to mention in the subject line from the table dynamically. In this tutorial, it’s Invoice ID.
• F2 refers to the Amount Due column in the Table which is also formatted with the TEXT function so that the original email can show the $ symbol.

The above image shows the original formula along with the Excel table so you can get a clear idea about the method.

Upon clicking the Send Email hyperlink, you’ll get the above email in Outlook if that’s the default email client of your PC.

Conclusions

Harnessing the power of Office Scripts, Excel VBA, and Power Automate to send emails directly from Excel is a game-changer for streamlining your workflow and enhancing productivity.

Whether you’re automating reports, notifications, or communication with clients and colleagues, these tools offer versatile solutions to simplify your tasks.

Have you tried any of these methods or encountered any challenges while implementing them? I’d love to hear about your experiences, so please feel free to share your thoughts and insights in the comments below.