来自 Excel VBA 的 Outlook 电子邮件和签名 - .Body 与 .HTMLbody

Outlook Email and Signature from Excel VBA - .Body vs .HTMLbody

提问by Chris L

I have a button on my worksheet to send an email (plus more, but not important). I want my default signature with its HTML formatting but neither option is producing the results I want:

我的工作表上有一个按钮可以发送电子邮件（还有更多，但并不重要）。我想要我的默认签名及其 HTML 格式，但两个选项都没有产生我想要的结果：

• .Bodyproduces the correct body (fonts and carriage returns) but the signature is plain text

• .HMTLBodyproduces the correct signature but the body for some reason, the font goes to Times New Roman instead of the default Calibri, and the carriage returns don't work whether I use vbNewLine, vbCr, or vbCrLf

• .Body产生正确的正文（字体和回车），但签名是纯文本

• .HMTLBody产生正确的签名，但身体由于某种原因，字体变为Times New Roman字体，而不是默认的宋体，以及回车都不工作，我是否使用vbNewLine，vbCr或vbCrLf

Am I just S.O.L.? Do I need to just pick one and deal with it, or is there a way for me to have my cake and eat it too?

我只是SOL吗？我需要选择一个并处理它，还是有办法让我也吃蛋糕？

Code:

代码：

    .Display         ' need to display email first for signature to work
    .Subject = Title
    .To = ActiveSheet.Range("E10").Value ' <-- Put email of the recipient here
    .CC = "" ' <-- Put email of 'copy to' recipient here
    .HTMLBody = "Thank you for the opportunity to bid on " & ActiveSheet.Range("B9").Value & ". " & _
        " Please read our attached proposal in its entirety to be sure of all inclusions, exclusions, and products proposed.  Give us a call with any questions or concerns." & _
        vbCrLf & vbCrLf & _
        "Thank you," & _
        .HTMLBody      ' Adds default signature
    .Attachments.Add PdfFile

Update:

更新：

Final working code thanks to the help of both answers below:

感谢以下两个答案的帮助，最终的工作代码：

.Display         ' We need to display email first for signature to be added
.Subject = Title
.To = ActiveSheet.Range("E10").Value
.CC = ""
.HTMLBody = "<font face=""calibri"" style=""font-size:11pt;"">Thank you for the opportunity to bid on " & ActiveSheet.Range("B9").Value & ". " & " Please read our attached proposal in its entirety to be sure of all inclusions, exclusions, and products proposed.  Give us a call with any questions or concerns." & _
    "<br><br>" & _
    "Thank you," & _
    .HTMLBody & "</font>"   ' Adds default signature
.Attachments.Add PdfFile

回答by Dmitry Streblechenko

When you set the HTMLBody property, make sure you merge the existing HTMLBody(with the signature) and your new data - you cannot just concatenate two HTML strings and expects a valid HTML. find the position of the "<body"string, find the position of the next ">" (to take care of the body elements with attributes), insert your data after that ">".

当您设置 HTMLBody 属性时，请确保合并现有HTMLBody（带有签名）和新数据 - 您不能只是连接两个 HTML 字符串并期望有效的 HTML。找到"<body"字符串的位置，找到下一个“>”的位置（处理带有属性的body元素），在“>”之后插入你的数据。

回答by Dani Aya

try to insert your data into the properly html tags:

尝试将您的数据插入到正确的 html 标签中：

.HTMLBody = "<font face=""verdana"" color=""black"">This is some text!</font>"

for spaces you must to add this tag "<br>", For example:

对于空格，您必须添加此标签"<br>"，例如：

.HTMLBody = "<font face=""calibri"" color=""black""> hello <br>"
.HTMLBody = .HTMLBody & " how <br>" & " are <br>" & " you?</font>"

result in:

导致：

hello

你好

---

---

how

如何

---

---

are

是

---

---

you?

你？

Edit2

编辑2

In order to insert images (signature as images) you could use the following code:

为了插入图像（签名为图像），您可以使用以下代码：

1 step.Copy this code an paste in class module and name that class module like "MailOptions"

1 步。将此代码复制粘贴到类模块中，并将该类模块命名为“MailOptions”

Private Message As CDO.Message
Private Attachment, Expression, Matches, FilenameMatch, i

Public Sub PrepareMessageWithEmbeddedImages(ByVal FromAddress, ByVal ToAddress, ByVal Subject, ByVal HtmlContent)

    Set Expression = CreateObject("VBScript.RegExp")
    Expression.Pattern = "\<EMBEDDEDIMAGE\:(.+?)\>"
    Expression.IgnoreCase = True
    Expression.Global = False 'one match at a time

    Set Message = New CDO.Message
    Message.From = FromAddress
    Message.To = ToAddress
    Message.Subject = Subject

    'Find matches in email body, incrementally increasing the auto-assigned attachment identifiers
    i = 1
    While Expression.Test(HtmlContent)
        FilenameMatch = Expression.Execute(HtmlContent).Item(0).SubMatches(0)
        Set Attachment = Message.AddAttachment(FilenameMatch)
        Attachment.Fields.Item("urn:schemas:mailheader:Content-ID") = "<attachedimage" & i & ">" ' set an ID we can refer to in HTML
        Attachment.Fields.Item("urn:schemas:mailheader:Content-Disposition") = "inline" ' "hide" the attachment
        Attachment.Fields.Update
        HtmlContent = Expression.Replace(HtmlContent, "cid:attachedimage" & i) ' update the HTML to refer to the actual attachment
        i = i + 1
    Wend

    Message.HTMLBody = HtmlContent
End Sub

Public Sub SendMessageBySMTP(ByVal SmtpServer, ByVal SmtpUsername, ByVal SmtpPassword, ByVal UseSSL)
    Dim Configuration
    Set Configuration = CreateObject("CDO.Configuration")
    Configuration.Load -1 ' CDO Source Defaults
    Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SmtpServer
    'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SmtpPort
    Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 30

    If SmtpUsername <> "" Then
        Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = SmtpUsername
        Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = SmtpPassword
    End If
    Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = UseSSL
    Configuration.Fields.Update
    Set Message.Configuration = Configuration
    Message.Send
End Sub

Step 2.In an standar module you will elaborate your .html content and instantiate a object from the class:

第 2 步。在标准模块中，您将详细说明 .html 内容并从类中实例化一个对象：

public sub send_mail()

Dim signature As String
dim mail_sender as new MailOptions 'here you are instantiating an object from the class module created previously
dim content as string

signature = "C:\Users\your_user\Documents\your_signature.png"

content = "<font face=""verdana"" color=""black"">This is some text!</font>"
content = content & "<img src=""<EMBEDDEDIMAGE:" & signature & " >"" />"

mail_sender.PrepareMessageWithEmbeddedImages _
                    FromAddress:="[email protected]", _
                    ToAddress:="[email protected]", _
                    Subject:="your_subject", _
                    HtmlContent:=content

'your_Smtp_Server, for example: RelayServer.Contoso.com
correos.SendMessageBySMTP "your_Smtp_Server", "your_network_user_account", "your_network_user_account_password", False

end sub