Hello All!

First, let me start by saying that I'm fairly new to using VBA & Macros, and my initial search of the forum didn't return any results to address my specific issues, so any help you might give to my query would be greatly appreciated!

I have a workbook/worksheet, which I will attempt to attach here, consisting of drivers' names and various dates for when their associated credentials in four different columns have expired, are getting ready to expire or are missing.  I'm trying to automate the process of creating some kind of notification system, preferably by email using Outlook, which will send a message to the driver, his/her supervisor, and me, rather than me having to compose an email manually by comparing the conditional formatting (color-coding) I've already established in the file.  I haven't added those email address columns to this worksheet/workbook yet, because I'm trying to decide whether listing them in individual columns on the same sheet (possibly Columns O - Q), or perhaps using a VLOOKUP, or other command, might be a better way, depending on the assistance you might give me.

To complicate matters, I would really like to send emails based on several conditions, but note that the same driver might receive 4 different emails, one for each column, if the dates happen to expire at the same time or even at different times:

1) An email to drivers whose credentials in a particular area (i.e., driver license expiration date) have expired (Columns G - J); sent on the day of expiration, and then every 14 days, until the date in the file is updated to a future date.

2) An email to drivers whose credentials in those same areas expire in the current month; sent on the first day of each month.

3) An email to drivers whose credentials expire in the current week or "today;" sent on Monday of the same week.

4) It would also be beneficial to send an email to recipients for any missing information; sent every 14 days until date is no longer blank.

It should also be noted that the actual file I'll be working with is in a sharepoint library accessed via MS Teams, so it can be used by multiple people, and I would prefer that I don't have to open the workbook to run a macro for the emails to be sent.

A potential email, for an expired credential, the day it happens, might look something like this:

 

Date:  03/11/2021  Time:  04:00am

From: William Woodson <[email protected]>

To: Jacob Brown <[email protected]>

CC: Jane Doe <[email protected]>; John Public <[email protected]>

Subject:  REMINDER!  Your DQ File Requires Attention

Jacob:

This is just a quick reminder to let you know that, according to your DQ File records, your DRIVER LICENSE on file, which allows you to be authorized to drive company vehicles, expired today, March 11, 2021.

Please reply to this email with a copy of your current DRIVER LICENSE attached as soon as possible.

Respectfully,

Your DOT CMV Administrator

Note: This message will be repeated every 2 weeks until the credential listed above is no longer expired.