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.
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.
Read More: How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
Similar Readings
- How to Automatically Send Email from Excel Based on Date
- Send Email from Excel with Body Using a Macro (with Easy Steps)
- How to Send Excel File to Email Automatically (3 Suitable Methods)
- How to Send Email If Conditions Met in Excel (3 Easy Methods)
- Automatically Send Emails from Excel Based on Cell Content (2 Methods)
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.
- 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.
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
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to Send Email Automatically When Condition Met in Excel
- [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)
- Macro to Send Email from Excel (5 Suitable Examples)
- Macro to Send Email from Excel with Body (3 Useful Cases)
- Excel Macro: Send Email to an Address in Cell (2 Easy Ways)