How to schedule auto-exporting MS Access query to Excel and email it?

Question

_{Thursday, October 9, 2014 7:16 PM}

I know Access can setup an Outlook Task to auto-export query to Excel, but it requires the Outlook to be always open on the user's computer.

Is there an easy way to setup a schedule that can automatically export a query to Excel and this schedule will then auto-email the exported Excel file to an email address every Monday at 5AM for example?

If this can only be done in VBA, any reference I may start with?

Thanks.

All replies (3)

_{Thursday, October 9, 2014 10:15 PM ✅Answered | 1 vote}

You need at least a computer running with a default mail client (not necessarily Outlook) so that you can use the Windows Scheduler to run a Scheduled Task at 5 AM to start Access with the /x switch which can automatically run a Macro.

You can then use the Macro action SendObject to send the output of the Query in Excel format as a file attachment in the e-mail.  Alternatively, you can use the Macro RunCode to run a VBA procedure with the SendObject Method (which performs same function as the Macro action SendObject).

Check your Access Help / Access VBA Help Topics:

* Command-line options for Access (for the /x switch).

* SendObject Macro action.

* SendObject Method (VBA).

The alternative to Windows Scheduled Task is to leave Access running with a Timer Event which checks the time and and performs the action just after 5 AM since the timer may not be triggered at exactly 05:00:00.

Van Dinh

_{Thursday, October 16, 2014 1:20 PM}

How about this?

http://windows.microsoft.com/en-us/windows/schedule-task#1TC=windows-7

Then, just add an AutoExec line of code, like this article describes.

http://www.mrexcel.com/forum/microsoft-access/131694-auto-open-procedures-access.html

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

_{Sunday, January 1, 2017 5:09 PM}

Hi,

I had exactly the same problem and I found a simple tool to solve it called AutoSQL. We use it at the company I work for with a lot of success. It works with all databases supporting ODBC, so Access as well. You need to setup and ODBC connection, put in the SQL query you need to run and specify where to output it as Excel and mail it. No need for any advanced VBA.

Kind regards,

Nanne