How to Send Multiple Emails from Excel Spreadsheet (2 Easy Methods)

We’ll use a sample dataset as an example. The dataset represents the Salesman and their Email addresses. We’ll send an email to everyone.

Method 1 – Send Multiple Emails from an Excel Spreadsheet with Mail Merge in Word

Steps:

• Open a blank Word file.
• Enter your desired message.
• Go to Mailings, then choose Select Recipients and select Use an Existing List.

• The Select Data Source dialog box will open.
• Select the Excel file where the Email addresses are stored.
• Press Open.

• The Select Table dialog box will appear.
• Choose your desired sheet and press OK.

• Select the word you need to replace for each mail. In this example, choose Wilham.
• Under the Mailings tab, select Salesman from the Insert Merge Field drop-down.

• This’ll return a field that will get populated based on the values in the table.

• If you want to see a preview of your mail from the recipient’s perspective, click Preview Results.

• Under the Finish & Merge drop-down, select Send Email Messages.

• The Merge to E-mail dialog box will open.
• Choose the header Email in the To field and type your Subject line (Greetings) as required.
• Press OK.

Read More: How to Send Excel File to Email Automatically

Method 2 – Using Excel VBA for Sending Multiple Emails from a Spreadsheet

Case 2.1 – Mass Emails to a List

Steps:

• Select Developer and choose Visual Basic.

• The VBA window will open.
• Click Insert and select Module.
• Copy the following code and paste it into the Module window.

Sub massEmails()
Dim appOutlook As Object
Dim Email As Object
Dim mailbody, mailto As String
Dim i, j As Integer
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)
For i = 2 To 5
    mailto = mailto & Cells(i, 2) & ";"
Next i
ThisWorkbook.Save
Email.To = mailto
Email.Subject = "Important Notice"
Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Company Rules." & vbNewLine & "Regards."
Email.Display
End Sub

• Save the file and press F5 to run the code.
• The Outlook window will appear, and you’ll see all the recipients in the To field.
• Press Send.

Read More: Automatically Send Emails from Excel Based on Cell Content

Case 2.2 – Emails with Multiple Attachments

Steps:

• Go to the Developer tab.
• Select Visual Basic.
• In the VBA window, select Module under Insert.
• In the Module window, copy and paste the below code.

Sub attachments()
Dim appOutlook As Object
Dim Email As Object
Dim source, mailto As String
Dim i, j As Integer
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)
For i = 2 To 5
    mailto = mailto & Cells(i, 2) & ";"
Next i
For j = 2 To 5
    source = "D:\SOFTEKO\how to send multiple emails from excel spreadsheet\" & Cells(j, 3)
    Email.attachments.Add source
Next
ThisWorkbook.Save
source = ThisWorkbook.FullName
Email.attachments.Add source
Email.To = mailto
Email.Subject = "Important Sheets"
Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
Email.Display
End Sub

• Save and run the code by pressing F5.
• The Outlook window will appear, and you’ll see the desired file attachments.

Case 2.3 – Emails Based on Cell Value

In the following dataset, we have Quantities. Our code will send emails the Quantity is below 100.

Steps:

• Right-click on the sheet name.
• Select View Code.

• Copy and paste the following code into the code window.

Dim rn As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set rn = Intersect(Range("B5"), Target)
    If rn Is Not hing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value < 100 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub MailCellvalues()
Dim appOutlook As Object
Dim Email As Object
Dim mailbody, mailto As String
Dim i, j As Integer
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)
For i = 2 To 5
    mailto = mailto & Cells(i, 3) & ";"
Next i
ThisWorkbook.Save
Email.To = mailto
Email.Subject = "Important Notice"
Email.Body = "Greetings Everyone," & vbNewLine & "Please raise B5 above 100." & vbNewLine & "Regards."
Email.Display
End Sub

• Save the file and press F5.
• You’ll see the output in the Outlook window.

Read More: How to Send an Editable Excel Spreadsheet by Email

Download the Practice Workbook

Related Articles

• How to Send Bulk Email from Outlook Using Excel
• How to Send Automatic Email from Excel to Outlook
• How to Send Email Automatically When Condition Met in Excel
• How to Send Email If Conditions Met in Excel
• How to Automatically Send Email from Excel Based on Date

<< Go Back To Send Email from Excel | Learn Excel