Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  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,983
    Thanks
    90
    Thanked 453 Times in 413 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,847
    Thanks
    22
    Thanked 544 Times in 515 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,983
    Thanks
    90
    Thanked 453 Times in 413 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,983
    Thanks
    90
    Thanked 453 Times in 413 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 142 Times in 135 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.

  11. #10
    New Lounger
    Join Date
    Aug 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    will this work if your using a citrix based system?

  12. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    5,847
    Thanks
    22
    Thanked 544 Times in 515 Posts
    Assuming you mean Windows virtual desktop, then yes, it will work. If you are using XenApps it may or may not, depending on the packaging.
    More information on your set up please.

    cheers, Paul

  13. #12
    New Lounger
    Join Date
    Aug 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    we are using using citrix to access our own workspace from another server from our main site.

    the automail is a mail loop wherein we have 3 tabs, 1st tab is sending email tab which us used to create the body of your email, enter you subject, the second tab is where you input a report or summary of bills etc, the 3rd tab is for email database.

    basically just put the report needed, make sure you have the email address on the database, then it will send all emails to all recipient based on the report you've created.

    my concern is that we are unable to access the location on the signature's because of we only accessing it from another server.

    Quote Originally Posted by Paul T View Post
    Assuming you mean Windows virtual desktop, then yes, it will work. If you are using XenApps it may or may not, depending on the packaging.
    More information on your set up please.

    cheers, Paul

  14. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    5,847
    Thanks
    22
    Thanked 544 Times in 515 Posts
    Do you use a desktop at all or just Citrix VD? If it's just Citrix you should have the same C: all the time, regardless of where you log in. If this is not the case you can set up a scheduled task to copy all signatures to the required location.

    cheers, Paul

  15. #14
    New Lounger
    Join Date
    Aug 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    its having your own desktop. if you to my computer C: is not present but I can still access it if I type it manually, but I', unable to locate appdata, I think this is hidden by default, we don't have the any access on changing those settings

    Quote Originally Posted by Paul T View Post
    Do you use a desktop at all or just Citrix VD? If it's just Citrix you should have the same C: all the time, regardless of where you log in. If this is not the case you can set up a scheduled task to copy all signatures to the required location.

    cheers, Paul

  16. #15
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,983
    Thanks
    90
    Thanked 453 Times in 413 Posts
    mrpasawai,

    It will work if you built your signature into the body of your email instead of trying to attach it. Use something like

    Code:
    .HTMLBody = "Hello," & "<br />" & _
            message & "<br />" & "<br />" & _
            "Thank you," & "<br />" & _
            "Maudibe" & "<br />" & _
            "<IMG src=""\\lbfshr1\users$\Maudibe\My Pictures\LBlogo (2).bmp"">"
    The image tag should point to the server folder that contains the image file and not a local folder on the computer where Citrix is being run
    HTH,
    Maud

Page 1 of 2 12 LastLast

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
  •