the solution is that you can't check for nulls using the = sign.
To check for nulls you need IS:
Code:
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:
Code:
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!