Need help with code to send emails to recipients selected using check-box

[lantoni]

I have a database we want to use to send emails to selected recipients. I have my code working to the point that it opens an empty Outlook email will ALL recipients in the To field. The command is on a form based on a query with a check-box field. I want to send the email only to the recipients selected... I'm new to VBA and got this far by googling and trying different codes. Please help! Here is what I have so far:

Private Sub cmdEmailSelected_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailsListQry")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs![Email1]) Then


vRecipientList = vRecipientList & rs![Email1] & ";"
rs.MoveNext
Else
rs.MoveNext
End If
Loop Until rs.EOF
vMsg = " Your Message here... "
vSubject = " Your Subject here... "

Dim stDocName As String

If Me.Dirty Then Me.Dirty = False

stDocName = "Empty_Report"
' DoCmd.SendObject acSendNoObject,
DoCmd.SendObject acSendNoObject, stDocName, acFormatXLS, vRecipientList, , , vSubject, vMsg, True
MsgBox ("EMail sent successfully!")
Else
MsgBox "No contacts."
End If

End Sub

[ssanfu]

Maybe add a filter to your query?
You didn't say what the check box field name was, so I used "Selected". Change "Selected" to your actual field name.
And I modified your code a little.....

Private Sub cmdEmailSelected_Click()
    Dim rs As DAO.Recordset
    Dim vRecipientList As String
    Dim vMsg As String
    Dim vSubject As String
    Dim stDocName As String

    If Me.Dirty Then
        Me.Dirty = False
    End If

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailsListQry WHERE Selected = TRUE")
    If rs.BOF And rs.EOF Then      '<<== I changed this also.
        MsgBox "No contacts."
    Else
        rs.MoveFirst
        Do
            If Not IsNull(rs![Email1]) Then
                vRecipientList = vRecipientList & rs![Email1] & ";"
                rs.MoveNext
            Else
                rs.MoveNext
            End If
        Loop Until rs.EOF

        vMsg = " Your Message here... "
        vSubject = " Your Subject here... "

        stDocName = "Empty_Report"
        ' DoCmd.SendObject acSendNoObject,
        DoCmd.SendObject acSendNoObject, stDocName, acFormatXLS, vRecipientList, , , vSubject, vMsg, True
        MsgBox "EMail sent successfully!"
    End If

   rs.Close
   Set rs = Nothing
End Sub

[lantoni]

Steve, It works beautifully!!!!!

I had tried to build the SELECT...FROM.. WHERE... condition but I was getting syntax errors Super thanks dude!!!!

For learning purposes, what is the reasoning for the "If rs.BOF And rs.EOF Then"

(this is how I've been learning this stuff...)

and I have another question: if a recipient has more than one email address (email2), how would that look like in the code?

You know, You've made my day already!!
Thanks,
Leila

[ssanfu]

There are many ways to check for records in a record set (query).

Long ago, I read that if all that matters is if there are records in the record set, check BOF and EOF.
If there are no records in the record set and you try something like "rs.MoveLast", you will get an error. Just the way I do it.

I also do this:

    Dim sSQL As String

    sSQL = "SELECT * FROM EmailsListQry WHERE Selected = TRUE"
    '    Debug.Print sSQL
    Set rs = CurrentDb.OpenRecordset(sSQL)

I can un-comment the debug statement to see if the SQL is properly formed. This is especially useful if the SQL is complicated (many tables).




Also, check the code again. I edited it again - added DAO to the record set declaration and 2 lines at the bottom to close the record set and destroy it.

[lantoni]

Got it.... and got the modifications as well...

Thank you so much for the help!! Have a nice weekend!
Leila :-)

[ssanfu]

You too.

Ready to mark this solved??