Tom
I'm nearly there!
I have:
Private Sub Command2_Click()
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!userEmail
Forms!SomeForm!txtUserID = rs!UserID
DoCmd.SendObject acSendReport, rptUsers, acFormatRTF, userEmail, , , "test
subject", "test message", False
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub
... but this fails on the DoCmd.SendObject line - I get:
runtime error 2487 The Object Type argument for the action or method is
blank or invalid
and then clicking 'debug' gets me to the DoCmd.SendObject line. I have
checked the syntax and can't see what's wrong.
Hope you can help.
Thanks once again
Les
Tom van Stiphout said:
On Fri, 29 Feb 2008 07:33:23 -0000, "Les Isaacs"
Sorry, my mistake.
I meant to say that this line should be added before DoCmd.SendObject:
Forms!SomeForm!txtUserID = rs!UserID
DoCmd.SendObject ... (with the new query!)
Using this technique the UserID to send an email for will change for
each iteration of the loop, the new query will pick up the correct
userID and pull the data for just that user, and SendObject will mail
it out.
I am assuming you have a table with UserID, UserName, EmailAddress,
etc.
-Tom.
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
On Fri, 29 Feb 2008 00:12:15 -0000, "Les Isaacs"
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
On Thu, 28 Feb 2008 04:35:03 -0000, "Les Isaacs"
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
