How to automate a query and send it via email at a regular time?

[yeske03]

It's been a few years since I've built anything in Access 03, so I'm a little rusty.

I've built a simple database to be used by employees and what I'm looking to do is make it as idiot proof as possible. The basic thought behind building this is to replace the need for hand written purchase orders and move everything to computers.

I've made a query that shows all transactions they've made during the day. Now what I'd like to do is have that query be e-mailed to me daily. It would be nice if it could be set up that every day at 6pm it automatically sends the file, but don't know if thats an option. My other thought would be to write a command within the "CLOSE" command that will email the file and close access with one button.

Any information will be useful. Thank you.

[jimatqsi]

Of course you want to email the output from the query, not the query itself. So that begs the question, in what format, an Excel sheet or PDF, text or what. I do a lot of pdf emails each night, which required a significant upfront setup, but works very nicely. If that's what you want I could give you all you need to know.

For the scheduling, it depends if you want to scheduled within the app because you know the app is running, or if you need to launch the app and force it to do the task before closing the app. I use the latter method.

I use Windows Task schedule to schedule a batch file that looks like this
"C:\Program Files (x86)\Microsoft Office\OFFICE12\msaccess.exe" c:\sm\specialty.mdb /cmd NightlyRun
then in the code I look for the passed command string to take the required actions
If command() = "NightlyRun" Then

Hope that helps.

Jim