I have an excel spreadsheet, a list of drivers' credentials (i.e., driver license, medical card, etc.), with their corresponding expiration dates in four columns. I already have conditional formatting to differentiate/color-code the expiration dates based on a comparison of "today's date." However, the list contains almost 150 drivers, so I'm looking for a way to automate notifications, by email, to me, the driver, and the driver's supervisor, that date(s) are getting ready to expire, so that I don't have to go into the list manually every day to see which dates are expiring or already expired. Is that possible to do considering that the file itself is stored on a company sharepoint site accessed through MS Teams, so anything available (like Power Automate or Outlook) would be subject to the company's security protocols (which I can't control) and is used/shared by multiple users?Drivers' Credentials Spreadsheet
2 Answers
One way is to import the data into Access and setup a bunch of queries one for each date range. Then in MS Word you can do a mail merge for each query.
Mail merge allows the sending of emails.
- 14,924
You can use vba code to send emails but would still required you to open the workbook at least once a day to run the macro. If the workbook is closed, the macro can't run. Just google "send email via vba".
A power automate flow might provide some method to automate this further (to run without opening the workbook on a local computer), but I am not sure if data in an excel file can be used as input triggers or mail parameters. Power Automate runs fully secured with authentication so security shouldn't be an issue. In fact if your company has Office 365 for enterprise things would be easier.
As for the VBA solution, the most basic VBA macro could just prep emails to send using mailto:// URLs which can include subject and body text. This would generate several draft emails using the local computer's default email client and the active user would need to press the send button several times. This semi-automatic method has the benefit of leaving email server setup to the default mail client.
An automated VBA macro would need to access additional reference objects like the "Microsoft Outlook 16.0 Object Library” or the older "Collaboration Data Objects (CDO)". The former requires that the locally logged-in MS Outlook client be used for sending emails and controls Outlook much in the same way as you could control any Ms Office product with VBA. The latter might require CDO installation first, as well as SMTP server connection settings (with SSL encryption depending on your company's mail server setup, user authentication etc.). Those were just the first two methods/objects that popped up in my initial google search. I'm sure there are others (e.g. if you are using Thunderbird or Groupwise email clients).
- 3,194