Results 1 to 2 of 2
  1. #1
    8RAND0N is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    1

    Exclamation Split Report by Individual Record (.PDF format) and E-Mai Individual PDF to Specific User(s)

    Hi all,

    I hope I'm following the right protocol here -- first time posting. I'm in a bit of a pinch with a project. I'm getting immersed in Access and related programming languages (SQL, VBA, etc), and I think I have a VBA scenario on my hands.

    I have several Access reports, each one with anywhere from 1 to 80 records. I'd like to ...

    1. Split each Report by individual record and save it as a name unique to the record. So in a report that has 60 records, the script would split a single report with 60 records into 60 PDF reports each with a unique record.
    2. I would like the file names to be unique to the record. In this case, the record is a person ... so a file format that follows a LastnameFirstnameMonthYear (SmithJohnFebruary 2017) format would be super.
    3. Once split, I would like these individual PDFs emailed to the individual from which the individual report is based with a standard message body.


    So when it's done, the script has either gone through each report I have and split it up by individual record, emailing these records to individual users with an email body template. Or it could be a script that just applies to one report so I'd have to run it per report to do what I'm trying to do. I'm just hoping this entire process can be automated.

    The table from which these reports are run includes the email addresses associated with each record/individual.



    I hope this is enough of an explanation. I'm "learning by doing" and in this particular case I'm not sure the right way to do it ... if you need any additional detail, please let me know!

    Thank you and cheers!

    Brandon

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    a listbox, lstUser, has the list of all users to email to,
    A listbox , lstRpt ,has the reports to pick to print...
    the query in the report, looks at the lstUsr to only pull data for that person.

    Code:
    sub btnRun_click()
    
    vDir = "c:\folder\"
    
    For i = 0 To lstUser.ListCount - 1
       vUsr = lstUser.ItemData(i)   'get next user in list
       lstUser = vUsr        'set the list
    
       vTo = lstUser.Column(2)   'get the email from the list
       vBody = "body of email"
       vSubj = vRpt
    
    
         vFileName = vUsr & "_" & lstRpt & format(date,"yymmdd-hhnn") & ".pdf"
         docmd.OutputTo  acOutputReport ,lstRpt ,acFormatPDF, vFilename
    
            'then send the email
         DoCmd.SendObject acSendReport, lstRpt , acFormatPDF, vTo, , , vSubj, vBody   
    Next
    end sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-29-2014, 12:02 PM
  2. Replies: 4
    Last Post: 04-23-2014, 06:30 PM
  3. Replies: 1
    Last Post: 08-28-2013, 02:27 AM
  4. Replies: 6
    Last Post: 08-07-2012, 02:44 PM
  5. Replies: 3
    Last Post: 01-25-2011, 09:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums