Calling Outlook from within Excel

[Hogan's Goat]

I found this code online which works really excellent once you
reference Outlook:

Sub sendmyfilesomewhereplease
ActiveWorkbook.SaveAs FileName:="C:\MyFiles\my new excel file.xls"
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
With myItem
..Recipients.Add "John Q (e-mail address removed)"
..body = "This is the message text"
..Subject = "This is my message subject"
End With
Set myAttachments = myItem.Attachments
myAttachments.Add "C:\MyFiles\my new excel file.xls", _
olByValue, 1, "My file transmission"
myItem.Send
End Sub

However, the attachment part isn't quite what I would want. Instead of
hard-coding the file and path, I would rather just have it call
ActiveWorkbook. I'm a cruddy coder, so I just put Activeworkbook in
there and it didn't work.

How would I make it work? I realize I'm trying to call an Excel object
from within a call to an Outlook app, but I don't know how to tell
Outlook to do that. Any suggestions? Thanks!

 

[Sue Mosher [MVP-Outlook]]

Outlook cannot attach files until they're saved. Your code will need to save
the active workbook first, then attach it.

 

[Hogan's Goat]

Here's a cheerful little earful from Sue Mosher [MVP-Outlook]:

Outlook cannot attach files until they're saved. Your code will need
to save the active workbook first, then attach it.

OK, fair enough. My code can save the file and I guess capture the path
and put it into a variable, then can I use that variable within the same
code to pass to Outlook as an attachment path?

 

[Sue Mosher [MVP-Outlook]]

Sure:

objMail.Attachments.Add yourvariable_with_the_path

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Here's a cheerful little earful from Sue Mosher [MVP-Outlook]:

Outlook cannot attach files until they're saved. Your code will need
to save the active workbook first, then attach it.

OK, fair enough. My code can save the file and I guess capture the path
and put it into a variable, then can I use that variable within the same
code to pass to Outlook as an attachment path?

 

[Hogan's Goat]

Here's a cheerful little earful from Sue Mosher [MVP-Outlook]:

Sure:

objMail.Attachments.Add yourvariable_with_the_path

Looks good, thanks!