MS Access VBA send multiple emails based on table value

[greedcan]

I am trying to create a loop that will go through a table and email a report for each record based on a variable I can semd emails, but no matter what I try I cant seem to limit the report to only the indiviual "shop"'s report.

Function SendToAllState()
On Error GoTo Macro1_Err

' set for loop
Dim rs As DAO.Recordset
Dim db As Database
Dim shop


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

Dim email

Do While Not rs.EOF
Set email = rs!
Set shop = rs![shop name]
If rs![shop name] <> Null Then 'The real loop exit condition.
Exit Do
End If



DoCmd.OpenReport "Revised Allstate PRO Score Card 1", acPreview, , "[shop name]=""" & "shop""", acPreview
DoCmd.SendObject acSendReport, "Revised Allstate PRO Score Card 1", acFormatPDF, email, , , "This Month's report", "See attached report. " & vbNewLine & vbNewLine & "you will require Adobe reader in order to read this report, if you do not have this program go to, [URL unfurl="true"]http://get.adobe.com/reader/[/URL] and down load Adobe reader.", False, ""
DoCmd.Close acReport, "CurrentRecord"





rst.MoveNext
Loop

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

 

[MazeWorX]

tryuntested)

Function SendToAllState()
    On Error GoTo Macro1_Err

    ' set for loop
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strShop As String
    Dim strEmail As String
    Dim strWhere As String

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

    Do While Not rs.EOF
        strEmail = rs![email]
        strShop = rs![shop name]
        strWhere = "[shop name]=" & strShop
        
        If IsNull(rs![shop name]) Then   'The real loop exit condition.
            Exit Do
        End If

        DoCmd.OpenReport "Revised Allstate PRO Score Card 1", acPreview, , strWhere, acPreview
        DoCmd.SendObject acSendReport, "Revised Allstate PRO Score Card 1", acFormatPDF, email, _
            , , "This Month's report", "See attached report.  " & vbNewLine & vbNewLine & _
                "you will require Adobe reader in order to read this report, if you do not have " & _
                "this program go to, [URL unfurl="true"]http://get.adobe.com/reader/[/URL] and down load Adobe reader.", False, ""
        DoCmd.Close acReport, "CurrentRecord"

        rs.MoveNext
    Loop

Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit
Set rs = Nothing
Set db = Nothing
End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work