Compile Names From Multiple Worksheets

[aegliveinterns]

Hello,

In the attached worksheet, I want to be able to hit a button on the TBNT sheet that aggregates all first names and emails of people who have "Failed Applicant" in column J of their local sheet (i.e. LA, NY) into the TBNT sheet. I'd also like to make it so if there is anything in the I column (TBNT), then it will not be sent to the TBNT worksheet since it was sent out already.

Pie in the sky: when I hit the button, if it could automatically merge the email addresses to a template email and send them out.

Thank you so much for your help!

Nate