Results 1 to 5 of 5
  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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 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

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

    balla506 (2014-08-06)

  5. #3
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,626
    Thanks
    7
    Thanked 231 Times in 219 Posts
    You even know where signatures hide.

    cheers, Paul

  6. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 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.

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

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
  •