Automatic E-mail Generated based on query

smartgir · March 19th, 2004, 01:53 PM

I am using Access 97. I have created a database. I need to send an e-mail alert to a person telling them the issue needs to be dealt with in the next seven days. The other e-mail will send an e-mail to pass due issues.

- The e-mail needs to be sent at 7:00 a.m. every morning to the different individuals who have issues that need to be resolved.

-Another e-mail to the people who have over due issues.

sal · March 19th, 2004, 06:36 PM

You will have to use a Windows OS scheduler to do this, Just make sure that you create a separate file that does only this task to ensure that another user will not be using an mdb that is used as a scheduled job. Use Windows NT Server or Win 2K Server scheduler.

If you have SQL Server, you can use the SQL Server Job Agent.

Sal

smartgir · March 24th, 2004, 02:38 PM

Sal:

I have set up this in scheduler. What do I need to do to get the e-mail to execute in Access. Is there some code I need to write into my module to auto execute this.

Betsy

Quote:

quote:Originally posted by sal
You will have to use a Windows OS scheduler to do this, Just make sure that you create a separate file that does only this task to ensure that another user will not be using an mdb that is used as a scheduled job. Use Windows NT Server or Win 2K Server scheduler.

If you have SQL Server, you can use the SQL Server Job Agent.

Sal

Betsy

jurgenw · March 24th, 2004, 03:19 PM

I suggest you look at the DoCmd.SendObject Method or the SendObject Action. Much of this depends on your email client and the libraries you have installed. Alternatives include CDO libraries and Outlook (not Express) automation code.

Ciao
JÃ¼rgen Welz
Edmonton AB Canada
[email protected]

Steven · March 25th, 2004, 09:17 PM

If, by chance, your company is still using old versions of Windows and Outlook (which thankfully (in this case only) mine does), you can have the email sent automatically using

Code:

DoCmd.SendObject

with the EditMessage argument being False. If your company uses newer versions of Windows and Outlook, this isn't as easy, as Microsoft have "fixed" this very useful "bug", and don't allow sending without user intervention.

So you'll end up with code like:

Code:

DoCmd.SendObject acSendQuery, "YourQuery", _
acFormatXLS, "Someone@somewhere", , , "Some Subject", _
"Some Body Text", False
DoCmd.Quit

If you have an autoexec macro run that code on startup, it will send that query, then quit when the database is opened.

If you don't want to use an autoexec macro, you can use another macro, and specify that in the startup parameters of your schedule - eg:
"C:\Program Files\Office\msaccess.exe" "X:\YourFile.mdb" /x macrotorun

HTH

Steven

I am a loud man with a very large hat. This means I am in charge