How to add a signature in Excel VBA email?

2019-08-02 16:48发布

站内问答 / 移动开发
1968 3 5

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 SupplierAudits@Hewden.co.uk." & 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 = "newsuppliers@hewden.co.uk"
            .To = "mark.o'brien@hewden.co.uk"
            .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

3条回答
Fickle 薄情
2楼-- · 2019-08-02 17:09

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.

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 >. Embedded image attachments and styles must be handled separately.

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 Object Model does not expose signatures or accessing arbitrary properties on accounts.

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.
All Outlook signatures are exposed through the RDOSession.Signatures collection.
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.

EDIT: as of summer 2017, only MailItem.Display inserts the signature in Outlook 2016. MailItem.GetInspector does not do that anymore.

查看更多
0人赞 添加讨论(0) 举报
forever°为你锁心
3楼-- · 2019-08-02 17:14

In Outlook 2007 Tools – Options – Mail Format tab Hold down cntrl key and click on Signatures

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.

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.

Dim StrSignature As String
StrSignature = GetSignature(sPath)

.HTMLbody = strbody & vbNewLine & vbNewLine & StrSignature
查看更多
0人赞 添加讨论(0) 举报
太酷不给撩
4楼-- · 2019-08-02 17:33

Had the same problem, especially when using HTMLbody. Instead of using this:

    With OutMail
    .SentOnBehalfOfName = "newsuppliers@hewden.co.uk"
    .To = "mark.o'brien@hewden.co.uk"
    .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 = "newsuppliers@hewden.co.uk"
    .To = "mark.o'brien@hewden.co.uk"
    .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
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)