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

 

[VoG]

This should help http://www.rondebruin.nl/mail/folder3/signature.htm

 

[pujo]

YOU THE MAN !
THANKS !

 

[pujo]

Houston, we have a problem.

The email code works. When I tried to incorporate the signature code and she is a no go.

I am using Excel 2003 and Microsoft Outlook 2007.

Any suggestions?

Thanks,
Pujo

 

[VoG]

Do you get an error message or what?

Does this apply (from the link I gave you) "Important : This will not work if Word is your mail editor, you can turn that of in the options in Outlook"

 

[pujo]

Compile Error:
Sub or Function not defined

GetBoiler is highlighted

I will look into the issue of Word as the editor.

Thanks for the quick reply.
Pujo

 

[VoG]

You need to add this:

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

</pre>

 

[pujo]

This is what I have.

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>Dear Customer</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("username") & _
     "\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 = "This is the Subject line"
        .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 Function

I am unsure to insert this.

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

I have limited knowledge of VBA but I am learning. Thanks again for your assistance.

 

[pujo]

Wait, I think I have it!!!!

Nope, its just inserting what the code has and not the signature...

 

[VoG]

It should be

    Set OutApp = Nothing
End Sub

not End Function.

If you are using Vista then you need to change

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

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

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

to

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

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

    SigString = "C:\Users\" & Environ("username") & _
     "\AppData\Roaming\Microsoft\Signatures\Mysig.txt"