Access 2010 email report pages to separate people

[spharticus]

Greetings. Like the fine folks in these two threads:
http://windowssecrets.com/forums/sho...eparate-people
http://windowssecrets.com/forums/sho...eparate-people!

I have a report that is 512 pages or so. The unique field name on each page is STORE. I have a hierarchy in another table that has each store number and an email address. Based on what I see above I can loop through each record/page, create a one page PDF and send that page to each store email address. Next though I have to 'roll up' the pages based on the higher levels. so 20 stores belong to 00001, then all the stores in 00001, 00002, 00003 are part of 00010, etc.

Assuming that where the code is doing a vlookup kind of action (I'm an excel guy by trade) with the store number to the email, we should be able to do the same thing right? to say that if a store is part of 000001, then group all those pages together, create the pdf file and email to the corresponding address.

Thanks in advance-
Spharticus

[ruirib]

Hi,

Welcome to the Lounge.

If your database has data on the relationships between stores, I don't see why not. I guess you would need to base the report on a query that could retrieve the different stores for each higher level...

[WendellB]

As Rui suggests, if you include your hiearchy table in the data source for the report, and then use the Filter property of the report to create the various groupings based on the specific level chosen, you should be able to accomplish what you need. In fact, you should be able to automate the whole thing if you are using Outlook or another eMail client that exposes it's object model to automation.

[RetiredGeek]

Spharticus,

See this post for some code you might be able to adapt.

[MarkLiquorman]

I would do it somewhat differently. I'd have the recordsource for the report be a query. I'd then modify the query SQL to run it for a specific store, then use the DoCmd.SendObject command to send it via email. Then repeat for each Store.

[spharticus]

so far so good. Thanks to Retired geek, I have the code in that creates individual .pdf files for each page with the naming convention as I need.

Next step is to create reports with multiple pages based on the hierarchy in the other table. Being a beginner i got wrapped up with the outlook and the report creation code all in front of me at the same time....

Thanks for the comments so far...

[spharticus]

As mentioned above, now trying to compile specific pages from a report, pdf, based on a hierarchy.

I have the report grouped by STORE, and this is producing individual pdf files for each store number.

----------------------------
Private Sub Comando9_Click()

Dim rs As DAO.Recordset
Dim DestPath As String
Dim DestFile As String
Set rs = CurrentDb.OpenRecordset("Quick Strike 4 weeks totals")
DestPath = "C:\Documents\PDF\"
Do Until rs.EOF
'stWhere = "[Quick Strike 4 weeks master_Crosstab_STORE]='"
stWhere = "[Quick Strike 4 weeks master_Crosstab_STORE]='" & rs("Store") & "'"
DestFile = "Store" & rs("Store") & ".pdf"
DoCmd.OpenReport "Quick_Strike", acViewPreview, , stWhere, acHidden
DoCmd.OutputTo acOutputReport, "Quick_Strike", acFormatPDF, DestPath & DestFile, ShowPdf, "", 0, acExportQualityPrint
DoCmd.Close acReport, "Quick_Strike"

rs.MoveNext

Loop
rs.Close
Set rs = Nothing
End Sub

End Sub
------------------------------------------

I used the Relationships to bring in the fields I need to reference (District, Region, Division) and even put text boxes in the report itself that references the correct hierarchy for each store number- this is in the query "[Quick Strike 4 weeks master_Crosstab]". What I need out of this is the report to be all STORE pages for each District. I would like it to automatically get the first district number, compile all the store pages, save, then move to the next district number. Right now I can get this code to either make one file with the right name but all 512 pages, or the right name and one page. Not sure what's preventing the script from collating the pages.

I am confused about this: [Quick Strike 4 weeks master_Crosstab_STORE] This table or query doesn't exist. There is a table "Quick Strike 4 weeks master" and a query "Quick Strike 4 weeks master_Crosstab", and STORE is a column heading/value in the table/query but I can't take that out, get an error that the database engine doesn't recognize it as a valid source.

I would appreciate any advice, thanks.

--------------------------------------------
Private Sub Comando10_Click()

Dim rs As DAO.Recordset
Dim DestPath As String
Dim DestFile As String
Set rs = CurrentDb.OpenRecordset("Quick Strike 4 weeks master_Crosstab")
DestPath = "C:\Users\a101241\Documents\PDF\"
Do Until rs.EOF
'stWhere = "[Quick Strike 4 weeks master_Crosstab_STORE]='"
stWhere = "[Quick Strike 4 weeks master_Crosstab_STORE]='" & rs("District") & "'"
DestFile = "District" & rs("District") & ".pdf"
DoCmd.OpenReport "Quick_Strike", acViewPreview, , stWhere, acHidden
DoCmd.OutputTo acOutputReport, "Quick_Strike", acFormatPDF, DestPath & DestFile, ShowPdf, "", 0, acExportQualityPrint
DoCmd.Close acReport, "Quick_Strike"

rs.MoveNext

Loop
rs.Close
Set rs = Nothing
End Sub
--------------------------------------------