Automatically Emailing Report

[JezzaHyde]

Hi folks.

I currently have a report, based on a table, that contains information regarding changes to data within the database...that is, it records the name of the user that edited/deleted a record, the name of that record, the reason the record was edited/deleted and the time this transaction occured.

As a management type procedure, i have been asked to implement a method of automatically emailing this report to a certain person at a certain time each week and then clearing the underlying table.

I have a few ideas about how to go about it...but i was looking for some input as to a good way to accomplish this.

Thanks in advance

-Jezza

 

[scriverb]

The first command saves a report in Snapshot view. The second command emails the same report in snapshot view.

DoCmd.OutputTo acReport, "YourReportName", "SnapshotFormat(*.snp)", "c:\access\ReportFolder\YourReportName", False, ""

DoCmd.SendObject acReport, "YourReportName", "SnapshotFormat(*.snp)", "Bob", "Mary", "Larry", "Monthly Report", "Here is the report you requested", False, ""

As far as mailing at a certain time each month you can create an ACCESS database with a main menu that works as a scheduler. Using the TimerEvent you can check each day for the day of the month and trigger the above code on that particular day:

If DatePart("d", Date) = 15 Then
DoCmd.SendObject acReport, "YourReportName", "SnapshotFormat(*.snp)", "Bob", "Mary", "Larry", "Monthly Report", "Here is the report you requested", False, ""
end if

Set the Timer Interval to 86400000. This will allow for checking the day once each day.

Just leave this program running always and it will trigger the emailing of the report automatically on the 15th of each month.

Modifications of this type of scheduling can be made to make it more convenient to your situation if you don't want to leave the program running each day.
Bob Scriver

 

[Guest_imported]

Hello,
If you search on Google for Access Automatic Mailer you will probably find what you want.

Cheers,

Carly