Help with emailing reports to multiple clients

Hello all Experts,

I have a grouped report, which prints on a separate page per group. Currently I'm using Stephen Leban's A2000ReportToPDF to print to pdf.

From there I will email it using cdo.

My question is, how can I get each group to print in a separate pdf file.
LVL 28
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I generally do this by opening the report in Preview mode.

Then I create a recordset based on each group and loop through that recordset, inside the loop I set the filter of the report so that it limits the report to one "group".  You could probably then do the ReportToPDF (although if you are using Access 2003 or later, there is an Office add-in for SaveAsPDF), and then send the document.

The advantage that  this has over opening the report multiple times with a filter is that it can save a lot of time.  It would look something like:
Dim rs as dao.recordset
Dim strSQL as string
Dim rpt as Report

Docmd.OpenReport "yourReportName", acViewPreview  'you could also hide it if you want
set rpt = Reports("yourReportName")

strSQL = "SELECT DISTINCT [SomeField] from qry_rpt_YourReport"
set rs = currentdb.openrecordset(strsql, , dbfailonerror)

While not rs.eof
    rpt.Filter = "[SomeField] = " & rs!SomeField   'if numeric field
    'rpt.Filter = "[SomeField] = '" & rs!SomeField & "'"   'if text field
    rpt.FilterOn = true

    'save to pdf here
    'send the pdf here


set rs = nothing

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MacroShadowAuthor Commented:
Thanks for your reply.

Is qry_rpt_YourReport the record source of the report. If it is I may have a problem since the record source of the report is created in the Report_Open event.
Dale FyeOwner, Developing Solutions LLCCommented:

Why are you creating the RS for the report in the report itself?  It's not wrong, but is not the norm either.  Any reason you are not building that query in the form that calls the report, that's where I would normally put it.

If you do it the way I did it above, then the report will already be open when you get to your loop, so you could still capture the RecordSource from the report, it just might not be a stored query.  If you are saying that you build the SQL string in the report Open event and assign that to the RecordSource property, then you might need to try:

strSQL = "SELECT DISTINCT [SomeField] FROM (" & rpt.RecordSource & ")"
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted.>
First I will say that you should only need to use the Lebans utility if you are using Access 2003 or lower.
The Lebans code requires you to create the email from scratch (using CDO in your case)

I a nutshell,  I agree with fyed.
Here is the logic.

At some point you should have a table listing all your Clients
cID (Primary Key)

So you can loop this table to send the emails.
In each loop (like fyed states), you can open the report Filtered and Hidden in Print preview.
Then email it (then close it)

...Then the code is simply this:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strReportName As String

strReportName = "rptClientProjects"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblClients")

    Do Until rst.EOF
        DoCmd.OpenReport strReportName, acViewPreview, , "ClientID=" & rst!ClientID, acHidden
        'Change the last Argument to False to send the email immediately
        DoCmd.SendObject acSendReport, strReportName, acFormatPDF, rst!ClientEmail, , , rst!ClientName & " Project Reports", "Here are your Projects", True
        DoCmd.Close acReport, strReportName

Set rst = Nothing
Set dbs = Nothing

Obviously using CDO will avoid any issues with using Outlook as the email client, but it still adds another level of complexity to the app.


Jeffrey CoachmanMIS LiasonCommented:
...In other words, why bother going through all the trouble of trying to "Split" the report.
...Just email each client their own individual report.
MacroShadowAuthor Commented:

I tried strSQL = "SELECT DISTINCT [SomeField] FROM (" & rpt.RecordSource & ")"  but it dosn't work, I get a runtime error 3131.

Also, I'm not sure what SomeField is in this line: rpt.Filter = "[SomeField] = " & rs!SomeField.


I hope you don't mind but I'd rather focus on fyed's solution first.
Jeffrey CoachmanMIS LiasonCommented:
<I hope you don't mind but I'd rather focus on fyed's solution first. >
Don't mind at all.

You should always try the Expert's solutions in chronolgical order.

MacroShadowAuthor Commented:
Thanks. Just letting you know that I'm not ignoring you.
Dale FyeOwner, Developing Solutions LLCCommented:
[SomeField] is a reference to the field in your report that you are grouping by.  You need to replace that with the name of the field in your report.  You need to do this in both the SQL string, and in the line where you define the filter, inside your loop.
MacroShadowAuthor Commented:
Any idea what to do about this:

I tried strSQL = "SELECT DISTINCT [SomeField] FROM (" & rpt.RecordSource & ")"  but it dosn't work, I get a runtime error 3131.
Dale FyeOwner, Developing Solutions LLCCommented:
What field are you grouping by in the report?  This would be the field that defines who your email recipients are going to be.

As I stated above, you need to replace all of the references to [SomeField] with the name of the grouping field, so it might be:

strSQL = "SELECT DISTINCT [ClientID] FROM (" & rpt.RecordSource & ")"


rpt.Filter = "[ClientID] = " & rs!ClientID   'if numeric field
MacroShadowAuthor Commented:
I did that before posting but I get an error about their being a syntax error FROM (something like that I don't remember the exact error).
Dale FyeOwner, Developing Solutions LLCCommented:
You still have not told me what the name is of the field you are grouping by!

You need to add a line right after the line that builds the sql string.  Try:

strSQL = "SELECT DISTINCT [ClientID] FROM (" & rpt.RecordSource & ")"
debug.print strSQL

Then you can copy that line and post it back here, so I can see what it says.  Then, you need to get a screen shot of the error message, and post it here as well (either that, or write it down - exactly as on screen).
MacroShadowAuthor Commented:
Thanks for your time, and sorry for the aggravation (if that's what the exclamation mark meant).

The name of the field is ID (In the query the full name is B.ID).

The error is runtime error 3131 as I mentioned above, the exact wording is: Syntax error in FROM clause.

The value of strSQL is:
SELECT DISTINCT ID FROM (SELECT DISTINCTROW B.*,A.*,tblSellers.*,B.ID FROM (tblBuyers AS B LEFT JOIN tblRequests AS R ON B.ID = R.BuyerID) LEFT JOIN ((zstblMatches LEFT JOIN tblApartments AS A ON zstblMatches.PropertyID = A.PropertyID) LEFT JOIN tblSellers ON A.SellerID = tblSellers.SellerID) ON R.RequestID = zstblMatches.RequestID;)

Open in new window

For now I'll try :

Open in new window

Will it really work, or am I wrong?
Dale FyeOwner, Developing Solutions LLCCommented:
OK, there are a couple of issues we have to deal with.

1.  I rarely use wildcards like this to select all the fields from a table.  It is highly unlikely that you are displaying all of those fields in your report, so I would recommend explicitly identifying which fields you need for your report.  

2.  In your Query, you use both B.* and B.ID.  You need to delete the B.ID because that is already included in the B.* request.  If tables A, or tblSellers contains an "ID" field, you will also have issues.  That is part of the reason that I explicitly idenfity which fields I need, so that I can provide aliases for fields with similar names across multiple tables.

3.  Try using the following SQL Statement:

            & "FROM (" & Replace(rpt.Recordsource, ";", "") & ") as Temp"

This will remove the semi-colon at the end of the string you are using for your reports recordsource.
MacroShadowAuthor Commented:
So far this seems to work.

However I must modify strSQL to include the email (tblContacts.Email), can you please help me with that?
Dale FyeOwner, Developing Solutions LLCCommented:
I'm a bit confused.  If B.ID comes from the Buyers table, where does tblContacts come into the mix, it isn't even in your original query?

If you are grouping on the BuyersID, wouldn't you want to send this to the Buyers, not the "contacts"?
MacroShadowAuthor Commented:
I'm sorry, you are right it is tblBuyers (tblContacts was the original name, and was changed, I just forgot that).
Dale FyeOwner, Developing Solutions LLCCommented:
In that case, try:

strSQL = "SELECT DISTINCT Temp.ID, Temp.Email " _
            & "FROM (" & Replace(rpt.Recordsource, ";", "") & ") as Temp"
MacroShadowAuthor Commented:

Thank you. I adapted the code you provided to suit my needs.

@boag 200

I appreciate your input but for my purposes I rather use cdo (even though I do understand that using sendobject is a lot quicker).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.