提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何通过Outlook从Excel发送个性化群发电子邮件到列表?

例如,我在一个包含名称,电子邮件地址,注册码列的工作表中有以下数据范围,现在,我希望将带有个性化问候语和自己的注册码的邮件发送到A列中分隔的电子邮件地址。要解决此问题,以下方法可能会对您有所帮助。

doc发送个性化电子邮件1

使用邮件合并功能从Excel发送个性化群发电子邮件到列表

使用VBA代码从Excel发送个性化群发电子邮件到列表

使用Kutools for Excel将个性化群发电子邮件发送到具有不同附件的列表


将个性化电子邮件发送给具有不同附件的多个收件人:

您订购的 Kutools for Excel's 发电子邮件 功能,您可以根据需要通过Outlook快速向具有不同附件的多个收件人发送个性化电子邮件。 同时,您也可以将消息CC或Bcc发送给特定的人。 立即下载并免费试用Kutools for Excel!

doc发送个性化电子邮件18 1


箭头蓝色右泡 使用邮件合并功能从Excel发送个性化群发电子邮件到列表


与Word的 邮件合并 功能,您可以快速轻松地完成这项工作,请按照以下步骤做:

1. 启动一个新的空白Word文档,然后单击 邮件 > 选择收件人 > 使用现有列表,看截图:

doc发送个性化电子邮件2

2。 在 选择数据源 窗口中,选择包含要使用的数据范围的工作簿,然后单击 开放 按钮,看截图:

doc发送个性化电子邮件3

3。 在即将到来 选择表格 对话框中,请选择您需要的数据范围的工作表,然后单击 OK 按钮,看截图:

doc发送个性化电子邮件4

4。 并且电子邮件主文档和您的地址列表已经连接在一起,现在,您可以编辑短信并添加指示独特信息将出现在每封邮件中的位置的占位符。

(1。)要插入其个人问候语名称,请单击 邮件 > 插入合并域 > 名字,个性化名称已被插入到消息中,并且字段名称被包围 «».

doc发送个性化电子邮件5

(2。)继续输入您的信息并插入 注册码 进入你需要的地方,看截图:

doc发送个性化电子邮件6

5。 撰写完消息后,您可以点击 预览结果 在下面 邮件 选项卡以预览电子邮件消息并在实际完成合并之前进行更改。

6。 确认没有问题后,您可以将电子邮件发送给单独的收件人,请点击 邮件 > 完成并合并 > 发送邮件信息,看截图:

doc发送个性化电子邮件7

7。 然后在弹出 合并到电子邮件 对话框,请执行以下操作:

(1。)从 请选择下拉列表 电子邮件地址 柱;

(2。)您可以在主题中输入主题 标题 行文本框;

(3。)从 发送记录 部分,选择 全部.

doc发送个性化电子邮件8

8。 然后点击 OK,电子邮件将立即发送给具有自己的注册码的单独收件人,发送电子邮件后,您可以前往Outlook以确保电子邮件已成功发送。


箭头蓝色右泡 使用VBA代码从Excel发送个性化群发电子邮件到列表

除了邮件合并功能,下面的VBA代码也可以帮你一个忙,请这样做:

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:将个性化群发电子邮件发送至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. 然后按 F5 键运行此代码,并会弹出提示框提醒您选择要使用的数据范围,请参阅屏幕截图:

doc发送个性化电子邮件9

4。 然后点击 OK 按钮后,电子邮件将逐个发送到单独的注册代码地址,在发送电子邮件后,您可以转到Outlook以确保电子邮件已成功发送。

注意:在上面的代码中,您可以根据需要更改主题或正文消息。


箭头蓝色右泡 使用Kutools for Excel将个性化群发电子邮件发送到具有不同附件的列表

如果你有 Kutools for Excel,其 发电子邮件 功能,您可以根据需要快速将个性化电子邮件发送给具有不同附件的多个收件人。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel,请这样做:

1。 点击 企业服务 > 发电子邮件,看截图:

doc发送个性化电子邮件9

2。 在 发送Emials 对话框中,请选择要使用的数据范围,然后根据需要指定收件人地址,附件和主题,请参阅截图:

doc发送个性化电子邮件9

3。 在编辑框中插入个人问候语名称,请选择 名字 从下拉列表中选择,然后单击 插入占位符 要将这些名称插入到消息中,请参阅截图:

doc发送个性化电子邮件9

4。 然后根据需要在邮箱中输入邮件正文,请参阅截图:

doc发送个性化电子邮件9

5。 完成电子邮件正文后,请根据需要选择发送模式,可以使用Outlook或指定的服务器进行发送,请参阅screesnhot:

doc发送个性化电子邮件9

注意:如果您想使用其他服务器,请单击 发送服务器设置 将发送模式设置为您自己的模式,请参阅screesnhot:

doc发送个性化电子邮件9

6。 最后点击 发送 按钮发送电子邮件,完成后会弹出提示框提醒您发送状态。 看screesnhot:

doc发送个性化电子邮件9

点击下载并立即免费试用Kutools for Excel!


演示:通过Outlook从Excel发送个性化群发电子邮件到列表

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

如何通过Outlook将电子邮件发送到Excel中的多个收件人?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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.
    Joshua Nichols · 24 days 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 · 1 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 · 2 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 · 3 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 · 11 months 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 · 10 months 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 · 1 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 · 1 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 · 1 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