I have built an excel form for users to fill in and thereafter send it as an attachment (without having to save it locally on their computer). The code is rather simple but works like a charm.

 Dim Names()
    Names = Array("[email protected]")
   ActiveWorkbook.SendMail _
    Recipients:=Names(), _
    Subject:="Test subject"

My problem is that now I would actually not like the email to be sent, just created, until the users have attached an additional file (found on their local computer). I wrote the following code for the specific task:

Dim olapp As Object
Dim olmail As Object
Dim wb As Workbook
Set olapp = CreateObject("outlook.application")
Set olmail = olapp.CreateItem(olMailItem)
Set wb = ActiveWorkbook

With olmail
.To = "[email protected]"
.Subject = "Test Subject"
.Body = ""
.Attachments.Add wb.FullName

My problem in this case, is that only the latest saved copy will be attached to the created email, and since the users of the form will not have the form/excel file stored locally on their computer, an empty form (or the last saved form) will be attached to the email. Is there any way to re-write any of the code above for an email to be created, with a copy of the workbook, but not actually to send it?

Thanks in advance!

1 Answers

Community On

i tried this one-liner in the immediate window and it managed to send an unsaved file: Application.Workbooks("Book2").SendMail("[email protected]","Test Subject") you can use wb from your code instead of Application.Workbooks("Book2") in my example. Note that this will send the email, without the possibility to edit it.