How to Send Email from Excel List (2 Effective Ways)

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.

Sample Data

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.

Handy Approaches to Send Email from an Excel List

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.

Handy Approaches to Send Email from an Excel List

  • Select the sheet number where you have written the list.
  • Then, click OK.

Handy Approaches to Send Email from an Excel List

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.

Handy Approaches to Send Email from an Excel List

  • As the image is shown below, after adding the Name field, it will show as the variable of every person’s name.

Handy Approaches to Send Email from an Excel List

  • Similarly, place the Reg field wherever you want in the text message.

Handy Approaches to Send Email from an Excel List

  • Therefore, it will appear as the image shown below.

Handy Approaches to Send Email from an Excel List

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.

Handy Approaches to Send Email from an Excel List

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.

Handy Approaches to Send Email from an Excel List

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

Handy Approaches to Send Email from an Excel List

  • Consequently, all the emails will be sent through your associated Outlook Check your Outlook sent option to confirm that emails have been sent.

Handy Approaches to Send Email from an Excel List

  • When you open a sent email, you’ll notice that each field is filled with the information of a specific person.

Handy Approaches to Send Email from an Excel List

Notes. Microsoft Outlook must be your default mailing application. If you use another mailing application, you will not be able to send emails by this procedure.

Read More: How to Send Email Automatically When Condition Met in Excel


Similar Readings


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.

Sample Data

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

Sample Data

Step 3: Run the Program

  • Press F5 to run the program.
  • Select the range in the input box.
  • Click OK to send the emails.

Sample Data

  • As a result, previews of sending emails will appear, as shown in the image below.

Sample Data

  • Finally, you can check the sent emails for confirmation.

Sample Data

Read More: How to Send Email If Conditions Met in Excel (3 Easy Methods)


Conclusion

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.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo