Email VBA works, have question.

[pujo]

I am using Mr. Bruins code to send email.
It works great. The only thing it doesn't do is add my signature.
Is there any way to implement this into the code?
Here is what I have.

Sub Mail_workbook_Outlook_1()
'Working in 2000-2007
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "me@me.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[pujo]

Here is the way I have it. My OS is XP.
I am getting the text that is in the code, but not my signature.
On the other hand, I am getting no errors.

Private Sub CommandButton2_Click()
 
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2007

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    strbody = "<H3><B>Please see attached file</B></H3>" & _
              "Please visit this website to download the new version.<br>" & _
              "Let me know if you have problems.<br>" & _
              "<A HREF=""http://www.rondebruin.nl/tips.htm"">Ron's Excel Page</A>" & _
              "<br><br><B>Thank you</B>"

    'Use the second SigString if you use Vista as operating system

    SigString = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\Mysig.htm"

    'SigString = "C:\Users\" & Environ("knormand") & _
     "\AppData\Roaming\Microsoft\Signatures\Mysig.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    On Error Resume Next
    With OutMail
        .To = "me@me.com"
        .CC = ""
        .BCC = ""
        .Subject = "CHOPS Pigging Report"
        .HTMLBody = strbody & "<br><br>" & Signature
        .Attachments.Add ActiveWorkbook.FullName
        'You can add files also like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    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

 

[VoG]

In Windows Explorer check that you have this file

C:\Documents and Settings\pujo\Application Data\Microsoft\Signatures\Mysig.htm

(replace pujo with your Windows logon name).

If not, what .htm files do you have in that folder?

Edit:

From Ron De Bruin's page

You must change the file name of the signature to your signature name in the code;
I use the name Mysig in the examples.

 

[pujo]

In that folder I have 4 different files.
They are listed as
myname_files
myname.htm
myname.rtf
myname.txt

I am thinking of an issue that I will have now.
Four different people use this pc and use the same report, but logged in under their own profile. So if I set the code to insert my signature, then when the other users are logged in an working in this XL file when sent it would have my signature on it and not theirs. This could be an issue, and if it can't be fixed to where is inserts the logged in users signature then I dont want to waste your time. I do thank you for your help with this. Please advise.
Thanks,
PuJo

 

[VoG]

You need to change

    SigString = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\Mysig.htm"

to

    SigString = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\myname.htm"

Provided that the other users' signature files are all called myname.htm then the macro should find the right one.

 

[pujo]

Everyones profile is different.
Logged in using their first name.
For me it is (example) "john.htm" the others users "tait.htm", "andrew.htm"
Can this be done, or is it not worth the effort?

Edit: But that did fix the issue of inserting my signature!!

 

[VoG]

Provided that everyone has only one .htm file in that folder then the following should work (untested )

    SigString = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\*.htm"

    'SigString = "C:\Users\" & Environ("knormand") & _
     "\AppData\Roaming\Microsoft\Signatures\Mysig.htm"
    Dim Try As String
    Try = Dir(SigString)

    If Try <> "" Then
        Signature = GetBoiler(Try)
    Else
        Signature = ""
    End If

 

[pujo]

Nope,
I am the only user in that folder.
Every user has their own folder in their profile.
I could copy their .htm file and apply it to the folder, but how would it pick the correct signature ti insert?

I will try that and see what happens, let you know the outcome.

Thanks for all the assistance.

Pujo

 

[VoG]

Yes, the code looks in each user's folder - that's what Environ("username") does - gets the user's Windows logon name and looks in their folder.

The amendment that I've suggested looks for any .htm file in the user's folder and uses it if it is found. The only problem I can foresee is if they have more than one .htm file - the code will just grab the first one found.

 

[pujo]

No, there is only one htm file per folder.
I tried the code, but its not working to insert the signature.
That would be awesome if I could make it work.
I will try playing with it a little.

The error I get is here

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    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

In red

If you think of anything else please advise.

Can't say thanks enough.

pujo

 

[pujo]

Run-Time Error 53
File Not Found