Prev: invalid syntax
Next: Excel Automation and Vista
From: Deb C Deb on 4 Oct 2007 18:55
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?
From: Mr B draccess at askdoctoraccess dot on 4 Oct 2007 22:29
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
--
HTH

Mr B
askdoctoraccess dot com


"Deb C" wrote:

> 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?
From: Tony Toews [MVP] on 5 Oct 2007 22:59
Mr B <draccess at askdoctoraccess dot com> wrote:

>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/
From: Dennis on 9 Oct 2007 11:41
You do realize that you have to create the report (open it that is), and save
it as an RTF or SNP file. The, using the SendObject VBA command, you can send
the file to whomever you wish, as an attachment to the email.

"Deb C" wrote:

> How can I link a table to a report with one field holding the filter for the
> report and the other the email address of who the report should be sent to.
> Then automatically sending the reports.
 | 
Pages: 1
Prev: invalid syntax
Next: Excel Automation and Vista