Use VLOOKUP in VBA to Populate Email Addresses

[dwinter95]

Hey all,

I have a workbook that populates one sheet based on who needs informed of a change in company policy. I need to create a module that looks at each of the populated names and refers back to a table to pull email addresses. I cannot do this in a formula because the list is too long. Any thoughts?

Here is the very long formula I tried that did not work. It also has the issue of not accounting for blanks if there are less people being informed

=HYPERLINK("mailto:"&VLOOKUP(A40,RACI,3,0)&","&VLOOKUP(A41,RACI,3,0)&","&VLOOKUP(A42,RACI,3,0)&","&VLOOKUP(A43,RACI,3,0)&","&VLOOKUP(A44,RACI,3,0)&","&VLOOKUP(A45,RACI,3,0)&","&VLOOKUP(A46,RACI,3,0)&","&VLOOKUP(A47,RACI,3,0)&","&VLOOKUP(A48,RACI,3,0)&","&VLOOKUP(A49,RACI,3,0)&","&VLOOKUP(A50,RACI,3,0)&","&VLOOKUP(A51,RACI,3,0)&","&VLOOKUP(A52,RACI,3,0)&","&VLOOKUP(A53,RACI,3,0)&","&VLOOKUP(A54,RACI,3,0)&","&VLOOKUP(A55,RACI,3,0),Email to MOC Group)

[sintek]

Hi dwinter95

Welcome to the forum...Please be so kind and upload a sample file representing actual file setup and explain in detail what you want to achieve...See Top Yellow Banner...