I hope someone can help, been trying for months (on and off) to research, learn and do this myself but to no avail.

I want to email list of recipients individualised sections of report specific to them. I have found a code besides the ones at www.granite.ab.ca (http://www.granite.ab.ca/) etc and used it to develop a test db. However, I cannot get the code to work and would appreciate any and all help.

In specific I cannot understand how to go about setting up the bits in bold italics below. How do I set up the query and the report so that the code will look at and automate the attachment of sections of the report to the appropriate recipient?

I attach the db, and include below the code and instructions from the website for reference and for others to follow (there appears to be many requests for this!)

Instructions for mailing customised reports.

1. Produce your report for all members.
2. Hold the query that the report is based on (‘BaseSQL’ in the code below).
3. Open a recordset to provide the recipients.
4. Loop through this recordset, and for each recipient:

Change the query that the report is based on to include a Where clause to individualise it.

Email the individualised report to that recipient

Return the query to the original state without Where clause)

So the code will look something like this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT ManagerID, Email FROM ManagersTable")
Set qdf = dbs.QueryDefs("YourReportQuery")
BaseSQL = qdf.SQL
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE ManagerID =" & !ManagerID
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport", "Snapshot Format", !Email
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

For starters, the saved query already includes the WHERE clause, so you double it up and end up with invalid SQL. I probably wouldn't bother with trying to retrieve the SQL from there, since it's static. Just do:

BaseSQL = "SELECT ManagersTable.Email, Master.FirstName, Master.LastName, Master.AppointmentDate, Master.AppointmentTime, Master.SolicitorName FROM ManagersTable, Master"

I'd warn you that without a join, you will likely get more records than you expect with that SQL. I would expect a manager ID field in the master table.

Many thanks for the very quick reply and very sound advice Pbaldy.

I attempted what you suggested and the code now runs through the list without the SQL errors! I also made the joins in the master table.

I made some other mods to the code which I considered to be unnecessary however, I still cannot separate and send the information from the report to the appropriate individuals. All recipients receive the full report instead of their part.

Any ideas?

I attach the updated db for ref again. Looking forward to your, or any other response.

I probably wasn't clear enough. You took out the part that changed the SQL of the query that the report is based on:

qdf.SQL = strSQL

An alternative would be the method from the site you mentioned:

http://granite.ab.ca/access/email/reporttomultiplerecipients.htm

Thanks again Pbaldy.

Please bear with me while I try and understand this, as my VBA skills are still very basic.

I assume that my basic design of the db is now ok (report and table layout and structure) and what needs to be corrected is to ignore using my original query and use the section of code you mentioned in my code which should do the same job. In addition I assume I need to adapt the the code from www.granite.ab.ca (http://www.granite.ab.ca) (emailing diff report to multiple recipients) in the OnOpen event of my report.

Will doing this then bring me closer to meeting my needs or are there other things I have to consider?

Well, I would say one or the other. Your original method of modifying the query is not the way I would have done it, but it would work fine. Another option is the method in that link. One way or the other, the report must be filtered. Another option would be to add a criteria to your query that referred to a textbox on a form, and inside your loop put the current manager ID from the recordset in that textbox. That's how I've done it.