ignoring null value in recordset

[nickblitz]

i have this code here to send out email... it works fine but it does not skip through the blank spaces in the recordset.

can anyone help me?
how do i incorporate the find null thing into this??

Private Sub Command35_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset


Dim strEmailAddress


Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Query Full Director", dbOpenSnapshot)


Do Until rsEmail.EOF
strEmailAddress = strEmailAddress & rsEmail("Secondary Email") & ";"
rsEmail.MoveNext
Loop


strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)


DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, True, False


rsEmail.Close
Set rst = Nothing


End Sub

[June7]

What do you mean by 'blank spaces' - there is no email address in the record?

Options:

1. Filter in query to exclude records that don't have email

2. Code
If Not IsNull(rsEmail("Secondary Email")) Then
'send email
...
End If

[nickblitz]

What do you mean by 'blank spaces' - there is no email address in the record?

Options:

1. Filter in query to exclude records that don't have email

2. Code
If Not IsNull(rsEmail("Secondary Email")) Then
'send email
...
End If

Hi June,

thanks for your reply.
i added the code you posted above in. it does not have errors but when i clicked the button, nothing happened. no pop ups from outlook at all.

yeah there are blank records in my table.

i would much prefer a code that does that.

[June7]

Post your complete procedure or provide db for analysis. Follow instructions at bottom of my post.

[nickblitz]

Post your complete procedure or provide db for analysis. Follow instructions at bottom of my post.

that was my complete code.. ??

included with attachment.

theres a query full director form.

theres a command button that selects the secondary email and opens up outlook.

[nickblitz]

attachment

[June7]

I looked at the code behind each form and none show the If Then code I suggested.

Why have 7 identical forms for emailing? Have one form that can be filtered to show the desired records.

[nickblitz]

I looked at the code behind each form and none show the If Then code I suggested.

Why have 7 identical forms for emailing? Have one form that can be filtered to show the desired records.

i didnt add it in because it does not work.. can you help to see where i insert those codes into mine?

[nickblitz]

how is it? btw the queries are there search for specific records more easily to generate emails for different groups.

[June7]

Next time, provide the code you attempted, even if it is failing. For some reason the 'blank' Secondary Email field has a space when the code reads it so IsNull didn't work.

Do Until rsEmail.EOF
    If Len(rsEmail("Secondary Email") & "") > 1 Then
        strEmailAddress = strEmailAddress & rsEmail("Secondary Email") & ";"
    End If
    rsEmail.MoveNext
Loop

[nickblitz]

Next time, provide the code you attempted, even if it is failing. For some reason the 'blank' Secondary Email field has a space when the code reads it so IsNull didn't work.

Do Until rsEmail.EOF
    If Len(rsEmail("Secondary Email") & "") > 1 Then
        strEmailAddress = strEmailAddress & rsEmail("Secondary Email") & ";"
    End If
    rsEmail.MoveNext
Loop

thanks!!! it works! btw i was refering to the code you posted above. not my own codes.