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. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,284
    Thanks
    46
    Thanked 255 Times in 235 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
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,736
    Thanks
    7
    Thanked 241 Times in 229 Posts
    You even know where signatures hide.

    cheers, Paul

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

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
  •