Hello,

I often need to email the same companies multiple times requesting catalog update files for their various subsidiaries. I would like to create a macro that can compare my request list to a master list, match the email contact from that master list, and compose an email to that contact for each request. I've attached an example.

Request List

Column A - Company to match to master list
Column B - Name of subsidiary that I need the catalog file
Column C - If "NO" then send email. If "YES" then skip
Column D - Return the date/time email was sent
Column E - Does Column A in Request List match anything in Column A of Master List? If "FALSE" then skip.

Master List

Column A - Company name
Column B - Contact First Name
Column C - Contact Last Name
Column D - Contact Email
Column E - Contact CC (if populated)
Column F - Additional CC

To summarize, I want the macro to Match Column A from "Request List" to column A from Master list. If there's a match, create an email based off of the criteria in Master List. Email should say:

Subject: Catalog Request: (Request List Column B)

Hello (Master List Column B),

May you please send the Subsidiary Catalog List for (Request List Column B)?

Thank you,

-Signature

From here, the Request List should flip Column C from "NO" to "YES" and column D should populate the date/time sent.

Anyone have any suggestions or articles? I've found a bunch that do bulk mailings, but nothing that will match against a master list.

Thanks!

Mailing Request Macro.xlsx