Send Mail with InputBox

[mrowe]

Hi,

I've used the following code (partly from a previous posting) to send my sheet via email to a specfic user. What I would like to know is how to use an input box to send the sheet to who ever the user want to. Creating the Input Box is easy, it's how I define a variable that I don't know.

Thanks


Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SendMail Recipients:="matthew.rowe@tmp.com", Subject:="PIPELINE", ReturnReceipt:=True
MsgBox "Your Request Has Been Sent", , "MESSAGE"
ActiveWindow.Close
Sheets("Enter_NEW_Record").Select
Range("D17:F17").Select
End Sub

 

[Mark O'Brien]

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SendMail Recipients:="matthew.rowe@tmp.com", Subject:="PIPELINE", ReturnReceipt:=True
MsgBox "Your Request Has Been Sent", , "MESSAGE"
ActiveWindow.Close
Sheets("Enter_NEW_Record").Select
Range("D17:F17").Select
End Sub

OK dokes, you would put something like this at the start of your code:

<pre>
'Declare String
Dim sAddress As String

'Initialise the string with the address entered into box
sAddress = InputBox("Enter email address", "Send To")
'If the user doesn't enter an address then exit the sub.
If sAddress = "" Then Exit Sub

</pre>

This does not check to see if the user has put in a valid email address, but it does check to make sure the user put in something.

Then instead of "Martin.Rowe@tmp.com" you would put in:

<pre>
Recipients:=sAddress
</pre>

HTH

 

[mrowe]

Thanks alot! It works a treat.