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.

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.

Thanks for your response, I am not quite sure of the
correct references to place in the places you have
indicated. For 1. the workbook that contains both the
macro and the email lookup address range is
named 'A063inv.xls' I believe that this should go in
position A between the quotes!. For 2. the address range
is held in 'invA063!sheet1!a1:b20 the A column holds the
refernce that is found in the active workbook that is
being processed (the text file which can be named File.004
or File.007 and is opened by looking for the
file "File.*") column B holds the e-mail recipients name.
For 3. the reference is held on File.*!D21.

1. Set rangeToSearch = Workbooks("A") _
2. .Sheets("B").Range("C")

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

I completed the code with the following;

A = A063inv
B = sheet1
C= A1:B20
D= D21

The code fails with 'Subscript out of range' on the first
section. Would you please have a look at this and correct
my references.
Thanks
Jill

-----Original Message-----
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_range")

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.



.