Name workbook and save in a specific location

[trevolly]

Hi all,

I've got a workbook that is a request form for engine runs at an airport. The engineers fill in the data and email it to us and then we email the completed request back to them. I've managed to set up vba so that when the form is filled in it saves the file and creates an email and attaches the excel workbook to it and fills in the recipients. The vba also creates a title for the email from the data in the form naming the email "Completed Engine Run Approval Form - GEZAW - 24/07/23" getting the date and aircraft registration from cells in the workbook.

The workbook is attached but the filename is "Engine Run Form Master.xlsm".

Is there anyway that the vba could name the workbook using the same format as the email subject and also save a copy of the workbook to a specific location? "\\gatwick.airport.local\Group9\2023 Airfield Inspection and Data\Aircraft High Powered Engine Runs\NEW\Pending Runs\"

Here's the vba I'm using.....

Sub EmailBackToEngineers() 'Excel VBA to send mail.
Dim OutApp As Object
Dim OutMail As Object
Dim str1 As String
Dim str2 As String

Title = Range("C2")

str1 = [I4]
str2 = [I7]
str3 = [C6]

ThisWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled

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

On Error Resume Next
With OutMail
.TO = str1 & ";" & str2
.Subject = "Pending Engine Run Approval Form" & " - " & Title & " - " & str3
.Body = "Hi," & vbLf & vbLf _
          & "Please find attached a Pending High Speed Engine Run approval form. Please fill in the final section on completion of the engine run and return to us no later than 2 hours." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

As always any help is greatly appreciated guys.

 

[Exceljeanie]

Hi,
as workaround:

first make sure your Title is also a good filename (no forbidden characters).
then You could create the savefullname like
saverfullname = YourPath & Tiltle & ".xlsm" ' Or xlsx depending on your wb type
make sure this is a correct fullname (check Backslashes)
then you can save Your Workbook with
ActiveWorkbook.SaveCopyAs savefullname
And change to
.Attachments.Add savefullname