Email VBA works, have question.

P

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
710
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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.
Code: 
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
You need to add this:

Code: 
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>
 
Upvote 0
This is what I have.
Code: 
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.

Code: 
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.
 
Upvote 0
Wait, I think I have it!!!!

Nope, its just inserting what the code has and not the signature...
 
Upvote 0
It should be

Code: 
    Set OutApp = Nothing
End Sub

not End Function.

If you are using Vista then you need to change

Code: 
   '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

Code: 
   '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"
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Add Outlook "Confidential" Sensitivity Label
Replies
2
Views
178
Aviles
A
S
  • Question Question
VBA sending an email of two conditions are met
Replies
5
Views
243
mumps
mumps
K
  • Solved
Macro : Email Worksheet As PDF
Replies
3
Views
478
Domenic
D
K
  • Question Question
Call cell value Macro
Replies
1
Views
219
karldugan
K
C
  • Question Question
VBA to save file to SharePoint and attach to outlook template saved in SharePoint
Replies
6
Views
250
Chris8630
C

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top