Using a look up list to determine email address

[irishr_ram]

Hoping someone can help as seems easy but I cannot seem to get it working.

I have a VBA script setup to email a certain selection of cells which is all working fine. However at the moment the email address it sends it to is hard coded if you will and I want to have it determine the email address depending on the value of a certain cell.

So for example I have my lookup entry in C1 which says red so my table below would be

A B
Red dave@aol.com
Blue steve@aol.com
Green terence@aol.com

Result Cell = dave@aol.com

So in this example it would pull out dave@aol.com from the result cell

The code I am trying to get right is here

.Item.To = Lookups.Range("Result Cell").Value

 

[paulxmw]

Hi

I did something similar at work which works on numbers by using Vlookup, i had a table of email addresses on one sheet and with the number next to the email, i then used a vlookup on the sheet where the emails to send is and this worked fine

Hope this helps

paul

 

[irishr_ram]

Hi

I did something similar at work which works on numbers by using Vlookup, i had a table of email addresses on one sheet and with the number next to the email, i then used a vlookup on the sheet where the emails to send is and this worked fine

Hope this helps

paul

Cheers and similar to what I am doing. So I have a separate Lookups Tab which has a list of names and email addresses next to them. Then I have another cell which is the results cell and basically looks up the name on the report and then pulls out the email address.

So can do this fine but then on my VBA I have this line ".Item.To = Range("F5").Value" which F5 has the resulting email address in but does not seem to work and just stops the VBA at the point where it is wanting me to enter an email address.

 

[daverunt]

If you are using the Outlook model for e-mails it is just .To=