Adding to filename with vba

[trevolly]

Hi all,

I currently have an excel form that is used at work to request permission for a specific process. This workbook originates from an engineer who fills in details and emails it to my department (the airport) and then I fill in information and email it back...etc.... There is vba written for each step of cmpletion, so that when the form is filled in a button is pressed and the macro runs, saving the workbook and attaching it to an email. The original xlsm file is named "Engine Run Form".

The vba works fine but at each step it asks if the original file wants to be overwritten. The vba code uses the fullname of the workbook to name the exported xlsm file, hence the request to overwrite the original file. Is there any way to add an extra part to the filename when the vba runs at each stage? IE when the engineer sends the form it may add "Engineer Request" to the filename for example...... then when I email back it adds "Granted".

The vba is pretty much the same for each step....

Thanks if you can help
Trevor

Sub EmailRequesttoOps() '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 = [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 = "engineruns"
.Subject = "Engine Run Request Form" & " - " & str1 & " - " & Title
.Body = "Hi," & vbLf & vbLf _
          & "Please find attached the completed engine run request form for your approval." & 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

 

[Micron]

a) get the workbook name and assign to a string variable
b) If block:
- using Instr function, test if variable contains "Engineer Request". If not, concatenate that to the name variable and save as, using variable value as file name parameter
- if it contains Engineer Request, then concatenate "granted" to the variable and saveas (as noted above)
- probably should test for "granted" as well and decide what to do if something would cause a resave when you already have 2 values appended.

Workbook.SaveAs method (Excel)

Office VBA reference topic

learn.microsoft.com