来自 Excel VBA 的 Outlook 电子邮件和签名 - .Body 与 .HTMLbody [英] Outlook Email and Signature from Excel VBA - .Body vs .HTMLbody

问题描述

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

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:

• .Body 生成正确的正文(字体和回车)，但签名是纯文本

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

.HMTLBody 产生正确的签名但由于某种原因导致正文，字体转到 Times New Roman 而不是默认的 Calibri，并且回车不起作用无论我是否使用vbNewLine、vbCr 或 vbCrLf

.HMTLBody produces 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

我只是 S.O.L. 吗?我是不是只需要挑一个来处理，或者有没有办法让我的蛋糕也吃掉?

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?

代码:

    .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

更新:

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

.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

推荐答案

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

try to insert your data into the properly html tags:

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

对于空格你必须添加这个标签"
"，例如:

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

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

结果:

你好

如何

是

你?

Edit2

为了插入图像(签名为图像)，您可以使用以下代码:

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

第一步.将此代码复制粘贴到类模块中，并将该类模块命名为MailOptions"

1 step. Copy this code an paste in class module and name that class module like "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

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

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

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:Usersyour_userDocumentsyour_signature.png"

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

mail_sender.PrepareMessageWithEmbeddedImages _
                    FromAddress:="[e-mail]", _
                    ToAddress:="[e-mail]", _
                    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

这篇关于来自 Excel VBA 的 Outlook 电子邮件和签名 - .Body 与 .HTMLbody的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！