Store and send reports by customer number Access 2016

[TrevorJ]

I've looked through the site for this but nothing quite matches. I have an Access report for monthly accounts called rptAccount.
Each report is grouped by Customer code number, a 3 character number string (012) etc..
Is there a quick and easy (ish) way to send these accounts each month. They print separately but send as one big report. That is fine for archive. At the moment I have to go to Print Preview, save each report page as a pdf which requires about 6 clicks, then I have to go back to the archived pdfs and 'send' to email recipient. It's a bit long winded because there are usually 30+. Thanks for any help, macro if possible, not very conversant with VBA.

[TrevorJ]

Yes, that was it thanks. I started off with just the "rptAccount " but put the tblAccount in when it didn't work. Then when you said complete path and filename I didn't try it without the tblAccount part.
At least I have started !

[twinnyfo]

So, we are making progress!!

Let's take another look at what we want our code to do:

• I need a list of Customer Codes
• I need a list of e-mail addresses
• I need to either a) save the reports to PDF first, then send them, or b) send those reports as PDFs (either way would work)

You have the basics of the last step down, although we will have to make some modifications so you can save each customer's report as an individual report that you can then send via-email.

The first two are what we must work on, now. I can only presume that your Customers are in a Table. And hopefully, the Customer ID and the contact e-mail are in the same table? (Please say "yes").

If you've got this customers table, what is your code going to look like to get your Cutomer IDs and E-mail addresses?

I will begin by simply asking how familiar you are with working with Recordsets?--because that is how we are going to approach this solution. Again, this is more of a training session, so we will work through it step-by-step. Other novices will benefit from this process, I believe.

[TrevorJ]

Yes, the tblCustomers does have the email address field. At the moment I know I need a recordset to loop through, to find each report one at a time. I think I could use If Then Else, or While and While End. In English it's 'Start through the list, if the field is not empty, get the address, send the email (referring to the string's declared like subject etc.. When that's done it's return to the start until there is no longer a record (EOF).I will need the error handler that you mentioned before.
At the moment I can print the reports individually because they are grouped by Customer code so I assume I will 'call' the records in the report and customer table by that field. After that I 'm back to research but flushed with success that with your help, I can at least save the whole report!

[twinnyfo]

Yes, you can save the entire report--just make sure gstrReportFilte r = "".

So, your tasks:

• Create a Recordset on the Customers Table
• Using the Customer ID, create a string for the Path\FileName of that Customer's Report
• Set the Report filter based upon the Customer ID
• Save the Report
• Using the e-mail address, send the Report as an attachment

Give it your best shot--even if you have to just go step by step without sending anything to see that each step works individually.

I'm still here to hepp!

[TrevorJ]

This is my offering at the end of today;

Option Compare Database
Option Explicit
 
Private Sub Test_Click()
 
    Dim i As Integer
    Dim db As Database
    Dim rs As Recordset
   ' Dim ccrs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblcustomers")
 
'While Not rs.EOF
For i = 0 To rs.RecordCount - 25
Debug.Print rs.Fields("cuscode")
Debug.Print rs.Fields("email")
rs.MoveNext
Next i
    'DoCmd.OpenReport "rptAccount", acViewReport, acFormatPDF, 'rs.Fields("cuscode")
    'DoCmd.SendObject acSendReport, , acFormatPDF, rs!email, , , "Your monthly account", "Your account is attached", True
    'DoCmd.OutputTo acOutputReport, "rptAccount", acFormatPDF, "C:pdfs\test.pdf", True
 
 
'DoCmd.SendObject acSendReport, , acFormatPDF, rs!email, , , stSubject, stEmailMessage, True, ""
'DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
 
'Wend
'DoCmd.Close acReport, stReport, acSaveNo
 
    rs.Close
    Set rs = Nothing
    db.Close
End Sub
 

I have put some extra bits in that I think I'll need next. I understand up to the line after next i. I commented out the While and While End lines because I got mixed up in an endless loop !
The dim ccrs was to make another recordset for the cuscode field for the report but I don't know if I need it or whether it can all be done with the recordset that I have. What I do have works and gives me the cuscode and email address for each of the customer records so I'm happy so far.
I did move on to the email part but it only attaches the whole report and doesn't fill in or send to the email addresses. Note that I don't have Outlook.
I don't know exactly where to put the error handler.
Regards,
Trevor.

[TrevorJ]

Forgot to ask what the gstr was for, I assume it stands for global string, why global?

[twinnyfo]

Trevor,

Excellent! You are making progress. You are about 99% there. No, really!!

First, ask/answer a few questions:

The dim ccrs was to make another recordset for the cuscode field for the report but I don't know if I need it or whether it can all be done with the recordset that I have.

You have everything you need, as is. You will soon see.

I did move on to the email part but it only attaches the whole report and doesn't fill in or send to the email addresses.

Thisis because you haven't incorporated that Global Variable....

Note that I don't have Outlook.

How will you send e-mail using Access without Outlook? Office is a pretty standard suite of apps--I thought it came standard with an e-mail application.

I don't know exactly where to put the error handler.

An error handler tells the code what to do if it encounters an error of any type. For example, if you are working with mathematical calculations and try to divide a number by 0, that will cause an error. Without error handling, the code just stops (and breaks), which can cause your DB to crash in extreme cases, but it most likely will cause your app to hang or reset all your variables and a massive rift will develop in the intergalactic cosmic continuum vortex matrix (well, maybe not that). It's just bad. However, if you put error handling in all your procedures, if there is an error, you can "catch it" before it breaks anything and then tell the DB what to do. You can catch particular types of errors, too. If you are working with file management and a file is locked or not found, you can choose to skip that file or just exit your procedure.

Typically, error handling is the first line of my procedures, so that if anything goes wrong anywhere along the way, it takes care of it. But you can add different types of error handling at different parts of your procedures (more than you need to know now).

Because I am very lazy and don't like to type code repeatedly, I've created a small procedure (which does not have Error handling, by the way--shame on me!). I place it in an empty module. Then, in the immediate window of my VB editor, I type "ErrorSub" and it gives me the quick text for error handling. I copy and paste into my procedures and I am good to go!

Public Function ErrorSub()
'Just useful and quick way to add Error Handling to Code
 
    Debug.Print "On Error GoTo EH" & vbCrLf
    Debug.Print "    Exit Sub"
    Debug.Print "EH:"
    Debug.Print "    MsgBox " & Chr(34) & _
        "There was an error ....!  " & Chr(34) & " & _"
    Debug.Print "        " & Chr(34) & _
        "Please contact your Database Administrator." & _
        Chr(34) & ", vbCritical, " & Chr(34) & "WARNING!" & Chr(34)
    Debug.Print "    Exit Sub"
 
End Function

Please feel free to steal this code freely.

Forgot to ask what the gstr was for, I assume it stands for global string, why global?

Yes. Global. So you can access it from anywhere in the DB environment.

=============== =============== ======

Now a couple minor critiques (but not necessarily "correction s") to your code.

Line 6: Dim i As Integer - no need for an integer counter. If you are using large recordsets, an integer will break after record 32,767. Additionally, it uses additional resources. You have the basic idea in your While statement (more to follow on that).

Lines 14-17: It "can" be very dangerous to begin working with recordsets without first verifying that there are definitely records in the set. Also, some think using the .EOF property is "good enough", and in 99% of the cases it is. However, there are cases in which it is not quite right. Also, using the .RecordCount property "may" or "may not" give you the number of records in the recordset. Again, for large batches, sometimes the DB will only load a portoin of the records, just to get things started. Then, it requires a .MoveLast, followed by a .RecordCount. However, .RecordCount guarantees that if it is 0, there are no records, anything else means there are at least some records--that becomes your validation.

Then, the best way to run through the records is While Not .EOF. This ensures you get all the records.

So, based on your excellent opening volley, here are some recommended changes, with some notes included for you:

Option Compare Database
Option Explicit
 
'Remember this????
Public gstrReportFilter As String
 
Private Sub Test_Click()
On Error GoTo EH
    Dim db          As Database
    Dim rs          As Recordset
    Dim strPath     As String
    Dim strReport   As String
    Dim strFilename As String
    Dim strSubject  As String
    Dim strMessage  As String
 
    strPath = "C:\pdfs\"
    strSubject = "Your monthly account"
    strMessage = "Your account is attached"
 
    Set db = CurrentDb
    Set RS = db.OpenRecordset("tblcustomers")
    'Save some typing and use "With rs"
    With rs
        'First, check to see if there are records
        If Not .RecordCount = 0 Then
            'Always move to the top!
            .MoveFirst
            Do While Not .EOF
                'This is stuff you already had--I'm not really adding anything
                Debug.Print !cuscode
                Debug.Print !Email!
                'Hey, you've got the cuscode, set the global variable
                'gstrreportfilter = (you fill in the rest.....)
                'Hey, you've got the cuscode, save the report
                'strReport = (you fill in the rest.......)
                strFilename = strPath & strReport
                DoCmd.OutputTo acOutputReport, "rptAccount", _
                    acFormatPDF, strFilename
                'At this point, you've got the PDF
                'You've also got the e-mail address
                'Now just send the report
                DoCmd.SendObject acSendReport, _
                    "rptAccount", acFormatPDF, _
                    !Email, , , strSubject, strMessage, True
                RS.MoveNext
            Loop
        End If
        .Close
    End With
    db.Close
    Set rs = Nothing
    Set db = Nothing
 
    Exit Sub
EH:
    MsgBox "There was an error sending the reports!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Like I said, you are at the 99% point. But, I hope you've learned a few things......

[TrevorJ]

Thank you for your comments and code, I'm working on them now but it seems more like 20% to me !
Just a couple of quickies;
Do I put you EH code into mine under my Private Sub Test_Click() without your Function header and footer or do I put the whole thing above my Private Sub Test_Click() ?
I bought a standalone, no subscription version of Access 2016 so that's all I have, I've read somewhere that Access will use the default email app. which for these experiments is has. If I need to get Outlook, which version please?
Thanks.

[twinnyfo]

Do I put you EH code into mine under my Private Sub Test_Click() without your Function header and footer or do I put the whole thing above my Private Sub Test_Click() ?

See how my code has the error handling? Just like that. Please note the last six lines:

    Exit Sub
EH:
    MsgBox "There was an error sending the reports!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

You want your Code to exit when it gets done, prior to going to EH: Otherwise, your code will "say" you are getting an error, even though there is none.

I bought a standalone, no subscription version of Access 2016 so that's all I have, I've read somewhere that Access will use the default email app. which for these experiments is has. If I need to get Outlook, which version please?

I knew that Access would use your default e-mail--my concern was that you were using a browser version of G-Mail or something. You should be fine.

---------------

Your mission, should you choose to accept it, is to complete lines 34 and 36 above (they'll have to be uncommented in your code, as well). That's it, brother!

Let me know how it works!

[TrevorJ]

I'm sorry, I knew I was further away than you thought. I seem to have tried a million things over 2 days and got nowhere, except that the error message and warning bell work - hooray !
I haven't even started with the email part because there seems little point at this stage.
I think I've tried every combination of rpt account and cuscode possible so I don't now even know if that's what gsttrreportfilt er should be.
So I've left it just to save all the reports under the incrementing customer codes but of course without a filter it saves every report to each cuscode.
I know there might be a wend missing but I've never let it get that far.

Option Compare Database
Option Explicit
 
Public gstrReportFilter As String
 
Private Sub Test_Click()
On Error GoTo trouble
 
    Dim db As Database
    Dim rs As Recordset
    Dim strPath     As String 'Path to store pDFs
    Dim strReport   As String 'Name of report
    Dim strFilename As String 'Name of file to store
    Dim strSubject  As String 'Email subject Your monthly...
    Dim strMessage  As String 'Email message Your account is attached
 
    strPath = "C:\pdfs\"
    strSubject = "Your account"
    strMessage = "Your monthly account is attached"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblcustomers")
 
    With rs
        If Not .RecordCount = 0 Then
            .MoveFirst
            Do While Not .EOF
                Debug.Print !Cuscode
                Debug.Print !Email
                'gstrReportFilter = "rptaccount" & !Cuscode
                Debug.Print gstrReportFilter
                strReport = "Account " & !Cuscode & ".pdf"
                strFilename = strPath & strReport
                DoCmd.OutputTo acOutputReport, "rptAccount", acFormatPDF, strFilename
                rs.MoveNext
            Loop
        End If
        .Close
    End With
    db.Close
    Set rs = Nothing
    Set db = Nothing
 
    Exit Sub
 
trouble:
    MsgBox "There was a problem handling the reports" & ", please contact your Dad", vbCritical, "WARNING!"
    Exit Sub
End Sub

I totally understand if you want to just recommend I go away and study for a year, and then come back.
Regards Trevor.
I thought I knew what frustration was until I tried this.