Please find file attached to this query

You can get the desktop path using:

createobject("Wscript.Shell").specialfolders("desktop")

so you could use:

dim savedFile as string

savedfile = createobject("Wscript.Shell").specialfolders("desktop") & application.pathseparator & activeworkbook.name

activeworkbook.savecopyas savedfile

then later use savedFile in the .attachments.add line instead of activeworkbook.fullname

Thank you Velouria, this worked fine. Am I correct in saying this would work for any user who is working on the file?

I also want the file to be saved including current date e.g.  Support_Ticket_Form 12Feb20.xlsm

The original file name is Support_Ticket_Form.xlsm so I'd like to add the current date each time before a file is submitted by email otherwise the latest one will update the previous one and user will loose the history of requests submitted.

What would be the VBA addition for this?

I'm running into an issue as the file is saved originally to desktop with VBA. So next time user opens the same file VBA is trying to override it while it is still open and get the Run-Time error '1004' as per attachment.

I'm now thinking may be it is best not to save the file to desktop and once email is sent the file should be removed from desktop.

The email just needs to have the file attached after data already is entered in the file so it doesn't come blank after submission.

Any suggestions?

The VBA so far is as below:

Sub High_Priority_Mail_workbook_Outlook()

Dim savedFile As String

savedFile = CreateObject("Wscript.Shell").specialfolders("desktop") & Application.PathSeparator & ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs savedFile

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Material Support Ticket Form - High Priority"
.Body = "Hi team," & vbNewLine & vbNewLine & "Please look into this request." & vbNewLine & vbNewLine & "Thanks"
.Attachments.Add savedFile

.Display

Application.DisplayAlerts = False
Application.ActiveWindow.Close SaveChanges:=False

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Set objMsg = Nothing

End Sub

Hi Monish,

Using ActiveWorkbook can get messy when renaming, saving, and deleting files.

I'd also recommend using a yyyymmdd date format if you want to be able to sort files easily in chronological order.

Plus - does the email recipient need to run macros? If not, I'd leave the VBA out of the attachment.

Further, saving to the user's desktop is fine if you're going to remove the file straightaway, but I'd use their temp environment just to make sure that the file is never sitting right in front of them tempting them to open it.

savedFile = Environ("temp") & "Support_Ticket_Form_" & format(date, "yyyymmdd") & ".xlsx"

You can then delete the file from the user's temp after it's attached to the email:

Kill savedFile

Does this line...

    Application.ActiveWindow.Close SaveChanges:=False

...close the file containing the VBA? If so, you should probably get rid of it.