Compile Names From Multiple Worksheets

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

How are you at VBA? The reason that I ask is that there is a thread here for emailing form letters. It is an OutLook macro, but could be tunred into an Excel macro.

The first part of your question could be solved using a little manual activity by autofiltering the local sheets and copy the displayed rows to the TBNT sheet. If you would like to avoid this manual activity, we can help with a macro as long as you tell us how we identify which sheets are the "local" ones.