?
Solved

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

Posted on 2010-01-12
24
Medium Priority
?
1,144 Views
Last Modified: 2013-12-18
i have MS Access Report. I need to automate the procedure and make sure the report will run every morning at 7 AM, save as PDF and send email to the particular person
IS is possible and what needs to be done?
0
Comment
Question by:rfedorov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
  • +2
24 Comments
 
LVL 7

Assisted Solution

by:mmaris
mmaris earned 800 total points
ID: 26297544
"MARS is a dynamic, flexible, function-rich and intuitive automation tool for scheduling, exporting, distributing and delivering your Microsoft® Access reports, queries and macros. "

http://www.christiansteven.com/products/mars/
0
 

Author Comment

by:rfedorov
ID: 26297601
thank you, do you know how much the license?
0
 
LVL 7

Assisted Solution

by:mmaris
mmaris earned 800 total points
ID: 26298381
no I don't, sorry.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:rfedorov
ID: 26298432
looks like, this is not what i want
it too complicated and not free
0
 

Author Comment

by:rfedorov
ID: 26298791
How can I use Windows "Scheduler" to run a batch file, at a specified time and frequency.
0
 
LVL 85
ID: 26302085
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.
0
 

Author Comment

by:rfedorov
ID: 26305155
Thank You,
I am using Access 2003
how to add a a Task to the Scheduler?
Could you be a litttle bit more precise
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 1200 total points
ID: 26324447
The Scheduler is a Windows app.  For Windows XP, use the Scheduled Tasks applet in the Control Panel.  For Vista, open the Administrative Tools program group in the Control Panel, and then the Task Scheduler.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26324536
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"

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26324548
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.
0
 

Author Comment

by:rfedorov
ID: 26324625
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    


   

 
0
 
LVL 1

Expert Comment

by:The_Girl_Earl
ID: 26325043
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.
0
 

Author Comment

by:rfedorov
ID: 26325288
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  
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26326348
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"
0
 

Author Comment

by:rfedorov
ID: 26328402
Thank you, any help with macro,please?
0
 
LVL 1

Expert Comment

by:The_Girl_Earl
ID: 26330577
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.
0
 

Author Comment

by:rfedorov
ID: 26331924
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.....
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26335565
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

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26335573
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"

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26335604
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
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26335623
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).
0
 

Author Comment

by:rfedorov
ID: 26385114
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question