跳到主要內容

如何通過Outlook從Excel向列表發送個性化的群發電子郵件?

例如,我在工作表中有以下數據范圍,其中包含“名稱”,“電子郵件地址”,“註冊代碼”列,現在,我想向A列中單獨的電子郵件地址發送帶有個性化問候語和自己的註冊代碼的消息。要解決此問題,以下方法可以為您提供幫助。

doc發送個性化電子郵件1

使用郵件合併功能從Excel將個性化的大量電子郵件發送到列表

使用VBA代碼從Excel發送個性化的大量電子郵件到列表

使用Kutools for Excel將個性化的大量電子郵件發送到帶有不同附件的列表


箭頭藍色右氣泡 使用郵件合併功能從Excel將個性化的大量電子郵件發送到列表

用話語 郵件合併 功能,您可以快速輕鬆地完成此工作,請按以下步驟操作:

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以確保電子郵件已成功發送。


向具有不同附件的多個收件人發送個性化電子郵件:

這款獨特的敏感免洗唇膜採用 Moisture WrapTM 技術和 Berry Mix ComplexTM 成分, Excel的Kutools's 發電子郵件 功能,您可以根據需要通過Outlook通過Excel將個性化電子郵件快速發送給具有不同附件的多個收件人。 同時,您也可以抄送或密送給特定人員的郵件。       立即下載並免費試用Excel的Kutools!

doc發送個性化電子郵件18 1


箭頭藍色右氣泡 使用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將個性化的大量電子郵件發送到帶有不同附件的列表

如果你有 Excel的Kutools,其 發電子郵件 功能,您可以根據需要快速將個性化電子郵件發送給具有不同附件的多個收件人。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用. 

安裝後 Excel的Kutools,請這樣做:

1。 點擊 Kutools 加 > 發電子郵件,請參見屏幕截圖:

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將個性化的大量電子郵件發送到列表

Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

相關文章:

如何通過Outlook從Excel向列表中的多個收件人發送電子郵件?

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (47)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, I have used this from your Kutools for Excel, and it works a dream. However, the Outlook email signature is not working, despite it being checked off. I have a default email signature set up in Outlook to go with the default mail account. But no matter how many times I try, I can't get Kutools to insert the signature before sending the email. Should I be doing something different in Outlook with the email signature?
This comment was minimized by the moderator on the site
Hello, Rochelley
Did you select the signature from the Outlook's Signatures and Stationery dialog box, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-signature-1.png
Please check it first, thank you!

If there still problem, please comment here.
This comment was minimized by the moderator on the site
I've used KuTools Send Emails for personalized attachments using an Excel list and it works well. Is it possible to use this functionality to send personalized links to shared files instead of attachments? I've tried and haven't been able to get this to work.
This comment was minimized by the moderator on the site
Hello, Blanchard

With our Send Emails feature, you can send the links of the shared files successfully.
You just need to change the attachment path to the link of the shared file, see below screenshot:

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-bulk-send-emails.png

Please try, hope it can help you!

If this doesn't work, you can upload your error image here, so that we can check the problem.
This comment was minimized by the moderator on the site
I've used KuTools Send Emails for personalized attachments using an Excel list and it works well. Is it possible to use this functionality to send personalized links to shared files instead of attachments? I've tried and haven't been able to get this to work.
This comment was minimized by the moderator on the site
In "Send personalized mass emails to a list from Excel with VBA code", it cannot work.For starters, the instructions wrt F11 does nothing, and so useless blather.Next, the #If...#End If cannot exist anywhere, as it (1) is treated as a comment and (2) the compiler crashes (cannot compile).So one tries it after the End Sub because the compiler says in effect comments to be after End Sub.Naturally, the "ShellExecute" causes a crash because it is not declared: remember, the #If...#End If had to be removed.
It would be nice to have WORKING code.
This comment was minimized by the moderator on the site
Thanks for the "How To Send Personalized Mass Emails To A List From Excel Via Outlook?", it is very useful.Question: I have 2 email addresses on my outlook. I want to use the 2nd one to send the personalized mass emails. How should I do that? I cannot find the way of changing the "From" when I finish&Merge. Can you help?
This comment was minimized by the moderator on the site
Hello, Pilar,The normal Mail Merge function only can help to send the emials from the default account, if you want to send eamils from other account you defined, you can use our Send Emails feature of Kutools for Excel. You can download and installed the Kutools for Excel, free trial 30 days.Please try, hope it can help you!
This comment was minimized by the moderator on the site
<p>Could you please help me to include table structure in below code ?</p><p>Gopalakrishnan</p>
This comment was minimized by the moderator on the site
I used the kutools send mail option after sending mail theres no attachment
This comment was minimized by the moderator on the site
Hi, marian,
Do you type the full path of the attachments into the cells? Please check it. Thank you!
This comment was minimized by the moderator on the site
No I didn't type the path rather I used the insert link button to add the attachment
This comment was minimized by the moderator on the site
Hi, I have to send to one email address(BOT) multiple request for *documents.
* Subject line needs to be the document reference number as demonstrated in below table.
Email ID Subject
# policy 111
# policy 222
# policy 333
# policy 444
# policy 555
# policy 666
# policy 777
# policy 888
# policy 999
# policy 1110

please help me simplyfy my task. I use MS outlook 2013 and 2016
This comment was minimized by the moderator on the site
Email ID Subject
# policy 111
# policy 222
# policy 333
# policy 444
# policy 555
# policy 666
# policy 777
# policy 888
# policy 999
# policy 1110
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations