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

[Bartholomew]

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.

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

[dmaruca]

You're probably running into nulls. Try this.

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

[Bartholomew]

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

Bart