excel vba inputbox

Question

I am have the following code for excel vba that will email a range of addresses in my sheet. Howeve, I am looking to maybe use an inputbox to determine what the range is that I would like to email. The trouble i run into is getting the input to become a value that the function mailid understands. any suggestions?

Sub EmailActiveSheetWithOutlook2()

Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String

Application.ScreenUpdating = False

'Set email id here, it may be a range in case you have email id on your worksheet

 Sheets("Sheet1").Select
 mailId = Range("b4:b5").Value


'Write your email message body here , add more lines using & vbLf _ at the end of each line

Body = "Hello, it appears you have not yet filled out the transportation contact information excel sheet. This sheet was emailed to you, please complete this and send to me  saved as your firstnamelastname.xls at your earliest convience." & vbLf _
& vbLf _
& "Thanks & Regards" & vbLf _
& vbLf _
& "-Ryan " & vbLf _

'Sending email through outlook

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
    .To = mailid
    .Subject = "Transportation Committee Notice"
    .Body = Body
    '.Attachments.Add tWB.FullName
    .send
End With


End Sub

chris neilsen chris neilsen · Accepted Answer · 2012-08-25T22:33:46

To replicate the effect of your current code use

mailid = Application.InputBox(Prompt:="Select Range", Type:=8)

where Type:=8 specifies a return type of Range. This returns the Value property of the selected range into mailid

Alternatively use

Dim rng as Range
Set rng = Application.InputBox(Prompt:="Select Range", Type:=8)
mailid = rng.Value

rng is then set to the selected range, and can be validated before use

Note that you should add error handling to account for, eg user Cancelling the InputBox

Do not set Application.ScreenUpdating = False before issuing InputBox as this will prevent the user interacting with the screen.

As an aside, your code uses Dim incorrectly: Dim'ing a variable without a As clause declares it as `Variant.
eg

Dim oApp, oMail As Object

actually declares oApp as a Variant, use

Dim oApp As Object, oMail As Object

excel vba inputbox

1 Answers