Calling Outlook from within Excel

  • Thread starter Thread starter Hogan's Goat
  • Start date Start date
H

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!
 
Outlook cannot attach files until they're saved. Your code will need to save
the active workbook first, then attach it.
 
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.
Click to expand...

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?
 
Sure:

objMail.Attachments.Add yourvariable_with_the_path

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



Hogan's Goat said:
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.
Click to expand...

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?
Click to expand...
 
Back
Top