Microsoft Access - Automatic Email (access) - monthly, quarterly, etc

Asked By adam on 07-Jan-11 09:38 AM
I have a list of tasks in Microsoft Access that vary in terms of when they are due, and they are all done on a repetitive basis.  Some of them are done daily, monthly, quarterly, and yearly.  I want Access to send out an automatic email to the person(s) the task is assigned to based on when the task is due.  For the monthly tasks, I want access to send this email out 2 days prior to the task due day.  For the quarterly tasks, I want access to send out the reminder email 2 weeks prior to the due date, etc, etc.  Is there a way I can set the task to be repeated daily, monthly, quarterly, etc, and have access send out the email automatically?

Panji Tengkorak replied to adam on 07-Jan-11 10:22 AM
I am not sure if we can have access process scheduled but you can achieve the email send scheduling by creating a simple VB.NET console app that will check the access DB and then put the app in windows scheduler.
Matthew Johnson replied to adam on 07-Jan-11 02:06 PM
You can open Access through a Windows Scheduled Task. What you will need to ensure you have is a startup form that runs the script. For example, I have a report generator on a virtual machine that, every hour, opens, checks to see if any reports need to be processed, and if so, exports that report to PDF then attaches to an email addressed dynamically from the recordset.
Pat Hartman replied to adam on 07-Jan-11 06:30 PM
Rather than have the code behind a startup form run the emails, this is one of the few cases where it is appropriate to use a macro.  You can use Windows Scheduler to schedule Access to run each day at a particular time.  The bat file that runs Access can specify the name of a macro to run when Access is opened.  This bypasses the startup form and allows you to use the same database for both interactive and batch processes.

The macro will run a code procedure that uses the current date to determine what other process needs to run in addition to the daily process.

Depending on what version of Access you are using, you may have trouble sending email in bulk unless you are using Exchange server and can set up a special account that allows bulk email or are able to use another some other mail server to actually send the emails.  Don't forget to close Access as the last step of the macro.
Francis Cheng replied to Pat Hartman on 27-Jun-13 05:32 AM
is there a sample that I can try this automatic email?