Send workbook in email with subject and message

[USAMax]

I did this 10 years ago so I admit I am rusty at this but here are what I had in my notes that worked with Excel XP. I am currently using Excel 2010

With ThisWorkbook
.HasRoutingSlip = True
With .RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = Array("Recipient’s Name")
.Subject = "Monthly Client Status Update"
.Message = "Attached is the monthly status update as requested."
.ReturnWhenDone = True
End With
.Route
End With
ActiveWorkbook.SendMail Recipients:="recipient’s email address"

The last line will send the workbook but I need a Subject and Message to go with it.

 

[Trevor G]

Watch Datapig tutorial

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

 

[USAMax]

Thank you Trevor that was a complete training but I did get the one line I needed out of it that allows it to add a SUBJECT to the email. Is there a way to add a message too?

I currently have:
ActiveWorkbook.SendMail Recipients:="user@domain.com", Subject:="Monthly Client Status Update"

What I would like would be somthing like:
ActiveWorkbook.SendMail Recipients:="user@domain.com", Subject:="Monthly Client Status Update", Message=:"Attached is the monthly status update as requested."

 

[Trevor G]

You can use this code but you would need to set the reference in the VBA screen to use Outlook. In your workbook go into the VBA screen and then select the Tools menu and References search down the list for Microsoft Oultook 14.Object Library and click the box then OK then Insert a new Module from the Insert Menu, copy and paste this code adapt it to what you want

Sub SendOutlook2()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "enter the email address"
.Subject = "This is the subject"
.Body = "Here is the text that needs to be added"
.Attachments.Add "Enter the file path and document name include the extension"
.Display 'Or use .Send to send
End With
Set olMail = Nothing
Set olApp = Nothing

End Sub

 

[USAMax]

That is exactly what I was looking for...

Thank you Trever!

 

[USAMax]

Trevor,

I just tried your code and I received many of the same errors as my orginal code.
Compile error:
User-defined type not defined

This error was for the two Dim lines.

 

[Trevor G]

Trevor,

I just tried your code and I received many of the same errors as my orginal code.
Compile error:
User-defined type not defined

This error was for the two Dim lines.

You need to set the References to use Outlook please follow my previous instructions, use the Tools Menu in VBA and References then search down for Microsoft Outlook 14.Object Library then place a tick in the tick box on the left. Then OK, then Insert a New Module from the Insert Menu.

 

[USAMax]

Hello Trevor,

Your code did everything I wanted except attach the Active Workbook. Can you help me with this?

 

[Trevor G]

In the attachment line place in Activeworkbook.Fullname and that should place in the workbook.

.Attachments.Add Activeworkbook.Fullname

 

[USAMax]

Thank you Trevor,

I already had that line however I ran this command before your function:
Sheets("OVERVIEW").Copy 'Copy the OVERVIEW sheet into a blank workbook

Now I have a workbook called Book2 with no extension because it was not saved. On the last line you sent it says:
Run-time error '-2147024894 (80070002)'
Cannot find this file. Verify the file and path name are correct.


THIS IS THE CODE I AM USING
Function SendOutlook2(Receiver, Subject, Body)
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "enter the email address"
.Subject = "This is the subject"
.Body = "Here is the text that needs to be added"
.Attachments.Add ActiveWorkbook.FullName
.Display 'Or use .Send to send
End With
Set olMail = Nothing
Set olApp = Nothing

End Function