Include Cell Values from Drop Down list into Macro Code

MO11STA

New Member
Joined
Sep 10, 2015
Messages
32
Office Version
  1. 365
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 :)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top