Log In
Register
Home
Active Member
February 12, 2020
Offline
End Sub
Active Member
February 12, 2020
Offline
Please find file attached to this query
Trusted Members
Moderators
November 1, 2018
Offline
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
Active Member
February 12, 2020
Offline
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?
Active Member
February 12, 2020
Offline
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 = "monish@yahoo.co.uk"
.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
Active Member
August 25, 2016
Offline
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.
1 Guest(s)
