distribute sectiions of report to different email addresses?

[MikeS3416]

I have a report that is grouped by Department Heads. When printed, each
section begins on a new page that is then snail-mailed to the relevant Head.
I have a field attached to the Department Head with their email addresses. I
want to be able to email the report, using the emails in this field, so that
each relevant Department Head receives only their own data.

 

[SusanV]

You'll need to split the report into multiple reports for each dept head.
You can loop through the field containing the email addresses, and for each
recipient open the report, set the report filter to that person only, email
the report, then close it and go to the next one.

 

[Steve Schapel]

Mike,

This is possible with a macro, but awkward. This is an example of where
a VBA procedure would be much simpler, given the ability to loop through
a recordset. If you are using the SendObject method to do the emailing,
the code might be 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 DeptHeadID, Email FROM
DeptHeadsTable")
Set qdf = dbs.QueryDefs("YourReportQuery"­)
BaseSQL = qdf.SQL
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE DeptHeadID
=" & !DeptHeadID
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

Of course, substitute your own actual names for fields and table and query.

 

[MikeS3416]

Thanks for your help. Sorry about the delay in replying - I've been away.

I have a problem with the SQL code. I assume that “qdf.SQL†picks up the SQL
statement behind the query in “dbs.QueryDefs("YourReportQuery")â€. In this
case the SQL statement should be 2689 characters. What is actually coming
through to “qdf.SQL†is only the first 253 characters. This results in
"Run-time errror '3129'. Invalid SQL statement....." when it gets to the line
"qdf.SQL = BaseSQLâ€.

Any help gratefully received.

Mike

 

[Steve Schapel]

Mike,

I don't know what the problem here is. This code works fine for me with
long SQL statements. I will investigate. Can you post back with the
full SQL view of the query please? ... Just in case there is something
that gives a clue. Thanks.

 

[MikeS3416]

Steve

Thanks for your help.

The full SQL code for the query used by the report and the partial code that
is being picked up by qdf.SQL is below. It is quite long and, I suspect, a
bit messy. Whenever I was asked to include a new field in the report I added
another query/table into the query being used by the report.

The field that the report is spit on is [Owner Code].

Partial code in qdf.SQL:-
"SELECT [Eff Data].[Eff Code], IIf([Data 0708]![0708 Total to
date]=0,False,True) AS [Include on Report], [Strategies & Actions].Strategy,
[Strategies & Actions].[Key Actions], [Eff Data].Owner, Owner.[test email],
Owner.Service, [Eff Data].[Efficiency A"

Full SQL Code:-
SELECT [Eff Data].[Eff Code], IIf([Data 0708]![0708 Total to
date]=0,False,True) AS [Include on Report], [Strategies & Actions].Strategy,
[Strategies & Actions].[Key Actions], [Eff Data].Owner, Owner.[test email],
Owner.Service, [Eff Data].[Efficiency Area], [Eff Data].[XL Ref], [Eff
Data].[Additional Notes], [Efficiency Area].EffSortNo, [Efficiency
Area].Desc, Owner.[Owner Name], [Data 0405].[Add Cum 0405] AS [Totals 0405],
[Data 0405].[Add Cum Cash 0405] AS [Cashable Totals 0405], [Data 0506].[0506
Total to date] AS [Totals 0506], [Data 0506].[0506 Cash to date] AS [Cashable
Totals 0506], [Data 0607].[0607 Total to date] AS [Totals 0607], [Data
0607].[0607 Cash to date] AS [Cashable Totals 0607], [Data 0708].[0708 Total
to date] AS [Totals 0708], [Data 0708].[0708 Cash to date] AS [Cashable
Totals 0708], [Strategies & Actions].[0506 Mid Year Review], [Strategies &
Actions].Contact, [6Mth Totals 0506].[6mthTotals 0506], [6Mth Totals
0506].[6mthCashables 0506], [Original Totals 0506].[OrigTotals 0506],
[Original Totals 0506].[OrigCashables 0506], [Strategies & Actions].[0506 EOY
Review], [Strategies & Actions].[Notes 1], [Strategies & Actions].[Notes 2],
[Data 0506].[Add Cum 0506], [Data 0506].[Add Cum Cash 0506], [Data
0506].[0506 Add], [Data 0506].[0506 cash add], [Data 0607].[Add Cum 0607],
[Data 0607].[Add Cum Cash 0607], [Data 0607].[0607 Add], [Data 0607].[0607
cash add], [Data 0708].[Add Cum 0708], [Data 0708].[Add Cum Cash 0708], [Data
0708].[0708 Total to date], [Data 0708].[0708 Cash to date], [Data
0708].[0708 Add], [Data 0708].[0708 cash add], [Totals 0405]+[Totals 0506] AS
[Cum Total to 0506], [Cum Total to 0506]+[Totals 0607] AS [Cum Total to
0607], [Cum Total to 0607]+[Totals 0708] AS [Cum Total to 0708], [Cashable
Totals 0405]+[Cashable Totals 0506] AS [Cashable to 0506], [Cashable To
0506]+[Cashable Totals 0607] AS [Cashable to 0607], [Cashable To
0607]+[Cashable Totals 0708] AS [Cashable to 0708]
FROM (((((((([Strategies & Actions] RIGHT JOIN [Eff Data] ON [Strategies &
Actions].[Efficiency No] = [Eff Data].[Eff Code]) LEFT JOIN [Efficiency Area]
ON [Eff Data].[Efficiency Area] = [Efficiency Area].Code) LEFT JOIN Owner ON
[Eff Data].Owner = Owner.[Owner Code]) LEFT JOIN [6Mth Totals 0506] ON [Eff
Data].[Eff Code] = [6Mth Totals 0506].[Eff Code]) LEFT JOIN [Original Totals
0506] ON [Eff Data].[Eff Code] = [Original Totals 0506].[Eff Code]) LEFT JOIN
[Data 0405] ON [Eff Data].[Eff Code] = [Data 0405].[Eff Code]) LEFT JOIN
[Data 0506] ON [Eff Data].[Eff Code] = [Data 0506].[Eff Code]) LEFT JOIN
[Data 0607] ON [Eff Data].[Eff Code] = [Data 0607].[Eff Code]) LEFT JOIN
[Data 0708] ON [Eff Data].[Eff Code] = [Data 0708].[Eff Code];

 

[Steve Schapel]

Mike,

This is puzzling, for sure. I will ask around. But first, can I get a
bit more info... First, can you also copy/paste the code you have done
so far. And second, what makes you think the SQL string is truncated?
How and when are you assessing the string returned to the BaseSQL
variable? Sorry this has got difficult, but I haven't struck this type
of problem before.

--
Steve Schapel, Microsoft Access MVP

Steve

Thanks for your help.

The full SQL code for the query used by the report and the partial code that
is being picked up by qdf.SQL is below. It is quite long and, I suspect, a
bit messy. Whenever I was asked to include a new field in the report I added
another query/table into the query being used by the report.

The field that the report is spit on is [Owner Code].

Partial code in qdf.SQL:-
"SELECT [Eff Data].[Eff Code], IIf([Data 0708]![0708 Total to
date]=0,False,True) AS [Include on Report], [Strategies & Actions].Strategy,
[Strategies & Actions].[Key Actions], [Eff Data].Owner, Owner.[test email],
Owner.Service, [Eff Data].[Efficiency A"

Full SQL Code:-
SELECT [Eff Data].[Eff Code], IIf([Data 0708]![0708 Total to
date]=0,False,True) AS [Include on Report], [Strategies & Actions].Strategy,
[Strategies & Actions].[Key Actions], [Eff Data].Owner, Owner.[test email],
Owner.Service, [Eff Data].[Efficiency Area], [Eff Data].[XL Ref], [Eff
Data].[Additional Notes], [Efficiency Area].EffSortNo, [Efficiency
Area].Desc, Owner.[Owner Name], [Data 0405].[Add Cum 0405] AS [Totals 0405],
[Data 0405].[Add Cum Cash 0405] AS [Cashable Totals 0405], [Data 0506].[0506
Total to date] AS [Totals 0506], [Data 0506].[0506 Cash to date] AS [Cashable
Totals 0506], [Data 0607].[0607 Total to date] AS [Totals 0607], [Data
0607].[0607 Cash to date] AS [Cashable Totals 0607], [Data 0708].[0708 Total
to date] AS [Totals 0708], [Data 0708].[0708 Cash to date] AS [Cashable
Totals 0708], [Strategies & Actions].[0506 Mid Year Review], [Strategies &
Actions].Contact, [6Mth Totals 0506].[6mthTotals 0506], [6Mth Totals
0506].[6mthCashables 0506], [Original Totals 0506].[OrigTotals 0506],
[Original Totals 0506].[OrigCashables 0506], [Strategies & Actions].[0506 EOY
Review], [Strategies & Actions].[Notes 1], [Strategies & Actions].[Notes 2],
[Data 0506].[Add Cum 0506], [Data 0506].[Add Cum Cash 0506], [Data
0506].[0506 Add], [Data 0506].[0506 cash add], [Data 0607].[Add Cum 0607],
[Data 0607].[Add Cum Cash 0607], [Data 0607].[0607 Add], [Data 0607].[0607
cash add], [Data 0708].[Add Cum 0708], [Data 0708].[Add Cum Cash 0708], [Data
0708].[0708 Total to date], [Data 0708].[0708 Cash to date], [Data
0708].[0708 Add], [Data 0708].[0708 cash add], [Totals 0405]+[Totals 0506] AS
[Cum Total to 0506], [Cum Total to 0506]+[Totals 0607] AS [Cum Total to
0607], [Cum Total to 0607]+[Totals 0708] AS [Cum Total to 0708], [Cashable
Totals 0405]+[Cashable Totals 0506] AS [Cashable to 0506], [Cashable To
0506]+[Cashable Totals 0607] AS [Cashable to 0607], [Cashable To
0607]+[Cashable Totals 0708] AS [Cashable to 0708]
FROM (((((((([Strategies & Actions] RIGHT JOIN [Eff Data] ON [Strategies &
Actions].[Efficiency No] = [Eff Data].[Eff Code]) LEFT JOIN [Efficiency Area]
ON [Eff Data].[Efficiency Area] = [Efficiency Area].Code) LEFT JOIN Owner ON
[Eff Data].Owner = Owner.[Owner Code]) LEFT JOIN [6Mth Totals 0506] ON [Eff
Data].[Eff Code] = [6Mth Totals 0506].[Eff Code]) LEFT JOIN [Original Totals
0506] ON [Eff Data].[Eff Code] = [Original Totals 0506].[Eff Code]) LEFT JOIN
[Data 0405] ON [Eff Data].[Eff Code] = [Data 0405].[Eff Code]) LEFT JOIN
[Data 0506] ON [Eff Data].[Eff Code] = [Data 0506].[Eff Code]) LEFT JOIN
[Data 0607] ON [Eff Data].[Eff Code] = [Data 0607].[Eff Code]) LEFT JOIN
[Data 0708] ON [Eff Data].[Eff Code] = [Data 0708].[Eff Code];

Mike,

I don't know what the problem here is. This code works fine for me with
long SQL statements. I will investigate. Can you post back with the
full SQL view of the query please? ... Just in case there is something
that gives a clue. Thanks.

 

[MikeS3416]

WSteve

I seem to have this working ok now. I used a much simpler query and report
to test the VBA and then transferred in the correct ones.

I don't think there was a problem with the SQL being truncated, it was just
that Access does not seem to show it all in the "value" field in the Watch
window. I was trying to use this to determine where I was going wrong and
could not help noticing how short the SQL was.

Many thanks for your help.

Mike

Mike,

This is puzzling, for sure. I will ask around. But first, can I get a
bit more info... First, can you also copy/paste the code you have done
so far. And second, what makes you think the SQL string is truncated?
How and when are you assessing the string returned to the BaseSQL
variable? Sorry this has got difficult, but I haven't struck this type
of problem before.

--
Steve Schapel, Microsoft Access MVP

Steve

Thanks for your help.

The full SQL code for the query used by the report and the partial code that
is being picked up by qdf.SQL is below. It is quite long and, I suspect, a
bit messy. Whenever I was asked to include a new field in the report I added
another query/table into the query being used by the report.

The field that the report is spit on is [Owner Code].

Partial code in qdf.SQL:-
"SELECT [Eff Data].[Eff Code], IIf([Data 0708]![0708 Total to
date]=0,False,True) AS [Include on Report], [Strategies & Actions].Strategy,
[Strategies & Actions].[Key Actions], [Eff Data].Owner, Owner.[test email],
Owner.Service, [Eff Data].[Efficiency A"

Full SQL Code:-
SELECT [Eff Data].[Eff Code], IIf([Data 0708]![0708 Total to
date]=0,False,True) AS [Include on Report], [Strategies & Actions].Strategy,
[Strategies & Actions].[Key Actions], [Eff Data].Owner, Owner.[test email],
Owner.Service, [Eff Data].[Efficiency Area], [Eff Data].[XL Ref], [Eff
Data].[Additional Notes], [Efficiency Area].EffSortNo, [Efficiency
Area].Desc, Owner.[Owner Name], [Data 0405].[Add Cum 0405] AS [Totals 0405],
[Data 0405].[Add Cum Cash 0405] AS [Cashable Totals 0405], [Data 0506].[0506
Total to date] AS [Totals 0506], [Data 0506].[0506 Cash to date] AS [Cashable
Totals 0506], [Data 0607].[0607 Total to date] AS [Totals 0607], [Data
0607].[0607 Cash to date] AS [Cashable Totals 0607], [Data 0708].[0708 Total
to date] AS [Totals 0708], [Data 0708].[0708 Cash to date] AS [Cashable
Totals 0708], [Strategies & Actions].[0506 Mid Year Review], [Strategies &
Actions].Contact, [6Mth Totals 0506].[6mthTotals 0506], [6Mth Totals
0506].[6mthCashables 0506], [Original Totals 0506].[OrigTotals 0506],
[Original Totals 0506].[OrigCashables 0506], [Strategies & Actions].[0506 EOY
Review], [Strategies & Actions].[Notes 1], [Strategies & Actions].[Notes 2],
[Data 0506].[Add Cum 0506], [Data 0506].[Add Cum Cash 0506], [Data
0506].[0506 Add], [Data 0506].[0506 cash add], [Data 0607].[Add Cum 0607],
[Data 0607].[Add Cum Cash 0607], [Data 0607].[0607 Add], [Data 0607].[0607
cash add], [Data 0708].[Add Cum 0708], [Data 0708].[Add Cum Cash 0708], [Data
0708].[0708 Total to date], [Data 0708].[0708 Cash to date], [Data
0708].[0708 Add], [Data 0708].[0708 cash add], [Totals 0405]+[Totals 0506] AS
[Cum Total to 0506], [Cum Total to 0506]+[Totals 0607] AS [Cum Total to
0607], [Cum Total to 0607]+[Totals 0708] AS [Cum Total to 0708], [Cashable
Totals 0405]+[Cashable Totals 0506] AS [Cashable to 0506], [Cashable To
0506]+[Cashable Totals 0607] AS [Cashable to 0607], [Cashable To
0607]+[Cashable Totals 0708] AS [Cashable to 0708]
FROM (((((((([Strategies & Actions] RIGHT JOIN [Eff Data] ON [Strategies &
Actions].[Efficiency No] = [Eff Data].[Eff Code]) LEFT JOIN [Efficiency Area]
ON [Eff Data].[Efficiency Area] = [Efficiency Area].Code) LEFT JOIN Owner ON
[Eff Data].Owner = Owner.[Owner Code]) LEFT JOIN [6Mth Totals 0506] ON [Eff
Data].[Eff Code] = [6Mth Totals 0506].[Eff Code]) LEFT JOIN [Original Totals
0506] ON [Eff Data].[Eff Code] = [Original Totals 0506].[Eff Code]) LEFT JOIN
[Data 0405] ON [Eff Data].[Eff Code] = [Data 0405].[Eff Code]) LEFT JOIN
[Data 0506] ON [Eff Data].[Eff Code] = [Data 0506].[Eff Code]) LEFT JOIN
[Data 0607] ON [Eff Data].[Eff Code] = [Data 0607].[Eff Code]) LEFT JOIN
[Data 0708] ON [Eff Data].[Eff Code] = [Data 0708].[Eff Code];

Mike,

I don't know what the problem here is. This code works fine for me with
long SQL statements. I will investigate. Can you post back with the
full SQL view of the query please? ... Just in case there is something
that gives a clue. Thanks.

--
Steve Schapel, Microsoft Access MVP


MikeS3416 wrote:
Thanks for your help. Sorry about the delay in replying - I've been away.

I have a problem with the SQL code. I assume that “qdf.SQL†picks up the SQL
statement behind the query in “dbs.QueryDefs("YourReportQuery")â€. In this
case the SQL statement should be 2689 characters. What is actually coming
through to “qdf.SQL†is only the first 253 characters. This results in
"Run-time errror '3129'. Invalid SQL statement....." when it gets to the line
"qdf.SQL = BaseSQLâ€.

 

[Steve Schapel]

Thanks, Mike. Pleased to hear that you are under way again now.