Send e-mail to <ALL> 1

[THWatson]

Using Access 2000

Behind a command button is the following code.

The first part "SelectedProcess" works fine. The "SendToAll" process results in error 2465, can't find field.

Private Sub cmdSendMail_Click()
Dim frm As Form, ctl As Control

    Dim ndx As Integer
    Dim varItm As Variant
    Dim strTemp As String
    Dim rs As DAO.Recordset
    Dim strList As String
    Dim strList2 As String
    Dim strSQL As String
   On Error GoTo cmdSendMail_Click_Error

'Check if <ALL> is selected
If Me.lstMembers.Selected(0) = True Then
GoTo SendToAllProcess
Else
GoTo SelectedProcess
End If

'- - - - - - - -
SelectedProcess:
strTemp = ""
Set frm = Forms!frmSelect
Set ctl = frm!lstMembers

For Each varItm In ctl.ItemsSelected
strTemp = strTemp & ctl.Column(3, varItm) & ";"
Next varItm
If strTemp = "" Then
        MsgBox "Please select one or more names.", vbExclamation
        Me.lstMembers.SetFocus
        Exit Sub
        End If


If ctl.ItemsSelected.Count > 1 Then
DoCmd.SendObject , , , "exec@guelphkiwanis.org", , strTemp
Else
DoCmd.SendObject , , , strTemp
End If

GoTo EndProcess

'- - - - - - - -
SendToAllProcess:

strSQL = "SELECT tblMembers.MemberID, tblMembers.FirstName, tblMembers.LastName, tblMembers.email, tblMembers.Status " _
& "FROM tblMembers WHERE (((tblMembers.email) Is Not Null) AND ((tblMembers.Status) Not Like 'Transferred*')) " _
& "UNION SELECT '0','<ALL>','','','' FROM tblMembers " _
& "ORDER BY tblMembers.LastName, tblMembers.FirstName;"

Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
  rs.MoveFirst
  Do While Not rs.EOF
    strList = strList & rs("[email]").Value & ";"
    rs.MoveNext
  Loop
End If

DoCmd.SendObject , , , "exec@guelphkiwanis.org", , strList

rs.Close
Set rs = Nothing

GoTo EndProcess
'- - - - - - - -
   
EndProcess:
For ndx = 0 To Me.lstMembers.ListCount - 1
Me.lstMembers.Selected(ndx) = False
Next
Me.txtSelected = Null
Me.Text19 = Null

   On Error GoTo 0
   Exit Sub


cmdSendMail_Click_Error:
If Err.Number = "2501" Then
Exit Sub
Else

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSendMail_Click of VBA Document Form_frmSelect"
        
End If

End Sub

Can anyone spot what I have incorrect here?

Thanks.

Tom

 

[PWise]

change


strSQL = "SELECT tblMembers.MemberID, tblMembers.FirstName, tblMembers.LastName, tblMembers.email, tblMembers.Status " _
& "FROM tblMembers WHERE (((tblMembers.email) Is Not Null) AND ((tblMembers.Status) Not Like 'Transferred*')) " _
& "UNION SELECT '0','<ALL>','','','' FROM tblMembers " _
& "ORDER BY tblMembers.LastName, tblMembers.FirstName;"

to

strSQL = "SELECT tblMembers.MemberID, tblMembers.FirstName, tblMembers.LastName, tblMembers.email, tblMembers.Status " _
& "FROM tblMembers WHERE (((tblMembers.email) Is Not Null) AND ((tblMembers.Status) Not Like 'Transferred*')) "

 

[THWatson]

pwise
Yep, that works.

I had figured out that the problem was the <ALL> so when the recordset formed <ALL> wasn't really part of it. When I added MoveNext following MoveFirst it worked, but your solution is much better.

Thanks!!

Tom