Hi All
Hope you are all well and able to help
Apologies if this question has been answered as I was unable to find a similar question
I have this macro code and assigned it to a button in a request form which will allow users to automatically submit to a mailbox (see code below)
However, I wish to include 2 cell values which are populated from a down list into the TempFileName and subject email that is sent using the macro button and curious to know if this is even possible.
Sub Mail_workbook_Outlook_2()
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb1 = ActiveWorkbook
TempFilePath = Environ$("temp") & "\"
TempFileName = "New Request Form"
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "Mailbox name"
.CC = ""
.BCC = ""
.Subject = "New Request Form"
.Body = "Hi all," & vbCrLf & vbCrLf & "Please find attached new Request Form."
.Attachments.Add TempFilePath & TempFileName & FileExtStr
.Send
End With
On Error GoTo 0
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your request has now been sent to the mailbox"
End Sub
Thanks for your help in advance
Hope you are all well and able to help

Apologies if this question has been answered as I was unable to find a similar question
I have this macro code and assigned it to a button in a request form which will allow users to automatically submit to a mailbox (see code below)
However, I wish to include 2 cell values which are populated from a down list into the TempFileName and subject email that is sent using the macro button and curious to know if this is even possible.
Sub Mail_workbook_Outlook_2()
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb1 = ActiveWorkbook
TempFilePath = Environ$("temp") & "\"
TempFileName = "New Request Form"
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "Mailbox name"
.CC = ""
.BCC = ""
.Subject = "New Request Form"
.Body = "Hi all," & vbCrLf & vbCrLf & "Please find attached new Request Form."
.Attachments.Add TempFilePath & TempFileName & FileExtStr
.Send
End With
On Error GoTo 0
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your request has now been sent to the mailbox"
End Sub
Thanks for your help in advance
