Jill, yes you can. You would need to do something like this:

Set rangeToSearch = Workbooks("name_of_wb_containing_AO63.xls") _
.Sheets("AO63").Range("email_lookup_and_address_ra nge")

foundValue = Application.VLookup(range("d21"), _
rangeToSearch, 2, False)
If IsError(foundValue) Then
MsgBox "No match found"
'code to stop macro
End If

'code to use foundValue as the email address

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"Jill" wrote in message
...
I use a spreadsheet with a macro attached to import a text
file "file.*" the file is then formatted and sent to a
mail recipient via outlook. This works really cleanly, but
I now wish to make the mail recipient dependant on a value
held within the text file. My problem is that the text
file is opened from the sheet A063 the processing
completed and the file is then dumped. The cell with the
contact reference number is held in cell d21 on the active
sheet but the lookup is held on the sheet A063. Is it
possible to use a lookup to set a string contact name from
the same macro that formats the text file? If so how?
Any help appreciated.