Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2010 email report pages to separate people

    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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...

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Spharticus,

    See this post for some code you might be able to adapt.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  7. #7
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    --------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •