Hi carmenv323,
The SQL statement in the query reports all the requested results as you've stated.
However in order to achieve the results you want in a VBA subroutine, you have to do a bit more work.
I'll post the code, but I shortened some of the names in your code to make my work go faster. You'll have to insert your particular names in place instead.
Code:
Dim SQL As String, strReviewers As String, mbox As String 'The mbox string will build what eventually
Dim rstReviewer As DAO.Recordset 'gets output in the Msgbox
Dim i As Integer
SQL = "SELECT EUEmail" & _
" From EUser " & _
" WHERE EUEnabled=True;"
Set rstReviewer = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
strReviewers = rstReviewer.Fields("EUEmail")
rstReviewer.MoveLast 'You have to execute these two statements because the recordset doesn't yet know
rstReviewer.MoveFirst 'how many records it has. And you have to return it to the beginning in order for
'the next loop to function properly.
For i = 1 To rstReviewer.RecordCount 'The previous two statements got us number of records in recordset
If i <> rstReviewer.RecordCount Then 'We set a loop for that number of records
mbox = mbox & rstReviewer.Fields(0) & ", " 'There's only one column in your recordset, its index is 0 (zero)
Else 'We add the comma to delineate the emails
mbox = mbox & rstReviewer.Fields(0) 'But don't add it after the last record
End If
rstReviewer.MoveNext 'After each iteration we have to move to next record in recordset
Next i
MsgBox mbox 'And VOILA! this will print out all the emails.