Create a Recipient List and add to 'To' line in blank email msg?

[DKM]

Hello. I'm hoping somebody can help me with a project I am working on.

Here is what I'm trying to do in a nutshell.
- From main Switchboard, user selects 'Email Tenants'
- On the next screen user has 2 options...'Email Tenants by Property' (which I'm not concerned about just yet) or 'Email all Tenants'.
- When user clicks the 'Email all Tenants' button, a script runs that *should* search through the Tenants table and create a string of email addresses that will automatically be added to the 'To:' line in a blank email message.

I am very much a beginner when it comes to programming, but I am able to create a blank email message with no problem. Unfortunately it does not add the email addresses to the 'To' line. So I imagine there is something wrong with the 'Do...Loop' in my code. So here is my code, if somebody can see something wrong with it, I'd appreciate any help I could get in correcting it. Thanks.

Private Sub cmdEmail_Click()
Dim rs As Recordset
Dim vRecipientList As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Tenants")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If rs!Email = Not Null Then
vRecipientList = vRecipientList & rs!Email & ";"
rs.MoveNext
Else
rs.MoveNext


End If

Loop Until rs.EOF
DoCmd.SendObject acSendNoObject, , , vRecipientList, , , "Subject", , True

Else
MsgBox "No contacts."
End If

End Sub

[Guus2005]

the solution is that you can't check for nulls using the = sign.
To check for nulls you need IS:

If rs!Email Is Not Null then ...
'or
If rs!Email Not Is Null then ...
'or
If not IsNull(rs!Email) Then ...

What i would do:

Private Sub cmdEmail_Click()
    Dim rs As Recordset
    Dim strRecipientList As String
 
    strRecipientList = ""
    Set rs = CurrentDb.OpenRecordset("SELECT Email FROM Tenants") 'Dont select more than you need.
    Do While not rs.eof
       If Not IsNull(rs.Fields(0)) Then 
           strRecipientList = strRecipientList & rs.Fields(0) & ";" 'Fields(0) is faster than Fields("Email") or rs!Email
       endif
       rs.MoveNext
    Loop
 
    if len(strRecipientList) = 0 then 'checking for length 0 is faster than checking for an empty string
        msgbox "No Contacts"
    else
        DoCmd.SendObject acSendNoObject, , , vRecipientList, , , "Subject", , True
    endif
 
End Sub

Enjoy!

[DKM]

That worked! Awesome. Thank you very much.