Access 2003 - Automatically e-mail report to employees

[Rob 35]

Good afternoon.

My company runs a report that lists deals employees are working on. The information is grouped per employee with a page break after each person. Currently, the report is printed and each employee's information is scanned to PDF and e-mailed to the appropriate employee. We'd like to automatically e-mail employees the section of the report that pertains to them. Is it possible to e-mail an RTF version of the report to each employee as an attachment? I'm open to other options. The user who runs the report uses Access 2003, but we may be able to upgrade them to Access 2007 if necessary.

I hope this request makes sense. If you need additional information, I'd be happy to provide it. Thanks for your assistance.

Rob

***

Table Information

Employee: Initials (Key), FirstName, MiddleInit, LastName, Email

DataFile: RecordNum (key), Emp1 (Employee key), Name1, Name2, Name3, ClosingDate, BillAmount, BillCode (BillCode key)

BillCode: Code (key), Descript, ReportText

 

[apr pillai]

Have a Look at these Posts:

http://www.msaccesstips.com/2007/11/ms-access-and-e-mail.shtml
http://www.msaccesstips.com/2007/11/configure-outlook-for-lotus-notes.shtml
http://www.msaccesstips.com/2007/11/automated-email-alerts.shtml

You can Export the Report in MS-Access Snapshot Viewer Format ( .snp extention) that will retain all the fancy formatting of your Report and less in file size. Those who have MS-Access2000 or later Versions can view this File.

 

[Rob 35]

Thank you for your response, but I was unable to use the information in the articles you sent to address my question. Prior to posting my question, I searched the forum and found several posts that deal with sending e-mail via Access, but was unable to use the information they contained, either. Any help is appreciated.

 

[apr pillai]

This is only an idea and it may work too. A solution to your problem is creating employee-wise dynamic query and use it as data source to the report, changing for each employee through a loop in VBA. Use SendObject method to send the mail with the Report in snapshot format as attachment.

DoCmd.SendObject acReport, "EmployeeReport", "SnapshotFormat(*.snp)", AddressTo, ccTo, bcTo, Subject, msgBody, False, ""

Let the loop repeat for each employee by modifying the SQL of the Query Definition and sending the Mail till all employees are complete.

 

[rconverse]

This is only an idea and it may work too. A solution to your problem is creating employee-wise dynamic query and use it as data source to the report, changing for each employee through a loop in VBA. Use SendObject method to send the mail with the Report in snapshot format as attachment.

DoCmd.SendObject acReport, "EmployeeReport", "SnapshotFormat(*.snp)", AddressTo, ccTo, bcTo, Subject, msgBody, False, ""

Let the loop repeat for each employee by modifying the SQL of the Query Definition and sending the Mail till all employees are complete.

You need to open the report in design view, change the source and then send the report. I do this in one of my routines. It works okay. I don't think it's considered a good practice. I can post code on Monday as the DB is on my work drive.