emailing a report 1

[mikeba1]

Hi everyone
Another email question
Currently I print a payment report showing what is to paid to individual suppliers.
I would like to send each individual supplier their part of the report.
I'm at a loss as to how to do this.
Thanks in advance

 

[Remou]

I would suggest basing your report on a query which can be update through code. Very roughly:

Set qdf=CurrentDB.QueryDefs("qryReport")
Set rs=CurrentDB.OpenRecorset("tblSuppliers")
strSQL=qdf.SQL
Do While Not rs.EOF
   strSQLSup = strSQL & " WHERE SupplierID=" & rs!SupplierID
   qdf.SQL=strSQLSup
   DoCmd.SendObject acSendReport, "rptSupplier", acFormatRTF, rs!Email
   rs.MoveNext
Loop
qdf.SQL=strSQL

 

[mikeba1]

Hi Remou
I'm having a senior moment
Can you explain the logic of your suggestion.

 

[Remou]

Here is what I visualized.

'Get the query that the report is based on
Set qdf=CurrentDB.QueryDefs("qryReport")
'Open the suppliers recordset
Set rs=CurrentDB.OpenRecorset("tblSuppliers")
'Get the sql of the query the report is based on
'this is a sketch, so I have assumed that the SQL is
'does not have a Where statement. More on this in a 
'line or two
strSQL=qdf.SQL
'Loop through the suppliers recordset
Do While Not rs.EOF
'Add a Where statement to the report SQL so
'only the current supplier from the recordset
'is included
   strSQLSup = strSQL & " WHERE SupplierID=" & rs!SupplierID
'Set the report query SQL to the altered sql statement
   qdf.SQL=strSQLSup
'Email the report, which now only includes the one
'supplier to the email from the supplier recordset
   DoCmd.SendObject acSendReport, "rptSupplier", acFormatRTF, rs!Email
'And so to the next supplier
   rs.MoveNext
Loop
'Set the query back to the usual SQL
qdf.SQL=strSQL

 

[mikeba1]

Thanks Remou
Where would I put this code.
Currently the report is based on a query, the report is run from a button click.
i.e

If [IncludeNotPrinted] = True Then
DoCmd.OpenReport "rptRemittanceAdvice2", acViewPreview
Else
DoCmd.OpenReport "rptRemittanceAdvice1", acViewPreview
End If

 

[Remou]

Where you think would suit best. It is a sketch, it needs to be modified to suit your application.

 

[mikeba1]

OK thanks Remou

 

[mikeba1]

Hi
I thought I had cracked my problem.
I put this bit of code in the detail section of my original report if the supplier has an email.

The docmd sendobject runs a different report with its control source set to the form 'test5' payno which will be for one supplier.

all works fine in preview mode, i get all the emails i expect into outlook.
however in print mode it does not work,it seems to ignore the docmd sendobject
AnyIdeas

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If ConcEmail > "" Then
Forms![test5]![payno] = PLBNo
Forms![test5]![mailadd] = ConcEmail
On Error Resume Next
DoCmd.SendObject acSendReport, "rptremittanceadvice1email", acFormatRTF, ConcEmail
End If
End Sub

 

[Remou]

I think the best bet would be to run the emails in the buton click event, just after the main form. It is rarely a good idea to do anything fancy in a report module.

 

[mikeba1]

Thanks Remou
I will try another approach,this way seemed to good to be true.

 

[Remou]

Isn't that always the way?