SQL Problems!

I have a database with over 5000 contacts and I wisht to add a checkbox field which employees can check and uncheck as they please to determine who will be the recipients of a single, mass email. Sounds simple enough and one would assume the sql qould go something along the lines of:

Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

And yet this doesn't seem to be working!

Furthermore, I tried a code supplied by someone else except "Error 3265: item not found in this collection" keeps popping up, highlighting the If Not IsNull line and the following two lines. Here is my code thus far:

Private Sub Send_to_Selected_Click()

Dim r As Recordset
Dim Email As String
Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

Do While Not r.EOF
If Not IsNull(r("Email")) _
And r("Email") <> "" _
And r("SendEmail") <> True Then
Email = Email & r("Email") & ";"
End If
r.MoveNext
Loop


End Sub

Where am I going wrong??

Never mind ... I'm a total idiot ... forgot my DoCmd.SendObject line

Work's now (also with a few slight adjustments on above code)

Post what you came up with. I see a few errors, plus a few hints

(and take a look at the link in my signature)

Dim r As Recordset
Dim Email As String
Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

Do While Not r.EOF
Email = Email & r("Email") & ";"
r.MoveNext
Loop
r.Close

DoCmd.SendObject acSendNoObject, Null, Null, "", "", Email, "", "", True, Null

End Sub


Out of curiousity, how can I make it so each email is personalised with "Hi (FirstName)!" ... I Managed to make each personalised, but it's personalised with the FirstName of the first contact only ... So each said Hi Kate! even tho it was being sent to John, Jack and Jill ... It's not a necessity but I thought it would be nice so if you have any tips, you know where I am!

It changes them, but you only see the LAST value.

Output the name INSIDE of the FOR/LOOP rather than at the end

well done. i think that it is going to work now.