Create multiple reports 1

[TrekBiker]

I currently have this selector form for customers with unpaid invoices.

This allows a report to be generated based on the selected CustomerID, then emailed to the customer using this, which works a treat.

Private Sub cmdStore_Click()
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem  
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"   
   'Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatement-copy", acFormatPDF, FilePath

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)    
    With oEmailItem
        .To = Me.sfmDebtorList.Form.[E-Mail]
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath

End Sub

I now want to be able to be able to send reports to any customers with ticks in 'Requested Email', all in one operation.

 

[TrekBiker]

Yes, yes, and I do so repeatedly, but just hitting my knowledge wall at times.

BUT after a bit of tidying up I look to have arrived! Four separate emails with correct attachments now appear. Your prompt allowed me to see that Unpaid Items.pdf was indeed appearing in my Documents folder, then being cleared at the end of each loop, then recreated for the next, etc.

The two procedures are now like this, first in response to a button press on the starting form to generate the CustomerIDs of those requesting emails.

Private Sub cmdEmailRequested_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer
Set dbs = CurrentDb
On Error GoTo ErrorHandler
    strSQL = "Select DISTINCT CustomerID From qryDebtorsStatementForEmail2 WHERE (ShipDate BETWEEN #" & [Forms]![frmSales]![Start] & "# AND #" & [Forms]![frmSales]![End] & "#)"
    Set rst = dbs.OpenRecordset(strSQL)
    If rst.EOF Then Exit Sub
    
    For x = 1 To rst.RecordCount
        Call Test(rst!CustomerID)
        rst.MoveNext
    Next x    
    Exit Sub    
ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description   
End Sub

Then this to receive each CustomerID in turn and create an email with their report as attachment.

Private Sub Test(ByRef lngCustID As Long)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

On Error GoTo ErrorHandler
    Set dbs = CurrentDb
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID= " & lngCustID
    Set rst = dbs.OpenRecordset(strSQL)  
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"  'Change this, better to send to the database location folder

'   Create temporary file for pdf
    DoCmd.OpenReport "rptDebtorsStatementForEmail", acViewPreview, "qryDebtorsStatementForEmail2", "CustomerID = " & lngCustID
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatementForEmail", acFormatPDF, FilePath
    DoCmd.Close acReport, "rptDebtorsStatementForEmail", acSaveYes
    
    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If
    Set oEmailItem = oOutlook.CreateItem(olMailItem)
    With oEmailItem
        .To = rst![E-Mail]
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath
    Exit Sub
    
ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   
End Sub

Apologies for taking so much of your time, and several lessons learned! Thank you.

 

[Andrzejek]

WOW! I am impressed (with myself)
My guess of:[tt]
...
DoCmd.OpenReport ...
DoCmd.OutputTo ...
DoCmd.Close ...
... [/tt]
was right on the money!

You don't have to change anything. If it works, leave it.
But, you may do this:

Private Sub cmdEmailRequested_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
[green]'Dim x As Integer[/green]
Set dbs = CurrentDb
On Error GoTo ErrorHandler
    strSQL = "Select DISTINCT CustomerID From qryDebtorsStatementForEmail2 WHERE (ShipDate BETWEEN #" & [Forms]![frmSales]![Start] & "# AND #" & [Forms]![frmSales]![End] & "#)"
    Set rst = dbs.OpenRecordset(strSQL)
    [green]'If rst.EOF Then Exit Sub[/green]
    
    Do While Not rst.EOF
    [green]'For x = 1 To rst.RecordCount[/green]
        Call Test(rst!CustomerID)
        rst.MoveNext
    [green]'Next x  [/green] 
    Loop 
    [green]'Let's be nice and clean rst up[/green]
    rst.Close
    Set rst = Nothing
    Exit Sub    
ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description   
End Sub

Closing and setting recordset to Nothing is not really necessary (it will be gone once out of scope), but shows responsible approach to memory usage by the programmer, IMO. You may do the same in your Sub Test.

And in Sub Test, you may use local Constants for FileName, (FilePath?), and Subject since they do not change (right?)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Splendid thanks and I add my congratulations. Usually standard texts can be used for Subject, and I presume Body Text, but they can always then be manually customised as necessary.

One thing that arises now is that a date range may result in 100+ individual reports. I wouldn't want them all to pile up in the customer's Outlook so will look at a way of stopping after say 5 and waiting for a response to continue with the next batch after the first ones have been sent.

 

[Andrzejek]

stopping after say 5 and waiting for a response to continue

...
    For x = 1 To rst.RecordCount[blue]
        If x Mod 5 = 0 Then
            If vbNo = MsgBox(x & " E-Mails created, " & rst.RecordCount - x & " E-Mails left." & vbNewLine & _
                "Do you want to continue?", _
                vbQuestion + vbYesNo + vbDefaultButton2, "Quit?") Then

                rst.Close
                Set rst = Nothing
                Exit Sub
            End If
        End If
[/blue]
        Call Test(rst!CustomerID)
        rst.MoveNext
    Next x    
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks again, will give it a go!

 

[TrekBiker]

This works a treat, thanks Andy.

As a parting shot it would be good to record all CustomerIDs that have been sent to emails. This is because when date ranges cover 100+ customers it's possible the database operator may not do all in one session, so it would be good to somehow show all done or remaining.

 

[Andrzejek]

The best way would be for an Operator to choose the date range that they can fulfill in one go. You may give them the number in a MsgBox of how many records/Customers will be processed if they stick with their dates selection.

something like:

...
    If rst.EOF Then Exit Sub
        [blue]
    MsgBox "There is/are " & rst.RecordCount & " Customer(s) to process." & vbNewLine & _
           "Do you want to continue?"
 [/blue]
    For x = 1 To rst.RecordCount
...

But...
You may want to have another small table in your DB:
[tt]
tblEMailsProcessed
ID (Autonumber)
UserName
ProcessedDate
CustomerID
... ?
[/tt]
And write to this table every time you create an e-mail with the PDF attachment.

...
    Kill FilePath
[blue]
    strSQL = "Insert Into tblEMailsProcessed (UserName, ProcessedDate, CustomerID) " & vbNewLine & _
             " Values (" & Environ("UserName") & ", #" & Now() & "#, " & lngCustID & ")"
[/blue]
    Exit Sub
    ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Superb, I'd already started a count of how many records there'd be in the selected range, but logging them in a separate table covers all needs. The operators can see how far they have got and resume later.

This is the end of this epic thread, promise, and as before huge thanks for the time you've spent on my ramblings!