从Excel 2007 VBA发送电子邮件,使用Outlook模板&设置变量 [英] Send an email from Excel 2007 VBA using an Outlook Template & Set Variables
问题描述
我有一个数据列表,我们来说说存储在Excel工作表中的客户信息(名称,电子邮件,金额等)。我的目标是单击Excel中的按钮,并将其信息发送到Outlook模板。
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.
- 创建邮件对象
- 将邮件对象设置为模板文件
- 设置,然后使用当前客户端的数据填充模板 - 大多数在此处停留,不知道如何指定变量在一个模板中,然后在VBA中与他们联系
- 保存到草稿以供稍后查看/发送
- 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
例如。亲爱的<< clientname >> =亲爱的约翰·史密斯
eg. Dear << clientname >> = Dear John Smith
我的代码到目前为止:
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
我需要知道这是否可能,我可以做没有插件,如果是,有谁知道一个很好的链接/教程我可以跟着吗
I need to know if this is even possible, can I do it without plugins and if so, does anyone know a good link/tutorial I can follow?
提前感谢善意
更新添加了我的代码,
推荐答案
这是你可以做的:
With MyItem
'Refer to and fill in variable items in template
.Body = Replace(.Body, "<< clientname >>", Worksheets("Clients").Range(1, 2))
End With
或如果您的邮件是HTML,请执行以下操作:
or, if your mail is in HTML:
With MyItem
'Refer to and fill in variable items in template
.HTMLBody = Replace(.HTMLBody, "<< clientname >>", Worksheets("Clients").Range(1, 2))
End With
在Excel / Outlook 2007上成功测试
Tested successfully on Excel / Outlook 2007
这篇关于从Excel 2007 VBA发送电子邮件,使用Outlook模板&设置变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!