How to set up a VBA to automatically send an e-mail to Gmail if conditional format is met

Hello Everyone,

In my business, I use an excel spreadsheet for each client that is saved to a single folder. For each client spreadsheet, there is a Missing Info sheet with a missing information column, a reminder date column, and a received missing information column. To track the missing items, I currently use a basic conditional formatting rule (note: I'm not sure I created it correctly, but it seems to work as intended) that highlights a cell red with white font if it is past the reminder due date and if the received missing information column is empty. I have attached a sample spreadsheet with my conditional format rule.

To track the status of missing items, each week I check each client's spreadsheet to confirm what items are still missing. This worked fine in the past, but my client list has grown to the point where it would be useful to have this process automated so that once the conditional format is met I would receive an email to my Gmail account notifying me that a client file still has missing information.

So my first question is can this process be automated via a VBA without having to keep all my client spreadsheets open in excel? (Or perhaps set up a task so that once a week all the spreadsheets in my folder are briefly opened and closed which would trigger the VBA to run?) Can I configure it so that I would continue to receive an email notification about a client's spreadsheet once a week until their file no longer has an item that meets the conditional format rule? Finally, how would I go about setting this up in VBA (I have almost no experience with using VBA)?

If you have any questions, please let me know. Thanks.

Revec