0

I've written an Excel add-in to send emails using Outlook. I've come up with a way to add the user's signature. The fly in the ointment though is that they change the names of their signatures.

How can I get the user's default signature from Outlook?

    Dim salutation As String, ourRef As String, amount As String, customerName As String, yourRef As String
    Dim totalAmount As Double

    ' salutation
    salutation = IIf(Time < 0.5, "Good morning", "Good afternoon")

    ' opening text
    totalAmount = 0
    For i = 0 To UBound(Table, 1)
        totalAmount = totalAmount + Table(i, 3)
    Next i

    body = "<!DOCTYPE html><html><body>"
    body = body & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
    If bulk = True Then
        body = body & salutation & "<br /><br />We have sent you a bulk payment totaling " _
        & Format(totalAmount, "£##,##0.00") & ", to be credited to the following accounts: <br /><br />"
    Else
        body = body & salutation & "<br /><br />We have sent you the following payments: <br /><br />"
    End If

    ' table header
    body = body & "<style type='text/css'>.tftable {font-size:12px;color:#333333;width:100%;" _
        & "border-width: 1px;border-color: #9dcc7a;border-collapse: collapse;}.tftable th {font-size:12px;" _
        & "background-color:#A99D36;border-width: 1px;padding: 8px;border-style: solid;border-color: " _
        & "#9dcc7a;text-align:left;}.tftable tr {background-color:#F0EDCF;}.tftable td {font-size:12px;" _
        & "border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;}"
    body = body & "</style><table class='tftable' border='1'><tr><th>Our ref</th><th>Amount</th>" _
        & "<th>Client name</th><th>Your ref</th></tr>"

    ' table body
    For i = LBound(Table, 1) To UBound(Table, 1)

        ourRef = Table(i, 1)
        amount = Table(i, 3)
        customerName = Table(i, 2)
        yourRef = Table(i, 4)

        body = body & "<tr>"
        body = body & "<td>" & ourRef & "</td>"
        body = body & "<td>" & Format(amount, "£##,##0.00") & "</td>"
        body = body & "<td>" & customerName & "</td>"
        body = body & "<td>" & yourRef & "</td>"
        body = body & "</tr>"

    Next i

    body = body & "</tbody></table>"

    ' signature
    enviro = CStr(Environ("appdata"))
    Debug.Print enviro
    Set objfso = CreateObject("Scripting.FileSystemObject")
    strSigFilePath = enviro & "\Microsoft\Signatures\"
    Debug.Print strSigFilePath
    Set objSignatureFile = objfso.opentextfile(strSigFilePath & "Standard.htm")
    strbuffer = objSignatureFile.ReadAll
    body = body & "<br/><br/>" & strbuffer
    objSignatureFile.Close

End Function
Community
  • 1
  • 1
Stevie D
  • 1
  • 2
  • Please [edit] your question and add your relevant code parts or a [mcve]. – Pᴇʜ Mar 08 '19 at 14:56
  • Instead of having the code create the signature, did you try just adding `.Display` before the `.Send` to mail object when it is created? That will add the users signature to the email before sending. – Zack E Mar 08 '19 at 15:38
  • No doesn't work because the signature is not just text need to pull an image through as well. – Stevie D Mar 08 '19 at 16:25
  • @Stevie D Outlook Object Model does not expose signatures at all. 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. If using Redemption is an option. `Redemption` also exposes `RDOSignature`. This is just to share knowledge, my scope is restricted to `Excel VBA ` only. – skkakkar Mar 08 '19 at 16:43
  • Thanks skkakkar looks like this is going to be a bit tricky then. – Stevie D Mar 08 '19 at 18:07
  • a link to the answer quoted by @skkakkar: https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook/32391722#32391722 – Dmitry Streblechenko Mar 09 '19 at 17:47
  • @Dmitry Streblechenko I just keep extracts of important points in my notes of reputed experts like you, hence was not able to provide your link. Thanks for pointing to the link. I never intended to answer the question, it were in comments only and I also restricted and clarified my scope. I hope you will pardon me for any wrong doing inadvertently. – skkakkar Mar 09 '19 at 18:24
  • @skkakka - no problem at all: I just wanted the OP to see the whole discussion. – Dmitry Streblechenko Mar 09 '19 at 19:44

2 Answers2

1

Stevie, if I may so bold to Answer this...

  1. The signature file is located - C:\Users\username\AppData\Roaming\Microsoft\Signatures
  2. Their personal files for images are - C:\Users\username\AppData\Roaming\Microsoft\Signatures\[singatureName]_files Ex: "Business_files"
  3. The images appear to pull in image00#.jpg indexing
  4. You can try using HTMLBody VS just body
  5. You can use NameSpace.GetDefaultFolder to get the users default email account, thus getting that accounts' Signature

Perhaps when you use the NameSpace.GetDefaultFolder, then can look at signature name, then can do the [name]_files to get any images they may have in there. Please note that if you use HTMLBody, as it's an issue with my company, the user will have a block from the Programmatic Access Trust Center settings in Outlook. THis prompts the user with an Allow/Deny box, in which they can select to Allow for up to 10 minutes... just FYI.

Hope some of this helps. Danny, ExcelVBADude

J VBA
  • 178
  • 2
  • 5
0

You can read "New Signature" and "Reply-Forward Signature" binary values from the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Outlook\Profiles[profileName]\9375CFF0413111d3B88A00104B2A6676[accountNO]\

Victor Ivanidze
  • 397
  • 2
  • 7