Hi All
Hope you are all well and able to help![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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