String from a Range

[locopete999]

Hi Guys,

I have a VBA macro that sends an e-mail.

I'm now trying to get it to lookup the e-mail address.

For testing I was using

Const stTo As String ="E-mail"
Call oUIDoc.FieldSetText("EnterSendTo", stTo)

I'm happy with this, and this works, But now I want to look up the e-mail address, using a second excel document as my contacts list, based on the account number.

I've tried playing around with

Const stTo As Range  = Activesheet.Range (H25)
Call oUIDoc.FieldSetText("EnterSendTo", stTo)

and a couple of others. I'll be honest, I was fishing hoping it would work.

Cell H25 has a Vlookup and so contains the contact I want it to use in the string.

Can anyone help??

[Kenneth Hobson]

You don't need that to be a constant. H25 should be "H25". You don't need ActiveSheet unless you are going to prefix the workbook object to it. Range() defaults prefix to activeworkbook.activesheet.

You seldom need Call. e.g.

oUIDoc.FieldSetText "EnterSendTo", Workbooks(2).Worksheets(1).Range("H25").Value

[locopete999]

Thanks Kenneth.

I've got it working now.