email attachment that is not saved

[dohc46]

Hello,
I would like to be able to send the active workbook using Outlook automatically with VBA. I have the following code, that works fine IF the workbook has already been saved. I want this to work regardless if the workbook has or has not been saved. Example of a not saved workbook would be: Co-worker asks me to provide data, that I don't really care about...so in Excel I simply click File, New Blank workbook which is called Book1 (not saving it) I then do my thing requested. Again, the following code only works if I save the file.

Sub EmailtoAssociate
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)

With OutMail
  .To = "Associatesemail"
  .Subject = Activeworkbook.name
  .Attachements.Add Activeworkbook.FullName
  .Send
End With
End Sub

If I run this code when the active workbook has previously been saved somewhere on my drive/network it works perfectly. However it crashes if it has not been saved. The following error message occurs:

Run-Time error
Cannot fine this file. Verify the path and file name are correct.
and when I click on debug. the following line appears to be the problem: .Attachements.Add Activeworkbook.FullName

Thank you!

 

[mumps]

I don't think that it is possible to attach a file without saving it first. You can, however, insert a range of data directly into the email.

 

[dohc46]

Thank you for replying "mumps"...that would appear to be the case; even though it doesn't seem logical to me that you can't. I don't understand why it is easy to manually do it in Excel but it can't be programmed. I can easily start the Excel application; click on "blank worksheet" which it calls "Book1" and can then do work on it (never save it), then click on File/Share/ "Attach a copy instead "Excel Workbook". That takes me directly to Outlook, in which the unsaved attachment is ready to be sent.

 

[mumps]

You are very welcome.

 

[dohc46]

How can I code this scenario? IF the activeworkbook has not been saved, then save it wherever the current directory is; run the macro in my original question (send email); delete the file from the directory that was just saved. IF the activeworkbook has been saved then simply run my macro in my original question.

Thank you.

 

[mumps]

IF the activeworkbook has not been saved, then save it wherever the current directory is

Since it has not been saved, there is no current directory. You will need the full path to a folder and a name to use in the macro.