Re: Run macro for each record in a query

Tom

Sorry - I still don't get this!

1. When you say Change this line: docmd.sendobject .... To:
Forms!SomeForm!txtUserID - rs!UserID, surely I need the docmd.sendobject ...
in there - otherwise nothing will be actually emailed to anyone?

2. Are you saying that I should have the button that sends the emails on a
form (SomeForm) which is based on the query that selects who I want to email
to (qry_client_organisation_for_email)?

3. Assuming the answre to (2) above is Yes, which field from the underlying
table should I name 'txtUserID' - would it have to be the key field?

Sorry to be so thick: I hope you don't give up on me!!

Thanks again
Les


"Tom van Stiphout" <no.spam.tom7744@xxxxxxx> wrote in message
news:ce1fs3t0jj8qd2aeqge7jj5vgsl75phr6c@xxxxxxxxxx
On Fri, 29 Feb 2008 00:12:15 -0000, "Les Isaacs"
<leslie@xxxxxxxxxxxxxxxx> wrote:

Re 1:
The devil is in the details. If you're doing DoCmd.SendObject which
does not take criteria (where-clause) then perhaps your query could:
Change this line:
docmd.sendobject ....
To:
Forms!SomeForm!txtUserID - rs!UserID

Then in the query to run the report state:
select *
from SomeTable
where UserID = Forms!SomeForm!txtUserID
This will make the report run for the current user.

-Tom.



Hello Tom

Many thanks for your reply.

I have not been in the office today (hence the late reply from me), so
cannot try your suggested code. I have two problems with it though:

1. the report that is to be sent is specific to each client_organisation,
so
the object that is to be emailed needs to be regenerated for each pass of
the loop. Will that happen with your code?

2. I also need the reports to be in PDF format. I already have a function
defined within the mdb called ConvertReportToPDF (came from Stephen Lebans
I
think) - but where /how do I use this within your suggested code?

Hope you can help.
Many thanks
Les



"Tom van Stiphout" <no.spam.tom7744@xxxxxxx> wrote in message
news:5oids31tt4d2dl9kp7rl7jfli95jnm26cc@xxxxxxxxxx
On Thu, 28 Feb 2008 04:35:03 -0000, "Les Isaacs"
<leslie@xxxxxxxxxxxxxxxx> wrote:

Here is the skeleton loop code:

dim rs as dao.recordset
set rs=currentdb.openrecordset("qry_client_organisation_for_email",
dbopensnapshot)
while not rs.eof
debug.print "Now sending to " & rs!EmailAddress
docmd.sendobject ....
rs.movenext
wend
rs.close
set rs=nothing

-Tom.


Hello All

I have a table [practices], which contains details of 'client
organisations'. From time to time I need to send an email to certain
'client
organisations'. I have a macro called 'send_emails' which uses the
SendObject command, which I use to send the emails (a button on a form
runs
the macro). The emails include a report that is specific to the 'client
organisation'. Currently I select the 'client organisation' with a
combobox,
run the macro, select another 'client organisation', run the macro
again,
etc. etc.

It would be extremely useful if I could avoid having to select each
'client
organisation' manually. I would like instead to have a query
'qry_client_organisation_for_email' which would select all the 'client
organisation's that are to receive the email (I can easily create this
query), and then have some code that runs the macro 'send_emails' for
each
record in 'qry_client_organisation_for_email'. I am sure this can be
done,
but I don't know the syntax for it!

Hope someone can help.
Many thanks
Leslie Isaacs




.