Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

doc send personalized emails 1

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 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!

doc send personalized emails 18 1


arrow blue right bubble 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:

doc send personalized emails 2

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:

doc send personalized emails 3

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:

doc send personalized emails 4

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 « ».

doc send personalized emails 5

(2.) Go on typing your message and insert the Registration Code into the place where you need, see screenshot:

doc send personalized emails 6

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:

doc send personalized emails 7

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.

doc send personalized emails 8

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.


arrow blue right bubble 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:

doc send personalized emails 9

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.


arrow blue right bubble 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 60 days. 

After installing Kutools for Excel, please do as this:

1. Click Enterprise > Send Emails, see screenshot:

doc send personalized emails 9

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:

doc send personalized emails 9

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:

doc send personalized emails 9

4. Then type your message body into the box as you need, see screenshot:

doc send personalized emails 9

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:

doc send personalized emails 9

Note:  If you want to use other server, click Outgoing Server Settings to set the sending mode to your own, see screesnhot:

doc send personalized emails 9

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:

doc send personalized emails 9

Click Download and free trial Kutools for Excel Now !


Demo: Send personalized mass emails to a list from Excel via Outlook

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related article:

How to send email to multiple recipients in a list from Excel via Outlook?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shetal Shah · 15 days ago
    Hi All!

    I have a list of emails in one sheet & the email draft in another.
    I need a code to automatically send the email draft from sheet 2 to every 5 members in the list provided in sheet 1.
    Once the first 5 are sent, the code should tag them as "Sent" in a new dedicated column & then wait for 60 seconds before sending out the next set of 5 emails.
    The code should run until the end of the entire list.

    The code can use outlook to send out the mails from the default profile in outlook.

    Please help.

    Let me know in case if you need any more clarifications
  • To post as a guest, your comment is unpublished.
    Raunak · 1 months ago
    I want to send the emails with unique subject line, how can I do that with this VBA?
  • To post as a guest, your comment is unpublished.
    Joshua Nichols · 3 months ago
    I have use the aforementioned VBA code under "Send Personalized Mass Emails To A List From Excel with VBA Code". I wanted to know if I could send an email to certain individuals who need notifying based upon excel telling me to send reminders to those who have upcoming expiring contracts? I cannot get excel to send out the emails and I know its because of the code itself. Can someone please help me resolve this issue?
  • To post as a guest, your comment is unpublished.
    Jeanyves · 4 months ago
    VBA code works well, thanks, but how to add Cc? I tried and failed...
  • To post as a guest, your comment is unpublished.
    chris · 5 months ago
    For some reason this code has it where it sends every other email in the list. Is there a reason why it won't send each one? I directly copied the code that was above and didn't change anything
  • To post as a guest, your comment is unpublished.
    Ilham · 6 months ago
    HIi admin


    apakah metode ini bisa di lakukan pada word 2007 soalnya saya coba lakukan di file pdf nya tdk terkirim .. terimakasih
  • To post as a guest, your comment is unpublished.
    hiba · 1 years ago
    I have a list of email addresses in excel that i need to send emails to The subject and body are in cells besides the email address and i have the script below but i need to send 100 emails per 1 hour how i can please your support

    Sub SendEm()
    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("B" & i).Value
    .To = Range("A" & i).Value

    .Body = Range("C" & i).Value
    .attachments.Add (Sheets("Sheet1").Range("H" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("I" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("J" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("K" & i).Text)
    .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
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, hiba,
      Our Kutools for Excel's Send Emails can help you to solve your problem. Please set the conditions as following screenshot shown:
      With this feature, you can also apply the CC, BCC feature, and you can insert multiple attachments as well.
  • To post as a guest, your comment is unpublished.
    Terrence · 1 years ago
    Thanks!!

    But I have trying to do the test and is nothing sending out to the emai. Also no error message.. May I know what happened?
  • To post as a guest, your comment is unpublished.
    steven · 1 years ago
    what if I have data for alot of customers and some customers have more then 1 data... and I dont want to send seperate mails to the customers, but maybe send it in a tabular format in the email. How can I send just 1 email to a customer.
    • To post as a guest, your comment is unpublished.
      Sally · 1 years ago
      I want to know how to make it. Can you tell me?
    • To post as a guest, your comment is unpublished.
      Pulkit · 1 years ago
      Hi Steven, did you find a way to get this done. We can help you with a simple online web portal
  • To post as a guest, your comment is unpublished.
    surajit sen · 1 years ago
    how to cc people in this?
  • To post as a guest, your comment is unpublished.
    Arvind Jadhav · 1 years ago
    hi Skyyang,
    i need to attach different pdf file for each mail. can you help please??
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Arvind,
      The Kutools can help you to add multiple different attachments when sending emails.
      First you should insert the path of the attachments into the cells, and then apply the Kutools, please view the following screenshot:
      Hope it can help you, thank you!
      • To post as a guest, your comment is unpublished.
        Arvind Jadhav · 1 years ago
        hi Skyyang,
        i used your below code to send mail and is working perfect, is there anything you can help me to modify in code for sending pdf attachments along.
        i need to send more than 100 mails so is quit difficult to attach file each time. is Kutools work as per given path and send mail automatically?
        • To post as a guest, your comment is unpublished.
          Arvind Jadhav · 1 years ago
          hey,
          can you help me with attachment.add function? with your above VBA code i need to sent attachment.
          i cant use Kutools :(
  • To post as a guest, your comment is unpublished.
    Arvind · 1 years ago
    hi Friend,
    this is fantastic, need one help I need to attach different pdf file to different email each time.
    can anyone help me please?
  • To post as a guest, your comment is unpublished.
    Nagu · 1 years ago
    Madness post this is,this one is fake and don't believe this content
  • To post as a guest, your comment is unpublished.
    jimmi_10 · 2 years ago
    Hi Skyyang,

    I've been trying and trying to get my email signature to come up but just can't seem to get it right! Do you have a bit of code I can add to make this happen?
  • To post as a guest, your comment is unpublished.
    Aziz · 2 years ago
    Great way

    I just need to ask you about the possibilityof changing the subject each time based on email??
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Aziz,
      When you apply the Kutools for Excel, it can help you to insert the subjects based on the cell values, you just need to select the subject column header in the dialog box as following screenshot shown:

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Marlyon Claudius · 2 years ago
    how to add cc in the above code
  • To post as a guest, your comment is unpublished.
    Rick · 2 years ago
    is it possible to add code so that it also pulls in my normal email signature?

    also, when selecting my range to email, I have my excel spreadsheet filtered, but when I run the macro, it still pulls in the hidden rows as well.
  • To post as a guest, your comment is unpublished.
    Wahid Prince · 2 years ago
    Can you modify the code to attach multiple files in email? It would be a great help. Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Luucas · 2 years ago
    This code worked very well, much thanks of that. But I just have a small problems, I am trying to change the code to pre-determine the cells I want to take the emails and send it because the way I created my excel file. I would like to switch the selection box for a determined array of cells. Example: fix all my data will be between A2:C6 so when I run the macro I do not need to select it again. Very Much Thank You for everything you explained above