This post will guide you how to send mass emails to a list from an Excel Spreadsheet using Outlook. How do I send email to a list recipients using Excel and Outlook.
Table of Contents
1. Sending Email to A List of Recipients
Assuming that you have a list of data in a worksheet which contain name, email address and subject columns. and you want to send a mail to each email address in Column B. How to accomplish it. You can use the Word’s Mail Merge feature, select data source from your Excel, and then you can send email messages to each email addresses. Just do the following steps:Step1: Launch a new word document, and go to the Mailings tab, and select Select Recipients button, then select Use an Existing List from the dropdown menu list. and the Select Data Source dialog will open.
Step2: choose your workbook which contain your data in the Select Data Source dialog box, and click on Open button. then the Select Table dialog will open.
Step3: choose the worksheet which contain your data. click OK button.
Step4: then you can insert name filed into your mail content, such as: you can go to Mailings tab, and click on the Insert Merge Field button under the Write & Insert group. and select Name from the dropdown menu list to insert Name field into your mail content.
Step5: you can click on the Finish & Merge command, and then select Send Email Messages from the dropdown menu list. and the Merge to E-mail dialog will open.
Step6: you need to set message options in the Merge to Email dialog box, and choose email address value in the To drop down list, and choose subject value from the Subject line list box, then select All option in the Send records section. click Ok button.
Step7: the emails should be sent to each email address in Column B.
2. Sending Email to A List of Recipients Using VBA Macro
You can also use an Excel VBA macro to send email to a list of recipients. Just do the following steps:
Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Step2: then the “Visual Basic Editor” window will appear.
Step3: click “Insert” ->”Module” to create a new module.
Step4: paste the below VBA code into the code window. Then clicking “Save” button.
Sub SendEmail()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
With Mail_Object.CreateItem(o)
.Subject = Range("B1").Value
.To = Range("A" & i).Value
.Body = Range("B2").Value
'.Send
.display 'disable display and enable send to send automatically
End With
Next i
MsgBox "E-mail successfully sent", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub
Step5: back to the current worksheet, then run the above excel macro. Click Run button.
This will send an E-Mail to every address in Column “A” starting at row 2 down to the last row. And the email body is in Cell B2. And the email subject is in Cell B1.
3. Video: Use Outlook, Word and Excel to Send a Mass Emails
This Excel video tutorial, we’ll explore two methods to send emails to a list of recipients. We’ll start by using the Office Word application to compose and send emails, followed by creating a VBA macro for automated emailing.
Leave a Reply
You must be logged in to post a comment.