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]

If you have copied the sheet into a blank book then you can look to save that workbook some where first, adjusted code.

Sub SendOutlook2()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Sheets("Overview").Copy
ActiveWorkbook.SaveAs "L:\Temp.xlsx" 'Save first
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 '"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]

Can anyone help me. The code works perfectly for an existing unsaved workbook but it does not work for a new workbook that has not been saved.
ActiveWorkbook.Name and ActiveWorkbook.FullName both say Book5 so when it gets to the line, ".Attachments.Add ActiveWorkbook.Name" I get the error:

Run-time error '-2147024894 (80070002)'
Cannot find this file. Verify the file and path name are correct.

I just want to send the scratch workbook without having to save it.

Anybody have any ideas?

 

[s.ridd]

Can you not save the workbook and then delete it? I may be wrong but it defies [my] logic that you could send a file that doesn't exist. Perhaps by placing the following lines within your code it'll achieve your end goal.

Dim strFileName As String
strFileName = "C:\test\test.xlsx" 'or someother innocuous path and file name
ActiveWorkbook.SaveAs strFileName
'your email code
ActiveWorkbook.Close
Kill strFileName

Would be interested in hearing if anyone has a different solution or approach.

Hope this helps

Simon

 

[USAMax]

I am afraid this is my only option. I have too much security information on the page that needs to be sent and I don't want to lose it so I am coping it to another new workbook before sending. I can use the same file name and keep saving it over the same file each time but this is disk access time I had hoped to avoid.

Thanks Simon