So after many instances of finding the info I needed already posted to this site I finally must ask a question :). Thanks in advance for any help.

I am attempting to automate a process where emails are sent weekly listing employees that have not had a forklift safety course within a year. Our training records are housed in an access database and I have the query to list those employees that are due for refresher. What I am comlpetely lost on is what to use to check this query and email the contents on a weekly basis.

Again thanks for your help! Also, I am not necessarily looking for a detailed solution (although I would not be upset) but if someone can point me in the right direction I should be able to run with it.

Thanks,
Patrick

Assuming you already have email capability built into the database, one solution I have used for a problem like this in the past is to have a table that stores the date that the notifications were last sent out. Then in the startup form of the database, put in some code that, for instance, says:

If this is Monday, do a lookup of the date table.
If the date in the table is today, then do nothing - the emails have already been sent.
If the date in the table is not today, then send the emails and update the date in the table to today.

That way, the first person that enters the database on a day when the emails are supposed to get sent will trigger the process. You might also want to display a message that the process is ongoing, otherwise the person will think the database is hanging. Also, instead of checking for a particular date, you can check to see if it has been greater than 7 days since the emails have been sent.

If you don't have email capabilities set up, I think the MAPI code to do this through an exchange server is on the forum somewhere.

If you need actual code, I can post code I use for a GroupWise mail server, or you can do a search at Microsoft for email code for Outlook / Exchange, for example. If that is not the issue, do you have the code worked out to trigger the emails on a regular basis?


mmcdonal