«DoCmd.SendObject acSendReport» sends too many attached files...

[Newman]

I have a module that, every nights, opens a report and sends it by e-mail to the person the records belong to in a snapshot format. (It's code follows this message...)

It's been working fine for a couple of months, but started to act crazy last weekend.

Sometimes, it sends more snp files than it should. The customer receives the snp file that belongs to him and snp files that belong to someone else but from a different night.

ie: Last night, Mr.X got his snp file with his records from yesterday. That's fine!
But he also got snp files with records of last week from Mr.Y. That's not fine at all!
Do any of you have an idea where the problem comes from?

Many thanks.

Public Sub EnvoyerThermoRealisee()
    Dim DB As Connection
    Dim rsThermoRealisee As New ADODB.Recordset
    
    Set DB = CurrentProject.Connection
    With rsThermoRealisee

        .Open "SELECT DISTINCT AdresseMessagerie FROM RThermoRealisee", DB, adopenstatic, adlockoptimistic        
        If .RecordCount = 0 Then Exit Sub
        .MoveFirst
        Do Until .EOF
        
            strAdresse = rsThermoRealisee.Fields(0)
            DoCmd.OpenReport "EThermoRealisee", acViewDesign
            Report_EThermoNonAutorise.FilterOn = False
            Report_EThermoRealisee.Filter = "[AdresseMessagerie]='" & strAdresse & "'"
            Report_EThermoNonAutorise.FilterOn = True
            DoCmd.SendObject acSendReport, "EThermoRealisee", acFormatSNP, strAdresse, , , "Thermo réalisée", "Veuillez trouver ci-joint la liste des thermo réalisées à votre demande.", False
            DoCmd.Close acReport, "EThermoRealisee", acSaveNo
            .MoveNext
          
        Loop
        .Close
        DB.Close

    End With
End Sub

 

[WayneRyan]

Newman,

.Open "SELECT DISTINCT AdresseMessagerie FROM RThermoRealisee"

There's no Where Clause! Won't that send an email to everyone?

Wayne

 

[Newman]

As far as I know, there is no way to put a «Where» clause in a «sendobject».

The query behind the report already have the criteria: Date()-1, so that only the persons who made records on the previous day will come in the recordset.

I added some comment lines to my code:

Public Sub EnvoyerThermoRealisee()
    Dim DB As Connection
    Dim rsThermoRealisee As New ADODB.Recordset
    
    Set DB = CurrentProject.Connection
    With rsThermoRealisee
'Selects all the persons who need to receive a mail.
        .Open "SELECT DISTINCT AdresseMessagerie FROM RThermoRealisee", DB, adopenstatic, adlockoptimistic        
        If .RecordCount = 0 Then Exit Sub
'Moves to the first person I have to send mails to.
        .MoveFirst
        Do Until .EOF
        
            strAdresse = rsThermoRealisee.Fields(0)
            DoCmd.OpenReport "EThermoRealisee", acViewDesign
            Report_EThermoNonAutorise.FilterOn = False
'Puts a filter to the report to get only the records of the current person.
            Report_EThermoRealisee.Filter = "[AdresseMessagerie]='" & strAdresse & "'"
            Report_EThermoNonAutorise.FilterOn = True
'Sends an e-mail to the current person
            DoCmd.SendObject acSendReport, "EThermoRealisee", acFormatSNP, strAdresse, , , "Thermo réalisée", "Veuillez trouver ci-joint la liste des thermo réalisées à votre demande.", False
            DoCmd.Close acReport, "EThermoRealisee", acSaveNo
'Moves to the next persons to whom I have to send an e-mail.
            .MoveNext
          
        Loop
        .Close
        DB.Close

    End With
End Sub

 

[WayneRyan]

Newman,

Just guessing here ...

'
' This line gets the value for each employee.
'
' I think things might have changed, because the table
' was altered.
'
' I'd change its reference to:
'
' rsThermoRealisee.Fields("[AdresseMessagerie]") or
' rsThermoRealisee![AdresseMessagerie]
'
strAdresse = rsThermoRealisee.Fields(0) or

'
' This is the "Where" part, the above change might make
' it more accurate.
'
Report_EThermoRealisee.Filter = "[AdresseMessagerie]='" & strAdresse & "'"


Wayne