Access 2003 - Automatically e-mail report to employees

Rob 35

New Member
Joined
Jun 22, 2004
Messages
11
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

apr pillai

Board Regular
Joined
Aug 12, 2008
Messages
235

Rob 35

New Member
Joined
Jun 22, 2004
Messages
11
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

Board Regular
Joined
Aug 12, 2008
Messages
235
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

Well-known Member
Joined
Nov 29, 2007
Messages
1,185
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,288
Messages
5,485,912
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top