Trouble expanding cell range in a vLookup in a macro

[NoSuperUserHere]

Hi.

I have a macro that looks at a cell for a name, references another sheet and gets the email that corresponds to the name, and sends an email. I hopped into VBA and thought I entered the correct data to expand my vLookup. The original expression is:

Recipient = Application.VLookup(ActiveSheet.Range("A15").Value, Worksheets("List").Range("A2:B100"), 2, False)

I thought if I expanded the range like so:

Recipient = Application.VLookup(ActiveSheet.Range("A15:A17").Value, Worksheets("List").Range("A2:B100"), 2, False)

I could add more names below cell A15 and have the recipient be multiple people. It's not working. The "To" field is blank and the email doesn't get sent. What am I missing? Do I need to do an "and" statement? Would that look something like this:

Recipient = Application.VLookup(ActiveSheet.Range("A15").Value AND , Application.VLookup(ActiveSheet.Range("A16").Value AND , Application.VLookup(ActiveSheet.Range("A17").Value AND , Worksheets("List").Range("A2:B100"), 2, False)

 

[Anfinsen]

I think you need have your code loop through the range.

 

[NoSuperUserHere]

I hadn't thought of that. So it would be something like this?

Recipient = Application.VLookup(ActiveSheet.Range("A15").Value, Worksheets("List").Range("A2:B100"), 2, False)
Recipient = Application.VLookup(ActiveSheet.Range("A16").Value, Worksheets("List").Range("A2:B100"), 2, False)
Recipient = Application.VLookup(ActiveSheet.Range("A17").Value, Worksheets("List").Range("A2:B100"), 2, False)

 

[Anfinsen]

How many potential recipients can there be?
is there always a recipient in A15, A16 and A17?

 

[NoSuperUserHere]

No more than 5.

 

[Anfinsen]

easiest way I can see is to declare them seperately.

Dim Recipient1 as String (assuming this is how you have it declared)
Dim Recipient2 as String
Dim Recipient3 as String
Dim Recipient4 as String
Dim Recipient5 as String


Recipient1 = Application.VLookup(ActiveSheet.Range("A15").Value, Worksheets("List").Range("A2:B100"), 2, False)
Recipient2 = Application.VLookup(ActiveSheet.Range("A16").Value, Worksheets("List").Range("A2:B100"), 2, False)
Recipient3 = Application.VLookup(ActiveSheet.Range("A17").Value, Worksheets("List").Range("A2:B100"), 2, False)
Recipient4 = Application.VLookup(ActiveSheet.Range("A18").Value, Worksheets("List").Range("A2:B100"), 2, False)
Recipient5 = Application.VLookup(ActiveSheet.Range("A19").Value, Worksheets("List").Range("A2:B100"), 2, False)

 

[NoSuperUserHere]

Thank you! I'll give it a try!