?
Solved

Help with emailing reports to multiple clients

Posted on 2012-08-13
20
Medium Priority
?
376 Views
Last Modified: 2012-08-18
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.
0
Comment
Question by:MacroShadow
  • 9
  • 8
  • 3
20 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 38289595
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

    rs.movenext
Wend

rs.close
set rs = nothing

Open in new window

0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38291537
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.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38291604
Yes.

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 & ")"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38292168
<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
tblClients
cID (Primary Key)
cFName
cLname
cAddress
cEmail
cPhone
...etc

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")
rst.MoveFirst

    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
        rst.MoveNext
    Loop

rst.Close
Set rst = Nothing
dbs.Close
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.

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38292198
...In other words, why bother going through all the trouble of trying to "Split" the report.
...Just email each client their own individual report.
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38296858
@fyed:

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

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

@boag2000:

I hope you don't mind but I'd rather focus on fyed's solution first.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38297108
<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.
;-)

Jeff
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38297142
Thanks. Just letting you know that I'm not ignoring you.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38297470
[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.
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38299206
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.
The strSQL = SELECT DISTINCT [MyField] FROM (SELECT DISTINCTROW ....)
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38299889
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 & ")"

and

rpt.Filter = "[ClientID] = " & rs!ClientID   'if numeric field
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38300430
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).
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38300483
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).
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38301027
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 :
strSQL = "SELECT DISTINCT ID FROM tblBuyers"

Open in new window

Will it really work, or am I wrong?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38301119
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:

strSQL = "SELECT DISTINCT Temp.ID " _
            & "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.
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38302768
So far this seems to work.

However I must modify strSQL to include the email (tblContacts.Email), can you please help me with that?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38303250
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"?
0
 
LVL 28

Author Comment

by:MacroShadow
ID: 38303792
I'm sorry, you are right it is tblBuyers (tblContacts was the original name, and was changed, I just forgot that).
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 38304554
In that case, try:


strSQL = "SELECT DISTINCT Temp.ID, Temp.Email " _
            & "FROM (" & Replace(rpt.Recordsource, ";", "") & ") as Temp"
0
 
LVL 28

Author Closing Comment

by:MacroShadow
ID: 38308886
@fyed

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).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question