send a report via outlook to a mail group at 6pm every night!

[Stuart]

Hi there,

I have a database designed with reports created,


the database is kept open all the time and has multiple access.


there may be more than one person logged in to the database at any one
time,


what I want to happen is for access to be able to automatically send a
report each night at 6pm.


can someone help with a macro that could do this?


The To box should be fixed and I wanted to predefine what the message
should say etc.


Can someone please help with this?


Many thanks in advance,


Stuart

 

[bob]

1. create the macro that does it (assume it is called McrMail Report)
2. put this type of command in a batch file (correct as necessary):
"C:\Program Files\Microsoft Office\Msaccess.exe"
"C:\YourFolder\MyDatabase.mdb" /x "McrMail Report"
3. Use Scheduled Items in our OS to run the batch file at 6 pm ..send
yourself a copy as a check

Bob

 

[Stuart]

hi Bob,

Thank you for your reply!!! Glad someone noticed my message,

I have one problem with this way of doing what I need access to do.

1. The database has secure login.

2. Our network does not allow scheduled tasks.

Is there any other way of doing what I need to?

Hope you can help

Thanks again

Stuart

 

[Robert Morley]

Okay, so the other way to do it, then, would be to create a hidden form with
a timer event (or use any visible form that's guaranteed to be open 100% of
the time, like a main menu). The timer event would fire every minute, and
check if it's 6:00 pm yet. If it is, then send the report.

If you need more specific instructions, just ask.



Rob (not to be confused with Bob <grin>)

 

[Stuart]

Hi Rob,

Can you give me some more information on how to perform what you
suggested. I am a novice in MS Access.

Your help is much appreciated.

Kind Regards,

Stuart

 

[Robert Morley]

Okay, on whichever form you've decided to use (main menu if you have one, if
not, a new form is fine):

Open the form's properties and set the Timer Interval to 60000 (60
seconds...you can't set it much higher, so don't try). Then click on the
builder button for the On Timer event, and enter the following code (which
is untested, and typed off the top of my head, so let me know if you run
into any issues):

Private Sub Form_Timer()
Static blnAlreadySent As Boolean

If Hour(Now)=18 Then
If Not blnAlreadySent Then
DoCmd.SendReport acCmdSendReport, "YourReportName", acFormatRTF,
"Your To List", , , "Your Subject", "Your Message Text"
blnAlreadySent = True
End If
Else
blnAlreadySent = False
End If
End Sub

Normally, I try to avoid Static variables, and you can do that too, simply
be declaring the variable at the form level if you want, but this keeps the
code central. The report will be sent as soon after 6:00 pm as possible,
and should only be sent once. Then whether or not it's been sent, at 7:00
pm, it'll stop trying until the next day.

Just make sure that you launch the form as soon as your database opens, and
that nothing closes it.

If this database will be running on more than one computer at a time, you'll
have to get more creative, or you'll get a report sent out from each of
them.



Rob