如何在 Excel VBA 电子邮件中添加签名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25571411/
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
How to add a signature in Excel VBA email?
提问by james tanner
I use this VBA code to send an email when a user clicks a cell in a row.
当用户单击一行中的单元格时，我使用此 VBA 代码发送电子邮件。
I want to add a signature, with an image, to my email body. How could I amend my code to put this in?
我想在我的电子邮件正文中添加带有图像的签名。我怎么能修改我的代码来把它放进去？
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = Range("BL1").Column Then
If Target.Row > 7 And Target.Value = "Take Action" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "<p style='font-family:calibri;font-size:16'>" & "Dear Sirs," & "<br><br>" & vbNewLine & vbNewLine & _
"F.A.O: " & "<b>" & Range("B" & ActiveCell.Row) & "</b>" & "," & vbNewLine & vbNewLine & _
"<br>" & "This is an urgent update on the status of your account." & "<br>" & vbNewLine & _
"Our records show that your insurance is due to expire on: " & "<b>" & Format(Range("BE" & ActiveCell.Row), "dd" & " Mmmm " & "yyyy") & "." & "</b>" & " To ensure that you remain active on our systems as an approved Hewden Stuart Ltd Supplier, it is important that you provide us with the details of your renewed insurance policy. Please can you provide us with these details for the following insurance as soon as possible, in order to remain active on our systems:" & vbNewLine & vbNewLine & _
"<br><br>" & "Insurance: " & "<b>" & "{Insurance Type Goes Here}" & "</b>" & "<br>" & vbNewLine & _
"Due for Period: " & "<b>" & Format(Range("BE" & ActiveCell.Row), "dd" & " Mmmm " & "yyyy") & "</b>" & " - " & "<b>" & Format(Range("BE" & ActiveCell.Row) + 365, "dd" & " Mmmm " & "yyyy") & "</b>" & vbNewLine & vbNewLine & _
"<br><br>" & "Note:" & "<br>" & vbNewLine & _
"Please ensure that the above information is provided by your insurance broker, or your insurer, in the form of a standard letter or certificate. If your insurance is in the name of a parent company, please provide a breakdown of the companies covered from your insurer. In order to provide us with the above information, please login to your Control Panel with your unique Username and Password and attach your documents. Regrettably, failure to provide us with the information requested will result in suspension of your account. If you have any queries, please email us at [email protected]." & vbNewLine & vbNewLine & _
"<br><br>" & "Your Reference:" & "<br><br>" & vbNewLine & vbNewLine & _
"<b>" & Range("AB" & ActiveCell.Row) & "</b>" & vbNewLine & _
"<p style='font-family:calibri;font-size:13'>" & "Please quote your unique Supplier Reference number when providing us with any insurance documents and in the even that you should have any enquiries." & "</p>" & vbNewLine & vbNewLine & _
"<p style='font-family:calibri;font-size:16'>" & "<br>" & "Kind Regards," & "<br><br>" & vbNewLine & vbNewLine & _
"<b>" & "Hewden Supply Chain Department" & "</b>" & "</P>"
With OutMail
.SentOnBehalfOfName = "[email protected]"
.To = "mark.o'[email protected]"
.CC = ""
.BCC = ""
.Subject = "Important! - Insurance Alert!"
.HTMLbody = strbody
.Attachments.Add ("P:\cover.jpg")
.Send 'or use .Display
End With
End If
End If
End Sub
回答by Dmitry Streblechenko
Outlook adds the signature to the new unmodified messages (you should not modify the body prior to that) when you call MailItem.Display(which causes the message to be displayed on the screen) or when you access the MailItem.GetInspectorproperty - you do not have to do anything with the returned Inspector object, but Outlook will populate the message body with the signature.
当您调用MailItem.Display（这会导致消息显示在屏幕上）或访问该MailItem.GetInspector属性时，Outlook 会将签名添加到新的未修改消息中（在此之前您不应修改正文）- 您无需执行任何操作返回的 Inspector 对象，但 Outlook 将使用签名填充邮件正文。
Once the signature is added, read the HTMLBodyproperty and merge it with the HTML string that you are trying to set. Note that you cannot simply concatenate 2 HTML strings - the strings need to be merged. E.g. if you want to insert your string at the top of the HTML body, look for the <bodysubstring, then find the next occurrence of >(this takes care of the <body>elements with attributes), then insert your HTML string after that >. Embedded image attachments and styles must be handled separately.
添加签名后，读取该HTMLBody属性并将其与您尝试设置的 HTML 字符串合并。请注意，您不能简单地连接 2 个 HTML 字符串 - 需要合并这些字符串。例如，如果您想在 HTML 正文的顶部插入您的字符串，请查找<body子字符串，然后找到下一个出现的>(这会处理<body>具有属性的元素)，然后在之后插入您的 HTML 字符串>。嵌入的图像附件和样式必须单独处理。
On a general note, the name of the signature is stored in the account profile data accessible through the IOlkAccountManagerExtended MAPI interface. Since that interface is Extended MAPI, it can only be accessed using C++ or Delphi. You can see the interface and its data in OutlookSpyif you click the IOlkAccountManagerbutton.
一般而言，签名的名称存储在可通过IOlkAccountManager扩展 MAPI 接口访问的帐户配置文件数据中。由于该接口是扩展 MAPI，因此只能使用 C++ 或 Delphi 进行访问。如果单击该按钮，您可以在OutlookSpy 中看到界面及其数据IOlkAccountManager。
Outlook Object Model does not expose signatures or accessing arbitrary properties on accounts.
Outlook 对象模型不会公开签名或访问帐户的任意属性。
If using Redemptionis an option, you can use its RDOAccountobject (accessible in any language, including VBA). New message signature name is stored in the 0x0016001F property, reply signature is in 0x0017001F. You can also use the RDOAccount.ReplySignatureand NewSignatureproperties.
All Outlook signatures are exposed through the RDOSession.Signaturescollection.
A signature can be applied to a message using RDOSignature.ApplyTo- it will take care of correctly merging the data and bringing over embedded image attachments and styles.
如果使用Redemption是一个选项，您可以使用它的RDOAccount对象（可以用任何语言访问，包括 VBA）。新消息签名名称存储在 0x0016001F 属性中，回复签名存储在 0x0017001F 中。您还可以使用RDOAccount。ReplySignature和NewSignature属性。
所有 Outlook 签名都通过RDOSession.Signatures集合公开。
可以使用RDOSignature.ApplyTo将签名应用于消息- 它将负责正确合并数据并引入嵌入的图像附件和样式。
EDIT: as of summer 2017, only MailItem.Displayinserts the signature in Outlook 2016. MailItem.GetInspectordoes not do that anymore.
编辑：截至 2017 年夏季，仅MailItem.Display在 Outlook 2016MailItem.GetInspector中插入签名。不再这样做。
回答by Kevin Francis
In Outlook 2007 Tools – Options – Mail Format tab Hold down cntrl key and click on Signatures
在 Outlook 2007 工具 - 选项 - 邮件格式选项卡中按住 cntrl 键并单击签名
The folder where signatures are kept will open and the path and file name will be displayed. This will be the path and file name to be used to call the GetSignature function below.
保存签名的文件夹将打开并显示路径和文件名。这将是用于调用下面的 GetSignature 函数的路径和文件名。
Function GetSignature(fPath As String) As String
Dim fso As Object
Dim TSet As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set TSet = fso.GetFile(fPath).OpenAsTextStream(1, -2)
GetSignature= TSet.readall
TSet.Close
End Function
In your send email code, declare a string variable to hold the signature text returned from the GetSignature function. Once set, then the string then needs to be appended to the end of the body of the email.
在发送电子邮件代码中，声明一个字符串变量来保存从 GetSignature 函数返回的签名文本。一旦设置，然后需要将字符串附加到电子邮件正文的末尾。
Dim StrSignature As String
StrSignature = GetSignature(sPath)
.HTMLbody = strbody & vbNewLine & vbNewLine & StrSignature
回答by PIerreDB
Had the same problem, especially when using HTMLbody. Instead of using this:
有同样的问题，尤其是在使用 HTMLbody 时。而不是使用这个：
With OutMail
.SentOnBehalfOfName = "[email protected]"
.To = "mark.o'[email protected]"
.CC = ""
.BCC = ""
.Subject = "Important! - Insurance Alert!"
.HTMLbody = strbody
.Attachments.Add ("P:\cover.jpg")
.Send 'or use .Display
End With*
You should do this:
你应该做这个：
With OutMail
.SentOnBehalfOfName = "[email protected]"
.To = "mark.o'[email protected]"
.CC = ""
.BCC = ""
.Subject = "Important! - Insurance Alert!"
''''' part that is missing
. Display
End with
With OutMail
'''''
.HTMLbody = strbody & vbNewLine & .HTMLBody ' add the signature without losing the HTML-formatting of the signature
.Attachments.Add ("P:\cover.jpg")
.Send 'or use .Display
End With
