Send e-mail reminder when date x comes around.

[james_IT]

Hi guys,

Have searched archives for "reminder" etc but can't find what I’m looking for.

I am designing a lettings management database. I want the database to e-mail 3 or 4 people with a reminder when it reaches a certain date.

I.e. Property X needs a new gas safety certificate to be done on 20/01/2009. When it reaches 10/01/2009 I want it to e-mail the 3 people to remind them.

My questions is in two parts:

1) What’s the best table set-up for this (do I have a table for all reminders / a table for gas safety and other tables for other reminders or does it all go in the one table (property)?

2) How do I automate the e-mail reminders without having to set new code each time. Is there a way to create a reminder form, which the user can put in the property, type of reminder, the due date, the reminder date, and reminder notes/text? Then, when the date reaches the reminder date it will email the whole reminder/reminder text?

Any help/examples greatly appreciated

Thanks

James

 

[spoole]

Hi James, many ways to skin a cat but lets keep it simple, if its just day by day that you need the reminder for, i.e you dont need to check for end dates and times by hour then its nice and easy. Simply everytime you open the database (I assume you close the application and turn your computer off at night and start it again in the morning) have a form that opens with a "on open" event that checks your reminders table for expired or due to be expired reminders? and from there use the "SendObject" function to email the reminder out. Or look into Do-Loop function but this requires you run your database 24 hours a day 7 days a week for the loop to function correctly.
Hope this helps you get in the right direction.

 

[james_IT]

Hi,

thanks,

Ye that points me in the right direction but two questions;

how do i get this on open form to run in the background and what will the code for it to check the reminders table?

ill look into the send object function to get more info.

thanks

james

 

[spoole]

Hey Dude cant reply in to much detail as I am in work.Basically what you want to happen is the form to open automatically and on opening perfoms some sort action i.e check the reminders table and them email those reminders that are due, so:
1. Create a query that queries the reminders table and outputs to a file.
2.Create a new form, on that form you need to add a new macro , but dont add it to a button on the form add it to the form itself. Now the interesting part, you can perform the reminder check, then get it to email all in one piece of code, however Im not that good at VB so cant help, but I would cheat by using macros. On the first line of the macro "SetWarnings" to "No" on the second line "OpenQuery" and select the newly created query, on the last line you need the "SendObject" function. Only one issue with this way is making sure the right email is associated with the right reminder,,,,,,,,, Now how you do this I cant think of off the top of my head so maybe just experiment a little but take a copy of your dbase first