How to send personalized mass emails to a list from Excel via Outlook?
For example, I have the following data range in a worksheet which contains Name, Email Address, Registration Code columns, and now, I want to send a message with a personalized greeting and their own Registration Code to the separated Email Addresses in column A. To solve this problem, the following methods may help you.
Send personalized mass emails to a list from Excel with Mail Merge function
Send personalized mass emails to a list from Excel with VBA code
Send personalized mass emails to a list with different attachments by using Kutools for Excel
Send personalized mass emails to a list from Excel with Mail Merge function
With the Word’s Mail Merge function, you can finish this job quickly and easily, please do with the following step by step:
1. Launch a new blank Word document, and then click Mailings > Select Recipients > Use an Existing List, see screenshot:
2. In the Select Data Source window, choose the workbook which includes the data range that you want to use, and click Open button, see screenshot:
3. In the coming Select Table dialog box, please choose the worksheet with the data range you need, and then click OK button, see screenshot:
4. And the E-mail message main document and your address list have been connected together, now, you can edit the text message and add placeholders that indicate where the unique information will appear in each message.
(1.) To insert their individual greeting name, click Mailings > Insert Merge Field > Name, the personalized name has been inserted into the message, and the field name is surrounded by « ».
(2.) Go on typing your message and insert the Registration Code into the place where you need, see screenshot:
5. After composing the message, you can click Preview Results under the Mailing tab to preview E-mail messages and make changes before you actually complete the merge.
6. After making sure there is no problem, you can send the E-mails to the separate recipients, please click Mailings > Finish& Merge > Send Email Message, see screenshot:
7. Then in the popped out Merge to E-mail dialog box, do the following operations:
(1.) From the To drop down list, please choose Email_Address column;
(2.) You can type the subject into the Subject line text box;
(3.) From the Send records section, select All.
8. And then click OK, the emails are sent to separate recipients with their own registration code at once, after sending the emails, you can go to your Outlook to make sure the emails have been sent successfully.
|
Send personalized emails to multiple recipients with different attachments:
With Kutools for Excel's Send Emails feature, you can quickly send personalized emails to multiple recipients with different attachments from Excel via Outlook as you need. At the same time, you can CC or Bcc the messages to a specific person as well. Download and free trial Kutools for Excel Now!
|
Send personalized mass emails to a list from Excel with VBA code
Excepting the Mail Merge function, the following VBA code also can do you a favor, please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Send personalized mass emails to a list from Excel:
#If VBA7 And Win64 Then
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As LongPtr, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As LongPtr
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
#End If
Sub SendEMail()
'update by Extendoffice 20160506
Dim xEmail As String
Dim xSubj As String
Dim xMsg As String
Dim xURL As String
Dim i As Integer
Dim k As Double
Dim xCell As Range
Dim xRg As Range
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 3 Then
MsgBox " Regional format error, please check", , "Kutools for Excel"
Exit Sub
End If
For i = 1 To xRg.Rows.Count
' Get the email address
xEmail = xRg.Cells(i, 2)
' Message subject
xSubj = "Your Registration Code"
' Compose the message
xMsg = ""
xMsg = xMsg & "Dear " & xRg.Cells(i, 1) & "," & vbCrLf & vbCrLf
xMsg = xMsg & " This is your Registration Code "
xMsg = xMsg & xRg.Cells(i, 3).Text & "." & vbCrLf & vbCrLf
xMsg = xMsg & " please try it, and glad to get your feedback! " & vbCrLf
xMsg = xMsg & "Skyyang"
' Replace spaces with %20 (hex)
xSubj = Application.WorksheetFunction.Substitute(xSubj, " ", "%20")
xMsg = Application.WorksheetFunction.Substitute(xMsg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
xMsg = Application.WorksheetFunction.Substitute(xMsg, vbCrLf, "%0D%0A")
' Create the URL
xURL = "mailto:" & xEmail & "?subject=" & xSubj & "&body=" & xMsg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, xURL, vbNullString, vbNullString, vbNormalFocus
' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next
End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to remind you select the data range that you want to use, see screenshot:
4. And then click OK button, the emails will be sent to separate addresses with their individual registration code one by one, after sending the emails, you can go to your Outlook to make sure the emails have been sent successfully.
Note: In the above code, you can change the subject or body message to your need.
Send personalized mass emails to a list with different attachments by using Kutools for Excel
If you have Kutools for Excel, with its Send Emails feature, you can quickly send personalized emails to multiple recipients with different attachments as you need.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
After installing Kutools for Excel, please do as this:
1. Click Kutools Plus > Send Emails, see screenshot:
2. In the Send Emials dialog box, please select the data range that you want to use, and then specify the recipient addresses, attachments and subject as you need, see screenshot:
3. In the edit box, insert the individual greeting names, please choose Name from the drop down list, and then click Insert Placeholder to insert the names into the message, see screenshot:
4. Then type your message body into the box as you need, see screenshot:
5. After finishing the email body, please choose the send mode as you want, you can send by using Outlook or your specified server, see screesnhot:
Note: If you want to use other server, click Outgoing Server Settings to set the sending mode to your own, see screesnhot:
6. At last, click Send button to send the emails, after completing, a prompt box will pop out to remind you the sending status. see screesnhot:
Click Download and free trial Kutools for Excel Now !
Demo: Send personalized mass emails to a list from Excel via Outlook
Related article:
How to send email to multiple recipients in a list from Excel via Outlook?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
