Automatically Email Reports From Access

Question

I need to automatically email reports from Access at a specific time

I really don't have a lot of experience creating macros in Access, but I tried creating a macro that will automatically send an email at a specific time. This is what it looks like so far:

if [time] = "12:00:00 AM" then
emaildatabaseobject
Object Type Report
Object Name NameofReport
Output Format PDF 
To [e-mail]
CC
BCC
Subject test

Answer 1

I would personally suggest using the MS Access Macro to solely perform the emailing operation (either using the EmailDatabaseObject action, SendObject method of the DoCmd object in VBA, or by automating the installed email client using ActiveX in VBA).

You can then use Windows Task Scheduler to invoke the MS Access Macro at a given time or on a given schedule.

To create a Scheduled Task to invoke an MS Access Macro, perform the following steps:

• Open Task Scheduler in Windows either through the Start Menu or by running taskschd.msc
• Select the Task Scheduler Library from the left-hand pane (or create your own folder beneath this)
• From the right-hand Actions pane, select Create Task
• Populate the obvious options such as the Name, Description, etc. as appropriate.
• Add a Trigger based on the time/schedule on which you wish to run the macro.
• Add an Action and select Start a Program
• Browse to the location of and select the MSACCESS.exe executable.

• In the Add arguments edit box, specify the filepath to your database (enclosed in double quotes if the path contains any spaces), followed by the /x command-line switch and the name of your macro, e.g.:

  "C:\Your Folder\Your Database.accdb" /x YourMacro
  

  Or, if you have a SubMacro within the Macro:

  "C:\Your Folder\Your Database.accdb" /x YourMacro.YourSubMacro
  

  You can find out more about the available command-line switches for MS Access here.