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. Subscribe to our Windows Secrets Newsletter - It's Free!

    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
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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,508
    Thanks
    7
    Thanked 220 Times in 208 Posts
    You even know where signatures hide.

    cheers, Paul

  6. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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
  •