使用 Outlook 模板和设置变量从 Excel 2007 VBA 发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8469960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Send an email from Excel 2007 VBA using an Outlook Template & Set Variables
提问by Matt Rowles
I have a list of data, let's say client information (Name, Email, Amount Owing etc.), stored in an Excel worksheet. My aim is to click a button in Excel and send each client their information in an Outlook Template.
我有一个数据列表，比如说客户信息（姓名、电子邮件、欠款等），存储在 Excel 工作表中。我的目标是单击 Excel 中的一个按钮，并在 Outlook 模板中向每个客户发送他们的信息。
- create a mail object
- set the mail object to the template file
- setting and then filling in the template with data about the current client - mostly stuck here, not sure how to specify variables in a template and then relate to them in VBA
- save to drafts for later review/send
- 创建邮件对象
- 将邮件对象设置为模板文件
- 设置然后用有关当前客户端的数据填充模板 - 主要停留在这里，不确定如何在模板中指定变量，然后在 VBA 中与它们相关联
- 保存到草稿供以后查看/发送
e.g. Dear << clientname >> = Dear John Smith
例如，亲爱的 << 客户名 >> = 亲爱的约翰·史密斯
My code thus far:
到目前为止我的代码：
Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem
Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItemFromTemplate("C:\egTemplate.oft")
With MyItem
.To = Worksheets("Clients").Range(1, 2)
.Subject = "Monthly bill"
'Refer to and fill in variable items in template
.Save
End With
Set MyItem = Nothing
Set MyOlApp = Nothing
回答by JMax
Here is what you can do :
这是您可以执行的操作：
With MyItem
'Refer to and fill in variable items in template
.Body = Replace(.Body, "<< clientname >>", Worksheets("Clients").Range(1, 2))
End With
or, if your mail is in HTML:
或者，如果您的邮件是 HTML：
With MyItem
'Refer to and fill in variable items in template
.HTMLBody = Replace(.HTMLBody, "<< clientname >>", Worksheets("Clients").Range(1, 2))
End With
Tested successfully on Excel / Outlook 2007
在 Excel / Outlook 2007 上测试成功
回答by JimmyPena
This is a perfect job for mail merge. If you want to do it programmatically, see
这是邮件合并的完美工作。如果您想以编程方式执行此操作，请参阅
Mail Merge in Word+Excel using VBA
Or you could simply do it manually (from Word), inserting merge fields and then selecting your workbook as the data source. You can merge to email and Outlook will send out personalized emails to each recipient's email using the information from each row/record.
或者您可以简单地手动（从 Word）执行此操作，插入合并字段，然后选择您的工作簿作为数据源。您可以合并到电子邮件，Outlook 将使用每行/记录中的信息向每个收件人的电子邮件发送个性化电子邮件。
