Emailing Access Reports

[Guest]

We email individual reports (thru macros) to over 100 users weekly. Thru the
macro process each report is opened, the person selected thru "filter" the
report closed, then emailed thru "send object" in macros. This is difficult
to maintain. I believe I should be able to have a table with the name and
email address then create some way to loop thru the macro filtering thru the
name then sending to the email field. Does anyone know how to do this?

 

[Guest]

Deb,

Yes, you can do this. Just create your table with the email addresses. Then
in VBA code, create a recordset from that table of the email addresses. Get
a count of the number of records and use a "For Loop" to step through each
record in the recordset emailing the report as you are doing now.

First set a reference to the Microsoft DAO 3.6 Library (or which ever DAO
library you may have)

Here is some "air" code to hopefully get you started:

Dim rs as DAO.RecordSet
Dim strEmailAddress as String
Dim varRecCnt, Cntr

'open the recordset (you can use the table or create an sql statement and
use that)
set rs = currentdb.openrecordSet("NameOfYourTable")
rs.MoveLast
rs.MoveFirst
varRecCnt = rs.recordcount

for Cntr = 1 to varRecCnt
strEmailAddress = rs.Fields("NameOfEmailAddressField").value

'here you will use "SendObject" to send the report
'the variable "strEmailAddress" will contain the email address for each
record

rs.MoveNext
Next Cntr

 

[Tony Toews [MVP]]

Dim varRecCnt, Cntr

Variant? Just to pick nits I'd dim those as Longs. Actually I
wouldn't use those at all. See below.

rs.MoveLast
rs.MoveFirst
varRecCnt = rs.recordcount

for Cntr = 1 to varRecCnt
strEmailAddress = rs.Fields("NameOfEmailAddressField").value

'here you will use "SendObject" to send the report
'the variable "strEmailAddress" will contain the email address for each
record

rs.MoveNext
Next Cntr

If you use
Do Until RS.EOF
...
rs.movenext
Loop

you don't even need those variables and you save some lines of code.

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/