如何在Excel VBA电子邮件中添加签名？ [英] How to add a signature in Excel VBA email?

问题描述

我用这个VBA代码发送电子邮件，当用户点击一行中的单元格。

I use this VBA code to send an email when a user clicks a cell in a row.

我想添加一个图像的签名给我电子邮件正文我如何修改我的代码呢？

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 [e-mail]." & 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 = "[e-mail]"
            .To = "mark.o'[e-mail]"
            .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

推荐答案

当您调用 MailItem.Display （这会导致消息显示在屏幕上）或当您访问 MailItem.GetInspector 属性时 - 您不必对返回的Inspector对象执行任何操作，但Outlook将使用签名填充邮件正文。

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.GetInspector property - you do not have to do anything with the returned Inspector object, but Outlook will populate the message body with the signature.

添加签名后，请阅读 HTMLBody 属性，将其与要设置的HTML字符串合并。请注意，您不能简单地连接2个HTML字符串 - 字符串需要合并。例如。如果要将字符串插入HTML体的顶部，请查找< body 子字符串，然后找到下一个出现的> （这将负责处理具有属性的< body> 元素），然后在>之后插入HTML字符串。 。

Once the signature is added, read the HTMLBody property 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 <body substring, then find the next occurrence of > (this takes care of the <body> elements with attributes), then insert your HTML string after that >.

一般来说，签名的名称存储在可通过 IOlkAccountManager 扩展MAPI界面。由于该接口是扩展MAPI，它只能使用C ++或Delphi进行访问。您可以在 OutlookSpy 中查看界面及其数据，如果您点击 IOlkAccountManager 按钮。

On a general note, the name of the signature is stored in the account profile data accessible through the IOlkAccountManager Extended 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 OutlookSpy if you click the IOlkAccountManager button.

Outlook对象模型不会在帐户上公开签名或访问任意属性。

Outlook Object Model does not expose signatures or accessing arbitrary properties on accounts.

如果使用兑换是一个选项，您可以使用其 RDOAccount 对象（可以任何语言访问，包括VBA）。新消息签名名称存储在0x0016001F属性中，回复签名为0x0017001F。您还可以使用 RDOAccount 。 ReplySignature 和 NewSignature 属性。

If using Redemption is an option, you can use its RDOAccount object (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.ReplySignature and NewSignature properties.

这篇关于如何在Excel VBA电子邮件中添加签名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！