Looping problem sending e-mail

[JBurlison]

trying to loop thru all the users who are set to receive a report:

    On Error GoTo ErrorRPT
    
    Dim db As Database
    Dim rs As Recordset
    Dim Sendto As String
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("User Name", dbOpenDynaset)
       
    Forms![Infokeeper]![Startdate] = Me.Startdate
    Forms![Infokeeper]![Enddate] = Me.Enddate
    
    If DCount("[Receiving Report]", "User Name", "[Receiving Report] ='" & "Yes" & "'") = 0 Then
    Else
    With rs
        .MoveFirst
        .FindFirst ("[Receiving Report] ='" & "Yes" & "'")
        Sendto = .Fields("Email Address")
        'If DCount("[Receiving Report]", "User Name", "[Receiving Report] ='" & "Yes" & "'") = 0 Then
        'GoTo Ende
        'Else
'Nexte:
        '.FindNext ("[Receiving Report] ='" & "Yes" & "'")
        'Sendto = Sendto & "; " & .Fields("Email Address")
        'If DCount("[Receiving Report]", "User Name", "[Receiving Report] ='" & "Yes" & "'") = 0 Then
        'GoTo Ende
        'Else
        'GoTo Nexte
    'End If
    'End If
    End With
    rs.Close
    End If
'Ende:
    DoCmd.SendObject acSendReport, "Receiving Report", acFormatRTF, Sendto, , , "Receiving Report", "Automatically generated message from SAI Inventory Program"
    DoCmd.Close
    GoTo Endsubtxt

ErrorRPT:
    Call ErrorRPT1

Endsubtxt:
End Sub

I cannot figure out how to make is stop looping after the last person

 

[pbaldy]

I would open the recordset only getting the appropriate records:

"SELECT * FROM [User Name] WHERE [Receiving Report] ='Yes'"

And use this type of loop

  Do While Not rs.EOF

    rs.MoveNext
  Loop

 

[JBurlison]

Humm came up with this but it loops thru ones that say "no"

    On Error GoTo ErrorRPT

    Dim db As Database
    Dim rs As Recordset
    Dim Sendto As String


    Set db = CurrentDb
    Set rs = db.OpenRecordset("User Name", dbOpenDynaset)

    Forms![Infokeeper]![Startdate] = Me.Startdate
    Forms![Infokeeper]![Enddate] = Me.Enddate
    DoCmd.Close
    If DCount("[Receiving Report]", "User Name", "[Receiving Report] ='" & "Yes" & "'") = 0 Then
    Else
        With rs
            .MoveFirst
            Do While Not .EOF
                .FindNext ("[Receiving Report] ='" & "Yes" & "'")
                If Sendto = "" Or IsNull(Sendto) Then
                    Sendto = .Fields("Email Address")
                Else
                    Sendto = Sendto & "; " & .Fields("Email Address")
                End If
                rs.MoveNext
            Loop
        End With
        rs.Close
    End If
    DoCmd.SendObject acSendReport, "Receiving Report", acFormatRTF, Sendto, , , "Receiving Report", "Automatically generated message from SAI Inventory Program"

    GoTo Endsubtxt

ErrorRPT:
    Call ErrorRPT1

Endsubtxt:
End Sub

 

[pbaldy]

Well, you're still opening the recordset directly on the table (or query) rather than the SQL statement I recommended, so that's no surprise. Also, I assume the purpose of the If DCount... statement is to avoid sending the email if there are no matches. With your SendObject outside the If/Then, that won't happen.

 

[JBurlison]

Iv never worked with SQL before how would i implement it here? ps there will be a form that opens up in the if before the else that will allow the user to put a e-mail address in =D

 

[pbaldy]

Try

Set rs = db.OpenRecordset("SELECT * FROM [User Name] WHERE [Receiving Report] ='Yes'", dbOpenDynaset)

and get rid of the FindNext line.

 

[JBurlison]

Very nice A++

 

[pbaldy]

Excellent. By the way, personally I would get rid of this test:

If Sendto = "" Or IsNull(Sendto) Then

and simply use

Sendto = Sendto & .Fields("Email Address") & ";"

because in my experience (Outlook/Exchange) the email system doesn't care about the the trailing semi-colon. Wouldn't be a major performance difference, just throwing it out there. It should work fine as is.

 

[JBurlison]

ahh ic thanks!