Automatically run a query and email

[RogerC]

Can this can be done?

I refresh a report from a query every day. I then need to send the report to a large group of people. (All are on a shared network. All using Outlook). I would like to TOTALLY automate the process. I know Access can email reports (SendObject), but can it automatically refresh the query every day at a certain time and then email the new report without me having to do anything to make it happen each day?

 

[SydneyGeek]

Roger, you could investigate the OnTimer event on any form. By default the Timer property is set to 0, meaning it doesn't fire. You could set that to 600 so it checks every 10 minutes (increments are in seconds), and write a routine in the OnTimer event that (a) checks that the current time is less than your trigger or a flag value is 1, Exit Sub or (b) if time greater than your trigger and flag=0, run the email routine and set the flag to 1
Maybe this will get the thoughts going in the right direction?
Denis

 

[RogerC]

Thanks SydneyGeek! So it sounds like the whole thing can be done. Great! I will research the OnTimer event. One question, I don't actually have a 'Form' that I'm emailing... just a Report. Will the OnTimer event work on a Report as well as a Form?

What's the weather like in Sydney in December? I'm considering vacationing in Australia later this year.

 

[SydneyGeek]

Hi Roger,

Quick clarification... the 'Form' doesn't need to be the thing that you are emailing. It can be a dummy form in the background, to which you attach the code. You can then run the report and email whatever you like.

As for Sydney in December... it varies. Average temps that time of year are around 26 degrees C (that's low 80s F). Long evenings (dark around 8.30 pm), but it can be a bit humid. A pretty good time of year, mostly -- but if you're here for Christmas, prepare for culture shock -- no snow...

Denis