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.

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.

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.

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.

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

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.

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.

2. Using 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.

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.

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.

STEPS:

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

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.

Download Practice Workbook

Download the following workbook to practice by yourself.

Send Multiple Emails.xlsm

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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

asad Jun 23, 2023 at 12:51 PM

In my particular case, the email client is IBM Notes version 10. Please guide how can I use the code for bulk emails from excel.
- Reply
  Lutfor Rahman Shimanto Jun 25, 2023 at 5:04 PM
  Hello ASAD
  
  Thanks for reaching out and posting your query. You mentioned using IBM Notes Version 10 as your email client. You must modify the code to work with IBM Notes instead of Outlook to achieve your goal. I am introducing you to an Excel VBA code that can send emails with the help of IBM Notes.
  
  Excel VBA Code:
```
Sub SendEmailWithIBMNotes()
    
    Dim ws As Worksheet
    Dim i, lastRow As Long
    Dim NotesApp As Object
    Dim NotesMail As Object
    Dim RecipientEmail As String
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
    
    
    For i = 2 To lastRow
        
        Set NotesApp = CreateObject("Notes.NotesUIWorkspace")
        Set NotesMail = NotesApp.ComposeDocument("", "", "Memo")
        
        RecipientEmail = ws.Range("B" & i).Value
        
        NotesMail.Document.ReplaceItemValue "SendTo", RecipientEmail
        NotesMail.Document.ReplaceItemValue "Subject", "Email with IBM Notes"
        NotesMail.Document.ReplaceItemValue "Body", "Greetings Everyone," & vbNewLine & "Visit our ExcelDemy page to learn more about the various aspects of Excel. You can also submit your Excel issue to our ExcelDemy Forum." & vbNewLine & "Regards."
    
    Next i
    
    Set NotesMail = Nothing
    Set NotesApp = Nothing
    
End Sub
```
  Things to Keep in Mind:
  
  1. You must have IBM Notes installed on your PC.
  
  2. Keep the recipient’s email throughout column B starting from B2 in Sheet1.
  
  This idea helps you reach your goal. Don’t hesitate to contact us again if you have any more questions.
  
  Regards
  Lutfor Rahman Shimanto

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

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

2. Using Excel VBA for Sending Multiple Emails from Spreadsheet

2.1. Mass Emails to a List

2.2. Emails with Multiple Attachments

2.3. Emails Based on Cell Value

Conclusion

Related Articles

Leave a reply Cancel reply