I have a couple clients now who had emails they wanted to either send out on a scheduled basis, like every Thursday, and/or when certain events occurred, like importing a certain type of file with certain types of part numbers, or a po is updated and causes a project to go over in estimated costs for one or more of the line items on the PO.

The clients have had a Microsoft server using Active Directory which is always on, and are using Microsoft 365 for their email services.

What I did was setup a database with an Email queue table. This table stores the information about the email itself, including to, from, cc, bcc, subject, and body information as well as details about whether the email has been processed, whether there was an error when trying to send it, and when the queue entry was created, started processing, and finished processing.

Then I set up the app to connect to this table via DAO and add entries. One of the customers needed attachments so I had a second table with them with the attachment info and the app would upload the file attachments to the server and the server would store the attachment paths that went with each email.

Finally, I needed an Access app that could run in a scheduled task when I was not logged in. This required some special permissions for the user account that ran the job, so the customers needed to give my login credentials that ability.

Then the Access app needed to handle any errors well because any errors could hang the process and Access would never close. This is decidedly BAD.

Anyway, once all this is setup it allows user apps to submit to the email queue and whenever the server’s scheduled task runs, it checks the email queue and sends an email for each record in the queue.

I’m currently using the CDO sending email technique to do this. You can find out more about implementing that method from Phillip Steifel here: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=send-email-access-vba-cdo

This is quite a complete article showing a lot of information and he provides a downloadable demo of all of his code. Very much a class act!