Email Addresses - Ignore if null

[jammydodger2]

Ok, so i am using this code (see below) to open up a new email message with all my listed clients e-mail addresses in the bcc field.

Pleblem is some of the clients do not have an email address so it comes up with an error.
How do i get Access to ignore these blank fields, where there are no email addresses entered and use the ones that are entered?

Code:

Private Sub Command12_Click()
 
 
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strEmail As String
 
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
 
    rs.Open "CustomerT", cn
 
    With rs
        Do While Not .EOF
 
            strEmail = strEmail & .Fields("Email") & ";"
            .MoveNext
    Loop
        .Close
    End With
 
 
On Error GoTo Err_Command12_Click
 
    DoCmd.SendObject _
    , _
    , _
    , _
    , _
    , _
    ("" & strEmail), _
    , _
    , _
    True
 
Exit_Command12_Click:
    Exit Sub
 
Err_Command12_Click:
    MsgBox Err.Description
 
    Resume Exit_Command12_Click
 
End Sub

Thanks

[ajalwaysus]

I have one foot out the door, but here is a quick stab, refine your connection to only those who have email addresses.

Code:

rs.Open "SELECT * FROM CustomerT WHERE Email Is Not Null", cn

Hope this works.

-AJ

[jammydodger2]

Thank you so much. Works perfectly.

You are a Hero!
Cheers

[Mmmel]

Just thought I'd chime in with another possible solution. Would it work if you just ran a query from the table with the criteria "Is Not Null" for the email field and then based the code off the query instead? I had a similar problem and solved it this way.

Have a great day!

Mel