how to email an access report automatically every month

[macro to email report not as attachment]

I made several reports in access from queries. I want to send one of the
reports via email at the end of every month. I also want to email another
report every Saturday at 1130pm. Thanks.

 

[Mike]

Are you going to be there on saturday to activate the access database?

 

[SnakesPlane55]

Make sure you can launch your default e-mail program from Access if you
have never used it to e-mail a report. To test use the File/Send
command with a report selected or displayed. If it works it should
bring create an e-mail with your report as an attachment. Then design a
macro or code using SendObject specifying your report and the
recipients. Be sure to specify No for Edit property. Make sure you have
disabled all e-mail attachment safeguards or your e-mail program will
ask for confirmation when Access attempts to e-mail an attachment. Set
up to run this code or macro in your Autoexec macro. Use Windows Task
Scheduler to start your Access database at the time you want to send
the e-mail. When the task runs it will open the database and send the
e-mail. You will probably have to close the database before the task
will run again. You may be able to use a command line batch script to
open and close the database. If so, you can use the Task Scheduler to
run the batch script.

 

[macro to email report not as attachment]

No, I will not be there.

Are you going to be there on saturday to activate the access database?

 

[Steve Schapel]

I agree with Snake as regards setting this up as a scheduled task.
However, it is not necessary, and probably not preferable, to run it via
an AutoExec macro. Put it in a separate macro. In the Command Line of
your scheduler, you can specify the macro, somewhat like this...
"C:\PathToAccess\Msaccess.exe" "C:\PathToDB\YourDB.mdb" /x YourMacro

However, if your default email client is Outlook, you may have a problem
with a security warning, that can't be controlled from Access. If this
turns out to be a problem, some people have reported success with a
third-party utility http://www.contextmagic.com/express-clickyes/
Otherwise, you may have to look at another approach, e.g. Office
Automation, rather than the SnedObject action. There is some good
information at http://www.granite.ab.ca/access/email.htm

 

[SnakesPlane55]

Steve,

I didn't realize you could specify a macro in a task schedule command
line. Thanks for the tip. Would there be a way to similarly schedule
repair and compact and/or database backup to run automatically? Would
you need to write code and have a macro run the code. Is there a way
to call code in a module from a task schedule command line?

As for the Outlook security warning, is there no way to turn this off?
Could the messages be sent to the Outbox and sent later by Outlook if
set up to automatically send and receive?

Thanks.

Snakes

 

[Steve Schapel]

Snakes,

.... Would there be a way to similarly schedule
repair and compact and/or database backup to run automatically? Would
you need to write code and have a macro run the code. Is there a way
to call code in a module from a task schedule command line?

As far as I know, there is no equivalent way to run code. If the
database application is open, you can use the Timer event of a form to
run code at a specified interval. Other than that, you can write a VBA
function, and then use a RunCode action in a macro, which can be
scheduled in the way we already discuused.

As for the Outlook security warning, is there no way to turn this off?
Could the messages be sent to the Outbox and sent later by Outlook if
set up to automatically send and receive?

As far as I know, you can't do it like this. Unfortunately.