[RESOLVED] [ACCESS 2007] Anyone see anything obiously wrong with this code?

I have a button that runs a makequery in my db. when the query runs, the table it makes is populated with e-mail addresses and empty cells for people without e-mail addresses entered.

The problem I am having is that when I try to extract only the e-mail addresses, I end up getting spaces which keep my do command from working.

What I have tried to do is skip these cells in the open recordset. That woks for the first empty cell but wont work with the following emptys. I'm new and self taught so I am sure it is something simple however, after 8 hours of writing and revising this, I am at a loss.

Any help with this would be greatly appreciated.

The output I get is as follows (DATA MASKED FOR SECURITY :) ):

[email protected];;;;[email protected]@XXXXX.AF.MIL .... etc.

Code:

---

Private Sub btnEMailSecChfs_Click()
DoCmd.OpenQuery ("qry SECTION CHIEFS")

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim objSubject As String
Dim objAllRecip As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl SECTION CHIEFS")

On Error Resume Next

rs.MoveFirst
objSubject = rs("HookName") & "_" & "Information/Message:"

Do Until rs.EOF
 
  If objAllRecip = "" Then
    If rs![WORK E-MAIL ADDRESS] = "" Then
    rs.MoveNext
    Else
    rs.MoveNext
    objAllRecip = rs![WORK E-MAIL ADDRESS] & ";"
    End If
  Else
    If rs![WORK E-MAIL ADDRESS] = "" Then
    rs.MoveNext
    Else
    rs.MoveNext
    objAllRecip = objAllRecip & ";" & rs![WORK E-MAIL ADDRESS]
    End If
  End If

Loop

Debug.Print (objAllRecip & "3")

DoCmd.SendObject -1, , , objAllRecip, "[email protected]", , objSubject, , -1

rs.Close

End Sub

---

You're probably running into nulls. Try this.

Code:

---

Private Sub btnEMailSecChfs_Click()
DoCmd.OpenQuery ("qry SECTION CHIEFS")

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim objSubject As String
Dim objAllRecip As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl SECTION CHIEFS")

On Error Resume Next

rs.MoveFirst
objSubject = rs("HookName") & "_" & "Information/Message:"

Do Until rs.EOF
 
        If Not IsNull(rs![WORK E-MAIL ADDRESS]) And Trim(rs![WORK E-MAIL ADDRESS]) <> "" Then
                If Len(objAllRecip) > 0 Then objAllRecip = objAllRecip & ";"
                objAllRecip = objAllRecip & rs![WORK E-MAIL ADDRESS]
        End If
       
        rs.MoveNext
Loop

Debug.Print (objAllRecip & "3")

DoCmd.SendObject -1, , , objAllRecip, "[email protected]", , objSubject, , -1

rs.Close

End Sub

---

It will take me some time to decipher what you did. However, it worked flawlessly... Thank you so much.

Bart