When you need to send a mass email to a large group of people, you’ll need an automated process that can handle repetitive tasks quickly. Creating an Excel file with a list of emails is the most common way to send mass emails. So, in this tutorial, we will show you how to send email from an Excel list automatically to a large number of people.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Handy Approaches to Send Email from an Excel List
We’ve included a data set with some people’s names, as well as their emails and registration numbers, in the image below. From the Excel list, we must send emails to each individual. To accomplish this, we’ll use Microsoft Word’s Mail Merge function, followed by a VBA code to send emails to preferred individuals from the existing list.
1. Apply Mail Merge Function to Send Multiple Emails from an Excel List
⇒ Step 1: Open a New Word File
- Open a blank Word document.
- Click on the Mailings tab.
- From the Select Recipients option, choose the Use an Existing List option.
⇒ Step 2: Link the Excel List to the Word File
- Select the Excel file where you have created the list and click on Open to open the file.
- Select the sheet number where you have written the list.
- Then, click OK.
⇒ Step 3: Insert Fields
- From the Mailings option, click on the Insert Merge Field option to enter the fields you want to insert.
- Firstly, insert the Name field by clicking on it and in the preferred position of the general mail.
- As the image is shown below, after adding the Name field, it will show as the variable of every person’s name.
- Similarly, place the Reg field wherever you want in the text message.
- Therefore, it will appear as the image shown below.
⇒ Step 4: Check the Preview Results
- Click on the Preview Results to see the final preview before sending the email.
- The screenshot below shows how a sample email will look.
⇒ Step 5: Merge Emails
- To merge the emails, click on the Finish & Merge option.
- To open the Merge to E-mail box, select the Send Email Messages option.
- In the To box, select the Email option.
- Type a subject line you prefer in the Subject line box.
- The mail format will be HTML by default, so you don’t need to change it.
- In the Send Records option, click on All.
- Finally, click on OK to send the emails to multiple recipients at the same time.
- Consequently, all the emails will be sent through your associated Outlook Check your Outlook sent option to confirm that emails have been sent.
- When you open a sent email, you’ll notice that each field is filled with the information of a specific person.
- How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
- [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to See Who Is in a Shared Excel File (With Quick Steps)
- How to Enable Share Workbook in Excel
2. Run a VBA Code to Send Emails from Selection of a Range
With the blessings of VBA, you can create a program to send emails from an Excel list with a preferable selection of the range. Follow the outlined steps below to do the task.
⇒ Step 1: Create a Module
- To open the VBA Macro, press Alt + F11 .
- Click on the Insert tab.
- Select the Module option to create the Module.
⇒ Step 2: Paste VBA Codes
- In the new Module, paste the following VBA code.
#If VBA7 And Win64 Then 'Ptr is used to change for operating 32 bit to 64 bit Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal wnd As LongPtr, ByVal lpDirect As String, _ ByVal Parameters As String, ByVal File As String, ByVal Operation As String, _ ByVal nCmd As Long) As LongPtr #Else #End If Sub SendExcelListEMail() 'Declare the variables Dim xMailAdd As String Dim xRegCode As String Dim xBody As String Dim xURLink As String Dim xRngCell As Range Dim xIntRg As Range Dim xSelectTxt As String Dim k As Integer Dim p As Double On Error Resume Next 'Select range select adddress xSelectTxt = ActiveWindow.RangeSelection.Address 'Create a Input box for the range selection Set xIntRg = Application.InputBox("Please Input Excel data range:", "ExcelDemy", xSelectTxt, , , , , 8) 'Apply If condition to specify column numbers for the operation If xIntRg Is Nothing Then Exit Sub If xIntRg.Columns.Count <> 3 Then 'Show the result in a msg box for not meeting the condition MsgBox "Error with Region Selection, please confirm", , "ExcelDemy" Exit Sub End If 'Apply For loop to run operation in each row individually For k = 1 To xIntRg.Rows.Count ' Collect the email address and set to the variable xMailAdd = xIntRg.Cells(k, 2) ' Give a subject for the Email xRegCode = "ExcelDemy Registration No." ' Type the body of the email xBody = "" ' Insert Names with the variable xIntRg xBody = xBody & "Greetings " & xIntRg.Cells(k, 1) & "," & vbCrLf & vbCrLf xBody = xBody & " Here is your ExcelDemy Registration No. " ' Insert Registration No. with the variable xIntRg xBody = xBody & xIntRg.Cells(k, 3).Text & "." & vbCrLf & vbCrLf xBody = xBody & "We are really glad to have you visit in our site, keep supporting us." & vbCrLf xBody = xBody & "ExcelDemy Team" ' Define spaces with (hex) xRegCode = Application.WorksheetFunction.Substitute(xRegCode, " ", "%20") xBody = Application.WorksheetFunction.Substitute(xBody, " ", "%20") ' Specify to replace carriage returns with(hex) xBody = Application.WorksheetFunction.Substitute(xBody, vbCrLf, "%0D%0A") ' Generate the URL Link xURLink = "mailto:" & xMailAdd & "?subject=" & xRegCode & "&body=" & xBody ' Use the Link to start emailing ShellExecute 0&, vbNullString, xURLink, vbNullString, vbNullString, vbNormalFocus ' three seconds interval for sending keystrokes Application.Wait (Now + TimeValue("0:00:03")) Application.SendKeys "%s" Next End Sub
⇒ Step 3: Run the Program
- Press F5 to run the program.
- Select the range in the input box.
- Click OK to send the emails.
- As a result, previews of sending emails will appear, as shown in the image below.
- Finally, you can check the sent emails for confirmation.
I hope this article has given you a tutorial about how to send mass emails from an Excel list. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
Please contact us if you have any questions. Also, feel free to leave comments in the section below.
We, the Exceldemy Team, are always responsive to your queries.
Stay with us and keep learning.
- How to Send Automatic Email from Excel to Outlook (4 Methods)
- How to Send Email from Excel with Body Using a Macro (with Easy Steps)
- Excel Macro: Send Email to an Address in Cell (2 Easy Ways)
- How to Automatically Send Email from Excel Based on Date
- How to Send Multiple Emails from Excel Spreadsheet (2 Easy Methods)
- Macro to Send Email from Excel with Body (3 Useful Cases)