Sending excel data via an email with variable recipients

[Palmcrest]

Hi I have used some code to send data from a range of cells via an email

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:B5").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "My subject"
.Item.Send
End With
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I would like to reference a cell to nominate a typed in email address as aposed to puting the address in the code.
Im not sure how to do this.
Thanks in advance

 

[SkipVought]

Hi,

.Item.To = ActiveSheet.Range("A1")

...where A1 is where the eMail address resides.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Palmcrest]

Cheers,
Makes sense when it pointed out

 

[Palmcrest]

Spoke too soon.
Using a ref to A1 I get
Error mesage

"Method to object mail item failed"

 

[SkipVought]

So what text is in A1?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Palmcrest]

I put the required email address in A1 and referenced it with the suggested code.

 

[SkipVought]

And what statement does the error occur on?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Palmcrest]

.Item.To = ActiveSheet.Range("A1")