Populating the .to for an automated email from a range matching a value

mecerrato · Jan 27, 2023

I have this sheet called "Distribution" that contains the name of the project and the emails associated with the project. I am creating an automated email but need to include all the emails for that project.
I currently have the names hard coded in the .to like this:
.To = "ana.chiriboga@lgihomes.com; trey.williams@lgihomes.com; fik.mesheka@lgihomes.com"

But would like to know how I can use VBA code to match the value in a worksheet called "Loans" where the project name is in column F. I know that I can use ActiveSheet.Range("F" & ActiveCell.Row).Value to get the project name but how do I add the email address in worksheet "Distribution" that match the project name?

mecerrato · Jan 27, 2023

mecerrato said:
I have this sheet called "Distribution" that contains the name of the project and the emails associated with the project. I am creating an automated email but need to include all the emails for that project.
I currently have the names hard coded in the .to like this:
.To = "ana.chiriboga@lgihomes.com; trey.williams@lgihomes.com; fik.mesheka@lgihomes.com"

But would like to know how I can use VBA code to match the value in a worksheet called "Loans" where the project name is in column F. I know that I can use ActiveSheet.Range("F" & ActiveCell.Row).Value to get the project name but how do I add the email address in worksheet "Distribution" that match the project name?

View attachment 83940

I posted it on stackoverflow as well to see what idea they can give me