Automate Report Run and Email Results

I have a report created that I would like to run at a specific time each day and email the report to certain recipients.  I'm told this is possible, but need to know how to set it up.  Thanks!
LVL 2
johnson00Asked:
Who is Participating?
 
jjafferrConnect With a Mentor Commented:
I corrected the the timer code, but missed up the sendobject, so here both are corrected:

Private Sub Form_Timer()
'Quit the program at 10:30pm if the program is still open
    Dim TheTime As Date
    TheTime = Format(Now(), "hh:mm")

    Select Case TheTime
        Case #10:30:00 PM#
'Either use SendObject OR any other method from here

            DoCmd.SendObject acSendReport, "YourReportName", acFormatRTF,"Jaffer1@home.com;Jaffer2@home.com","Jaffer3@home.com;Jaffer4@home.com","Jaffer5@home.com;Jaffer6@home.com","This is the Subject","This is the Message",0

    Case Else
            'do nothing
    End Select

End Sub

Note, the following from the above:
TO: Jaffer1@home.com;Jaffer2@home.com
CC: Jaffer3@home.com;Jaffer4@home.com
BCC: Jaffer5@home.com;Jaffer6@home.com

jaffer
0
 
jjafferrCommented:

Have a Form Open, in the Timer, set it so that it checks if the time is what you want , then let it run the Report and Send the email/s

1- Form
Set the timer to say 50000 (50 seconds),
On the On Timer of the Form have the following code:

'Assuming you want to print/email at 10pm
if timer="22:00" then
 docmd.sendobject YourReportName, To, Subject, Body  'Please correct the syntax
endif

in the syntax you can put multiple recipients too, just search for sendobject in Access help,
You can use Outlook too.

jaffer
 
0
 
jjafferrCommented:
Ok, this is the correct code:

Private Sub Form_Timer()
'Quit the program at 10:30pm if the program is still open
    Dim TheTime As Date
    TheTime = Format(Now(), "hh:mm")

    Select Case TheTime
        Case #10:30:00 PM#
            DoCmd.OutputTo acOutputReport, "YourReportName", acFormatRTF, "c:\YourFile.rtf", False
    Case Else
            'do nothing
    End Select

End Sub

for other options of sending emails, please see
http://www.experts-exchange.com/Databases/MS_Access/Q_20563192.html

jaffer
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
shanesuebsahakarnCommented:
0
 
shanesuebsahakarnCommented:
I would highly recommend against SendObject as it is rather buggy and has many limitations.
0
 
jjafferrCommented:
Shane, don't you ever sleep man!

I agree, But I had to post my last post because I messed up in the earlier one.

One more thing to look at is the Outlook security,
if you want to automate the sending automatically at odd times when you are not around the PC,
then you should look at options other than Outlook.

if you want to use Outlook, then I would recommend you using ClickYes form http://www.contextmagic.com/express-clickyes/


jaffer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.