Automatically sending an email reminder every thursday of the week (1 Viewer)

[arobbo]

Hi all

I've got a db system linked with webpages that handles rent management and i'd like to set up an email reminder every thursday to tell the student tenants to put the rubbish out for the bin man. I have their email addresses in one table and another table telling them whether its recycling or landfill rubbish.

Basically the question is how would i go about getting access to send the email at a certain time and date ?


Many thanks

 

[reclusivemonkey]

You need to write some VBA to handle this. You can check the current date for which day of the week it is with the Weekday function. You can then use the SendObject command to send the email. Have you written any VBA before?

 

[arobbo]

Yes i've done some VBA before , i had a button that would send an email with an attachment of a sql query exported as an excel spreadsheet.

My VBA knowledge is quite basic , the biggest problem i have is where to put the code so that it will trigger automatically.

Also i have the database on a webserver for it to trigger automatically would access need to be open ?

Thanks for your help

 

[reclusivemonkey]

Yes you will need to have Access open to trigger the code. I would have the code triggered when the "Switchboard" form of your db was opened, then you just need to check that the email hasn't already been sent.

 

[arobbo]

i think i can get it to open when windows starts ...

where would i put the code on the switchboard form .... Onload ?

any ideas how to check whether the email has already been sent , i was thinking of setting it to be sent a certain time on that day , that way it will only happen once ....

 

[reclusivemonkey]

Yes, OnLoad should do it. You can use a table to write a record of which emails were sent on which date. Id the table gets too big, you could always create a query to delete the old info on a weekly/monthly basis.

 

[jrjr]

Just a thought, you could use Windows task manager to run the DB at a predetermined time and have Access shut itself down after the mail is sent.

Add yourself to the mailing list to check that it is working.