Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Add Signature to HTML email using VBA

    Can someone help me figure out how to add my standard signature to an email I create and send using VBA. I can get everything I want but that piece. Thanks.


    Code:
    Sub testemail()
    
    If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes Then
    'send email
    Dim myOutlok As Object
    Dim myMailItm As Object
    Dim signature As String
    Shell ("OUTLOOK")
    
    
    Set otlApp = CreateObject("Outlook.Application")
    Set OtlNewMail = otlApp.CreateItem(olMailItem)
    
    signature = OtlNewMail.HTMLBody
    OtlNewMail.HTMLBody = signature
    With OtlNewMail
    .To = Range("H9").Value
    .CC = Range("H10").Value
    .Subject = Range("H11").Value
    .HTMLBody = "Hello," & "<br />" & Range("H12").Value & "<br />" & "<br />" & "<br />" & "Thank you," & "<br />" & "<br />" & signature
    '.Attachments.Add "U:\CRAIG\Actual\" & Range("H6").Value & "\" & Range("H8").Value & "\" & Range("H13").Value & ".xls"
    .Send
    
    End With
    
    
    
    Set OtlNewMail = Nothing
    Set otlApp = Nothing
    Set otlAttach = Nothing
    Set otlMess = Nothing
    Set otlNSpace = Nothing
    Else
    Exit Sub
    End If
    End Sub

  2. #2
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,927
    Thanks
    85
    Thanked 429 Times in 390 Posts
    Balla,

    I have amended you code slightly and added the additional lines needed to grab your default signature.

    HTH,
    Maud

    Code:
    Sub testemail()
    '-----------------------------------------
    'DETERMINE IF EMAIL TO BE SENT
    If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes Then
    '-----------------------------------------
    'DECLARE AND SET VARIABLES
        Dim myOutlok As Object
        Dim myMailItm As Object
        Dim Signature As String
        Shell ("OUTLOOK")
        Set otlApp = CreateObject("Outlook.Application")
        Set OtlNewMail = otlApp.CreateItem(olMailItem)
    '-----------------------------------------
    'GET DEFAULT EMAIL SIGNATURE
        Signature = Environ("appdata") & "\Microsoft\Signatures\"
        If Dir(Signature, vbDirectory) <> vbNullString Then
            Signature = Signature & Dir$(Signature & "*.htm")
        Else:
            Signature = ""
        End If
        Signature = CreateObject("Scripting.FileSystemObject").GetFile(Signature).OpenAsTextStream(1, -2).ReadAll
    '-----------------------------------------
    'CREATE EMAIL
        OtlNewMail.HTMLBody = Signature
        With OtlNewMail
        .To = Range("H9").Value
        .CC = Range("H10").Value
        .Subject = Range("H11").Value
        .HTMLBody = "Hello," & "<br />" & Range("H12").Value & "<br />" & "<br />" & "<br />" & _
            "Thank you," & "<br />" & "<br />" & Signature
        .display
        '.Send
        End With
    '-----------------------------------------
    'CLEANUP
        Set OtlNewMail = Nothing
        Set otlApp = Nothing
        Set otlAttach = Nothing
        Set otlMess = Nothing
        Set otlNSpace = Nothing
    End If
    End Sub

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    balla506 (2014-08-06)

  4. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    5,564
    Thanks
    20
    Thanked 493 Times in 470 Posts
    You even know where signatures hide.

    cheers, Paul

  5. #4
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,927
    Thanks
    85
    Thanked 429 Times in 390 Posts
    Paul,

    They are located in the "C:\Users\Maudibe\AppData\Roaming\Microsoft\Signat ures" folder where Maudibe is replaced by the user's name. The signature will be the .htm file(s) in that folder.

  6. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    This works beautifully!!! Thanks Maudibe.

  7. #6
    New Lounger
    Join Date
    May 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Signatures with images

    This solution works perfectly for me, except in one aspect - the image from my default signature does not display in the generated email. Instead, the outline of it shows, and it says "This image cannot currently be displayed." I wonder if you might know how to resolve this?

    Thank you for the original solution.

  8. #7
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,927
    Thanks
    85
    Thanked 429 Times in 390 Posts
    Bob,

    I can't tell you how many solutions I have tried to do what you are asking but never found anything that really worked. As a workaround you can do what I do.

    1. Create an Outlook signature without a logo. Your default signature is the first one in the "C:\Users\Maudibe\AppData\Roaming\Microsoft\Si gnat ures" folder so make this created signature appear first by naming it something that will be first in alphabetical order. Using the .HTMLBody property, compose your message with the Signature variable at the end of your HTML code (see code post#2) then apply an image tag after a break tag. If your image is sized right, it will appear as part of the signature.

    Code:
    .HTMLBody = "Hello," & "<br />" & Range("H12").Value & "<br />" & "<br />" & "<br />" & _
            "Thank you," & "<br />" & "<br />" & Signature & "<br />" & _
            "<IMG src=""\\lbhfshr1\users$\Maudibe\My Pictures\LBHlogo (2).bmp"">"
    2. Instead of using the default signature, build your signature right in the .HTMLBody property value then apply the image tag as above.

    HTH,
    Maud
    Last edited by Maudibe; 2015-07-03 at 05:49. Reason: remove name

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,121
    Thanks
    2
    Thanked 140 Times in 133 Posts
    Have you tried creating a template email with the signature already present and then using to create the mailitem rather than just creating a blank one?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #9
    New Lounger
    Join Date
    Jul 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fixing missing image problem

    Quote Originally Posted by Maudibe View Post
    Bob,

    I can't tell you how many solutions I have tried to do what you are asking but never found anything that really worked. As a workaround you can do what I do.

    1. Create an Outlook signature without a logo. Your default signature is the first one in the "C:\Users\Maudibe\AppData\Roaming\Microsoft\Si gnat ures" folder so make this created signature appear first by naming it something that will be first in alphabetical order. Using the .HTMLBody property, compose your message with the Signature variable at the end of your HTML code (see code post#2) then apply an image tag after a break tag. If your image is sized right, it will appear as part of the signature.

    Code:
    .HTMLBody = "Hello," & "<br />" & Range("H12").Value & "<br />" & "<br />" & "<br />" & _
            "Thank you," & "<br />" & "<br />" & Signature & "<br />" & _
            "<IMG src=""\\lbhfshr1\users$\fanellit\My Pictures\LBHlogo (2).bmp"">"
    2. Instead of using the default signature, build your signature right in the .HTMLBody property value then apply the image tag as above.

    HTH,
    Maud
    In the .htm file in the signatures directory you can edit the htm file. The pictures are stored as relative path and when you use the code it looses that path so if you use discrete path it will be able to find the pictures. so go into the file and look for any relative paths and make them discrete.

    "/Microsoft/Signatures/picturefile.jpg"

    change that to include the whole path

    "/root/user/blah blah../Microsoft/Signatures/picturefile.jpg"

    This solved the missing image problem for me.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •