Emailing reports

[kidkosmo]

Hi gang,

I need some help with setting up some code to send out team member
reports. Here's the deal:

I've been using a form to select a single team member; ie Johnny
Test. I have one report named rptTeamMember which uses a query with
Forms!frmReports!txtTeamMember as the criteria along with a date range
also selected on the form. Then, an option to view or email the
report. The email address is stored in tblTeam under field name
Email. Of course, when you select email, that team member receives
their individual report (sendobject .snp).

Rather than having to do this for each individual, I'd rather set up
the form to select a date range and have a single command button that
will send out each team member's report with a single click (instead
of manually cycling through each team member on the form). I'm sure
this can be accomplished with a For...Next statement, but I really
suck at those. I've already made several attempts at that and the
first attempt ended up stuck in a loop. The second attempt resulted
in the whole team receiving each person's report (which we don't
want).

Any help would be sincerely appreciated.

Thanks!

 

[Danny Lesandrini]

Kid Kosmo:

You're going to want to create a recordset with all the important data.
Something like this ...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim sEmail as String
Dim lEmpID as Long
Dim sEmpName As String

sql = "SELECT EmpID, Email, LName, FName FROM tblEmployee"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If Not rs.BOF Then rs.MoveFirst
Do Until rs.EOF
sEmail = rs!Email
lEmpID = rs!EmpID
sEmpName = rs!FName & " " & rs!LName

' put code to send email here.

rs.MoveNext
Loop

If you need more help, write back, but that ought to get you started.

 

[Tony Toews [MVP]]

Rather than having to do this for each individual, I'd rather set up
the form to select a date range and have a single command button that
will send out each team member's report with a single click (instead
of manually cycling through each team member on the form). I'm sure
this can be accomplished with a For...Next statement, but I really
suck at those. I've already made several attempts at that and the
first attempt ended up stuck in a loop. The second attempt resulted
in the whole team receiving each person's report (which we don't
want).

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

For a page on how to print a report for a single record and how to
generate reports to attach to emails see the Emailing reports as
attachments from Microsoft Access page at
http://www.granite.ab.ca/access/email/reportsasattachments.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/