Results 1 to 20 of 20

Thread: [RESOLVED] Add signature to emails

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Resolved [RESOLVED] Add signature to emails

    Hi..I have the code below to email out reports. I would like to add my signature at the bottom on the email. Can someone please help. Thanks

    Code:
    Sub Email()
    
    
    mypath = "C:\Reports\"
    Set oboutlook = CreateObject("outlook.application")
    For Each cel In Range("a2:a500")
    If IsEmpty(cel) Then Exit For    
      Set nm = oboutlook.CreateItem(0)  ' new mail item
      With nm
        .To = cel.Offset(, 1) 
        .Attachments.Add mypath & cel & ".xlsx"
        
        .Body = "Please find attached reports" 
        .Subject = "Reports"
        .Save
        .Send
    '    .Close
      End With
    Next
    
    
    End Sub

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Add signature to emails

    Quote Originally Posted by fusion001 View Post
    Hi..I have the code below to email out reports. I would like to add my signature at the bottom on the email. Can someone please help. Thanks

    Code:
    Sub Email()
    
    
    mypath = "C:\Reports\"
    Set oboutlook = CreateObject("outlook.application")
    For Each cel In Range("a2:a500")
    If IsEmpty(cel) Then Exit For    
      Set nm = oboutlook.CreateItem(0)  ' new mail item
      With nm
        .To = cel.Offset(, 1) 
        .Attachments.Add mypath & cel & ".xlsx"
        
        .Body = "Please find attached reports" & vbcrlf & _
                    vbcrlf & _
                    "Your Name" & vbcrlf & _
                    "Your Address" & vbcrlf & _
                    "Phone/Email/Whatever else you want to include" & vbcrlf & _
                    "And so on"
        .Subject = "Reports"
        .Save
        .Send
    '    .Close
      End With
    Next
    
    
    End Sub
    Something like the bolded above should give you an idea of what you need to do.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Add signature to emails

    I ideally would like to add the signature from outlook rather than hard code it.

  4. #4
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Add signature to emails

    I haven't tested myself, but I found the following info on other sites:

    https://www.extendoffice.com/documen...signature.html
    https://it.toolbox.com/question/vba-...gnature-052114

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Add signature to emails

    looks like you just have to read the content of the saved signature file (from the default location in appdata) and add it to the body, as text, in your existing code, or html
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Add signature to emails

    I added this to the code but it has not worked.
    Code:
    Sub Email()
    
    Dim FileNameHTMSig As Variant
    
    mypath = "C:\Reports\"
    Set oboutlook = CreateObject("outlook.application")
    For Each cel In Range("a2:a500")
    If IsEmpty(cel) Then Exit For    
      Set nm = oboutlook.CreateItem(0)  ' new mail item
      With nm
        .To = cel.Offset(, 1) 
        .Attachments.Add mypath & cel & ".xlsx"
        
        .Body = "Please find attached reports" 
        .Subject = "Reports"
        
        DirSig = ("C:\Users\" & Environ$("USERNAME") & "\AppData\Roaming\Microsoft\Signatures")
        FileNameHTMSig = Dir$(DirSig & "\*.htm")
        
    
        .Save
        .Send
    '    .Close
      End With
    Next
    
    
    End Sub

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Add signature to emails

    Code:
        DirSig = ("C:\Users\" & Environ$("USERNAME") & "\AppData\Roaming\Microsoft\Signatures")
        FileNameHTMSig = Dir$(DirSig & "\*.htm")
    All that did was build up the path and get a list of the files at that location. Nothing more. You have to then figure out which file at that location you want, OPEN it, READ the contents, PUT the contents into the email, which may or may not work, and then send the email.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Add signature to emails

    if the content of your signature file is in htm code you will need to convert the htm to text, as you are not using .htmlbody, htm will not be rendered
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Add signature to emails

    I'm struggling with the code to get the signature. I could with some help please. Thanks

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Add signature to emails

    i don't have any signatures setup in outlook, so can not test some code

    have a look at
    https://www.rondebruin.nl/win/s1/outlook/signature.htm
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Add signature to emails

    I have 2016.

    This is how I indicate the path of my signature file:

    Code:
    sigString = Environ("appdata") & "\Microsoft\Signatures\Signature.htm"
    I have co-workers whose file is called "Main.htm" instead of signature, so you'll have to check what yours is called.

    This calls a function to get the contents of that file:

    Code:
    Signature = GetBoiler(sigString)
    This is the function:

    Code:
    Function GetBoiler(ByVal sFile As String) As String
        Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close
    End Function
    As others have mentioned, you'll need to convert your email body one way the other. Since you only have one line, I'd suggest converting the body to HTML, so you could then insert "Signature" after the body.

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Add signature to emails

    Follow up:

    To convert your one line "body" to HTML (where "msg" is a string variable):

    Code:
    msg = "<font size='3'>Please find attached reports</font><br><br>"
    To use the above, and add the signature:

    Code:
    .htmlbody = msg & Signature

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Add signature to emails

    Thanks for that.
    My signature is in this folder C:\Users\fred\AppData\Roaming\Microsoft\Signatures\fred.htm
    The name fred will change depending on the user who runs the code. Is there a way to pick this up?

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Add signature to emails

    Nope... not really because the name depends on the name of hte signature, not the user. I have 3... "Short", "Short-short", and "Full" ... the one I use depends on settings...

    Just about the only way would be if you had an Options screen some where. Then you could list the signatures they have and let them select the one they want. Or no signature.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Add signature to emails

    Or, if they don't have multiple signatures, or just want to use the first one found, you could do something like this:

    Code:
    Sub getSig()
        Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim type3 As String
        Dim mySigFile As String
        
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(Environ("appdata") & "\Microsoft\Signatures")
        
        For Each objFile In objFolder.Files
            type3 = LCase(Left(objFile.Type, 3))
            If type3 = "htm" Then
                mySigFile = objFile.Name
                Exit For
            End If
        Next objFile
    End Sub
    This would give you "Fred.htm" most likely, which you could append to the path in post #11.

    (Or, as TG suggests, if they want to be more sophisticated, you'd have to find all the signature files, and let them choose.)

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Add signature to emails

    if there are multiple signatures, you could have a listbox or similar for the user to select from, then save that selection for future use, but you might then need some option to change the signature at a later date
    or you could have all the users setup a specifically named signature for your app
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Add signature to emails

    Quote Originally Posted by vbfbryce View Post
    Or, if they don't have multiple signatures, or just want to use the first one found, you could do something like this:

    Code:
    Sub getSig()
        Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim type3 As String
        Dim mySigFile As String
        
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(Environ("appdata") & "\Microsoft\Signatures")
        
        For Each objFile In objFolder.Files
            type3 = LCase(Left(objFile.Type, 3))
            If type3 = "htm" Then
                mySigFile = objFile.Name
                Exit For
            End If
        Next objFile
    End Sub
    This would give you "Fred.htm" most likely, which you could append to the path in post #11.

    (Or, as TG suggests, if they want to be more sophisticated, you'd have to find all the signature files, and let them choose.)
    Will I need to have a new sub in addition to the main one in post 4? Thanks

  18. #18
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Add signature to emails

    You could do it either way. Include it in the main, or maybe create a function to return the file name, and call it from the main.

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Add signature to emails

    as you are sending plain text emails you should use the file fred.txt rather than fred.htm for your signature, else the htm code will show in the email, if you want to send a more detailed signature, perhaps with a picture you should change to a html email body

    this is a simple example to insert a signature with picture into a html email
    Code:
    Set msg = ol.CreateItem(0)
    msg.HTMLBody = "this is a signature test <br>"
    Open Environ("appdata") & "\Microsoft\Signatures\untitled.htm" For Input As 1
    strsig = Input(LOF(1), #1)
    Close 1
    msg.HTMLBody = msg.HTMLBody & strsig
    msg.Display
    the displayed email included the initial string and the signature with pictue
    unfortunately i did not give a name to the signature when i created it, hence untitled
    if i was using msg.body i would have inserted the text from untitled.txt instead

    if you want to allow the user to select from whatever signatures he has
    you can use code like

    Code:
    f = Dir(Environ("appdata") & "\Microsoft\Signatures\*.htm")
    If Len(f) = 0 Then MsgBox "no signatures"
         
    Do While Len(f) > 0
        lst.AddItem f
        f = Dir
    Loop
    where lst is a listbox
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  20. #20

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Add signature to emails

    Thanks for that.

Posting Permissions

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



Click Here to Expand Forum to Full Width