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

We store different kinds of information in our Excel worksheet. For instance, it can have Email addresses of important people or other companies. And we may need to send emails to those addresses when it’s necessary. But, copying the address over and again for each one of them is quite tiring and time-consuming. In this article, we’ll show you the easy and effective Methods to Send Multiple Emails from an Excel Spreadsheet.


Download Practice Workbook

Download the following workbook to practice by yourself.


2 Easy Methods to Send Multiple Emails from Excel Spreadsheet

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents the Salesman and their Email addresses. Here, we’ll send the emails to every one of them taking the addresses from this Excel Spreadsheet.

Send Multiple Emails from Excel Spreadsheet


1. Send Multiple Emails from Excel Spreadsheet with Mail Merge in Word

In our first method, we will make use of MS Word and the Mail Merge feature in Word to Send Multiple Emails from Excel Spreadsheet. Therefore, follow the steps below to perform the task.

STEPS:

  • First, open a blank Word file.
  • Then, type your desired message.
  • After that, go to Mailings Select Recipients Use an Existing List.

Send Multiple Emails from Excel Spreadsheet with Mail Merge in Word

  • As a result, the Select Data Source dialog box will pop out.
  • Subsequently, select the Excel file where the Email addresses are stored.
  • Press Open.

Send Multiple Emails from Excel Spreadsheet with Mail Merge in Word

  • Consequently, the Select Table dialog box will appear.
  • There, choose your desired sheet and press OK.

Send Multiple Emails from Excel Spreadsheet with Mail Merge in Word

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

Send Multiple Emails from Excel Spreadsheet with Mail Merge in Word

  • Thus, it’ll return the message like it’s demonstrated below.

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

  • Accordingly, the Merge to E-mail dialog box will emerge.
  • Choose the header Email in the To field and type your Subject line (Greetings) as required.
  • Lastly, press OK and it’ll dispatch the mails to all the recipients.

Read More: How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)


Similar Readings


2. Apply Excel VBA for Sending Multiple Emails from Spreadsheet

However, you can avoid some hassle if you use the VBA code to carry out the operation. In this method, we’ll show 3 different examples of Sending Multiple Emails from a Spreadsheet through Excel VBA.


2.1 Mass Emails to a List

In this example, you’ll see how to mail a common message to everyone. So, learn the following steps.

STEPS:

  • Firstly, select Developer Visual Basic.

Apply Excel VBA for Sending Multiple Emails from Spreadsheet

  • As a result, the VBA window will pop out.
  • Now, click Insert Module.
  • Next, 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.
  • Consequently, the Outlook window will appear and you’ll see all the recipients in the To field.
  • At last, press Send.


2.2 Emails with Multiple Attachments

Additionally, if you have more than one file to attach and you have to send them to multiple email addresses, follow the below process.

STEPS:

  • Click the Developer tab at first.
  • Then, select Visual Basic.
  • After that, 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 it and run the code by pressing F5.
  • As a result, the Outlook window will appear and you’ll see the desired file attachments.

Apply Excel VBA for Sending Multiple Emails from Spreadsheet


2.3 Emails Based on Cell Value

We’ll show another example where the Excel file will automatically send an email to multiple addresses when a certain cell value is different from our requirement. In the following dataset, we have 50 in cell B5. Our code will send emails if it’s below 100. Now, learn the following process to perform the task.

Apply Excel VBA for Sending Multiple Emails from Spreadsheet

STEPS:

  • First of all, select the sheet and right-click on the mouse.
  • Then, select View Code.

Apply Excel VBA for Sending Multiple Emails from Spreadsheet

  • In the pop-out dialog box, copy and paste the below code.
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

  • Afterward, save the file and press F5 to run it.
  • Consequently, you’ll see the output in the Outlook window.

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


Conclusion

Henceforth, you will be able to Send Multiple Emails from Excel Spreadsheet following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo