Date driven auto email (1 Viewer)

I have a form that contains six boxes with the run out date for six different types of employee vaccinations each box contains a conditional format that allows it to change colour to highlight how close they are to requiring another course of injections (Green = within 6 weeks, Amber = within 2 weeks and Red = Out of date).

What i would like is similar to the conditional formatting be able to auto email the individual on the 6 week 2 week and if they happen to run out of date.

Not sure how difficult this will be my access knowledge is vastly improving so any help would be greatly appreciated.

P.s I have searched the access forums and only came close with a birthday greeting style auto email but the fact i want to be a pain and auto email at different intervals hampered that idea!

Thanks in advance Darren

re:

Hi,
you can achieve this with three different queries.
The due date should not be stored. It is a calculated value and can be calculated at runtime within these queries.
So you store a date which shows when the vaccination was injected.
Now create a query based on the table holding this data...add a new expression which calculates the due date e.g.:

DueDate: DateAdd("yyyy",+2,[DateInjected])

This would calculate a due date 2 years after the injection.
You can now use criteria on this expression to limit the results returned by the query to a specific time span e.g.:

BETWEEN DateAdd("ww",+2,Date()) AND Date()

Now run the query and it should show you ALL records which will become due in the next two weeks. If you now want to email these results you can first use the Dcount() function to check the query if it is holding any results e.g.:

If Dcount("*","YourQuery") > 0 Then
'send an email
Else
MsgBox("no records will be due")
End If

You can run this code on the on open event of the first form so it checks the queries when the application is being opened. For the email part you can use the SendObject() method. It is pretty limited...meaning it can only send internal db objects as attachments...only one attachment at a time...it is only meant for interaction with MS Outlook and can only create plain text emails. If none of this conflicts with what you are trying to do you can go ahead and use it...if it does interfere with your plans then use some other automation code to be a little more flexible. You can repeat this with other similar queries to check other time intervals. If you can not guarantee that the application will be opened regularly then create a macro which will run the dcount()/email procedure. You can call this macro with a batch file utilizing the macro command line switch. Schedule the batch file to be run periodically with the windows scheduler or any other scheduling software. Make sure the last action of the macro or the function which the macro calls is to shut down the application.
HTH
Good luck