How to automate to Run MS Access Report every day, SAve as PDF and send email

thank you, do you know how much the license?

looks like, this is not what i want
it too complicated and not free

How can I use Windows "Scheduler" to run a batch file, at a specified time and frequency.

What version of Access are you using?

You can add a Task to the Scheduler that will run your access database, and fire off a macro when needed. This macro could perform the duties you need. To do that:

"full path to msaccess.exe" "full path to your database" /x "your macro name"

Please note that if you have either an AutoExec macro, or a Startup form defined in your database this may cause you trouble, since Access would also fire those items.

Thank You,
I am using Access 2003
how to add a a Task to the Scheduler?
Could you be a litttle bit more precise

You can write a VBScript (to run from the Task Scheduler) to do the report printing, supposing you have created a macro or function.  Here is a sample script:

Dim appAccess 
Dim strDBName

Set appAccess= WScript.CreateObject("Access.Application")
strDBName = "D:\Documents\Northwind.mdb"
appAccess.OpenCurrentDatabase strDBName
appAccess.Run "FunctionName"

Select allOpen in new window

You can create the script in Notepad, or the Microsoft Script Editor.  Save it with the .vbs extension, and then select it to run from the Task Scheduler.

It looks like thefirst part is working, but strange way
What i did:
This is my file called ReportToRun.Vbs
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase "pathtodb\CARE_DB.mdb"
ObjAccess.DoCmd.OpenReport "MyReport", acViewPreview
Set ObjAccess = Nothing
i set up all dates and time, and at specified time it runs, but gives me Security Warning form with
three buttons : Cancel. Open and MOre info
I have to press Open and then the form just dissaper, runs report and closes
i do not want to see the warnig, it should pass    


   

 

starting from the beginning
Create yourself a macro in your access database.
This should open the report and then do a send to the email address / addresses of your choice in your chosen format.

Test it quickly to see it does what you need it to do....

Once you have done that, open Task scheduler, and go to create task...

Give the task a name in the first screen - in the triggers tab - set it to run at 7am and choose the relevant options of being logged in etc.

In the Actions tab you need to create a New Action
You need it to start a program from the dropdown options given.

Then, in the program script box, put in the path to the msaccess.exe on your machine. For example...

"C:\Program Files(x86)\Microsoft Office\Office14\MSACCESS.EXE"

- note - yours will possibly be in Program files without the (x86) - I'm on Windows 7 with Office 2010.... if you're not sure where msaccess.exe is on your machine, just do a search for it...

In the Add Arguments (optional) put in the path to your database, and the name of the macro you have created to run... for example, I have a database callsed servicedesk2010 on my desktop, and my macro is called Macro1 ( note - I am using windows 7, so the path to your desktop will be different if you're not, but this is an example...

"C:\users\earlj\desktop\servicedesk2010.mdb" /x "Macro1"

If you want any further conditions etc. then just add them into the relevant boxes, but this will do nicely.

Don't forget the " in the relevant places as above.

Give it a quick test, all should be ok.

It looks like thefirst part is working, but strange way
What i did:
This is my file called ReportToRun.Vbs
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase "pathtodb\CARE_DB.mdb"
ObjAccess.DoCmd.OpenReport "MyReport", acViewPreview
Set ObjAccess = Nothing
i set up all dates and time, and at specified time it runs, but gives me Security Warning form with
three buttons : Cancel. Open and MOre info
I have to press Open and then the form just dissaper, runs report and closes
i do not want to see the warnig, it should pass  

Do what The Girl Earl said -- make a macro in your Access database to do what you want, test it to make sure it runs, then run the macro from the VBScript, using this syntax:
ObjAccess.DoCmd.RunMacro "MacroName"

Thank you, any help with macro,please?

very easy... go to macro section in your database, and open a new blank macro...
First line
OpenReport
Then in the bottom, put in the report name and the view ie preview
then
SendObject
in the bottom, use the relevant dropdown to pick Report, and then your report name, then the view you want it sending as ... excel, word etc... then the email address you want it sending to... subject line, text etc.
You can use a third line command to Close the report too, if you want.

Then just save the macro as whatever you want to name it.

Thank you, I am confused now
let pretend my database located in "pathtodb\CARE_DB.mdb"
and name of the report "MYReport"
email is "name@hotmail.com"
how to write the macro,please.....

I recommend doing this task in 4 steps:
1.  A function that outputs the report to a PDF file
2.  A macro that calls the function with the RunCode action
3.  A VBScript that runs the macro
4.  A Windows Scheduled Task to run the VBScript

The functionis listed below; replace the object names with yours.

Private Sub SendPDFReport()
'Created by Helen Feddema 17-Jan-2010
'Last modified 17-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim strFileName As String
   Dim rpt As Access.Report
   Dim strReport As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   
   strCurrentPath = Application.CurrentProject.Path & "\"
   strReport = "rptProductPrices"
   strFileName = "Product Prices.pdf"
   strFileNameAndPath = strCurrentPath & strFileName
   
   'Output report to PDF in current path
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acformatpdf, _
      outputfile:=strFileNameAndPath, _
      autostart:=False
   
   'Create new mail message and attach text file to it
   Set itm = appOutlook.CreateItem(olMailItem)
   With itm
      .To = "someone@xyz.com"
      .Subject = "Daily report"
      .Body = "Your message"
      .Attachments.Add strFileNameAndPath
      'To edit before sending
      .Display
      'To send automatically
      '.Send
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Select allOpen in new window

On second thought, you don't need to create a macro, just run the code from the VBScript:

Dim appAccess  
Dim strDBName 
 
Set appAccess= WScript.CreateObject("Access.Application") 
strDBName = "D:\Documents\Northwind.mdb" 
appAccess.OpenCurrentDatabase strDBName 
appAccess.RunCode "SendPDFReport"

Select allOpen in new window

Correction:  The RunCode in the VBScript should just be Run (it is RunCode as a macro action)
If you get messages asking if you want to Open, etc., that requires you to go through some steps to turn off these annoying security messages.  See my Access Archon # (attached)
accarch119.zip

I don't think you can do the whole process in a macro (in particular, the saving to a file and then attaching the file to a mail message).

Helen, Thank you for your help.
I am able to run the script automatically, and print report i need
Done
Now. i want you modify a little bit the process
the only thing i need ---to save to PDF file in the folder i specify

This is my script
//////////////////////////////////////////////////////////////
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase "\My Path\CARE_DB.mdb"
ObjAccess.DoCmd.OpenReport "PAULA report", acViewPreview
Set ObjAccess = Nothing
///////////////////////////////////////////////////////////

please help me to finish the task and possible enter the line
Save to pdf in the folder C:/backup
Just that...

thank you