Base recordset on a query

[kaiana]

Hi,

I have some code to run for emailing multiple members. It works beautifully but it is based on a table. I want it to draw the data from a query rather than a table. Can I do that by calling the query or do I have to enter the sql statement? Any help would be greatly appreciated. Here is my code.

Private Sub Email_Click()

On Error Resume Next
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim txtTO, txtSubject, txtMessage As String

Dim clsSendObject As accSendObject
Dim strMsg As String

Set clsSendObject = New accSendObject

txtTO = ""
txtSubject = "Place the subject of the email here"
txtMessage = "Place the message for the email here"
Set db = CurrentDb
Set rs = db.OpenRecordSet("MultipleVouchers", dbOpenDynaset)
rs.MoveFirst
Do
If Not IsNull(rs("Email&quot) Then
txtTO = txtTO & rs("Email&quot & ";"
End If
rs.MoveNext


Loop Until rs.EOF
txtTO = Mid$(txtTO, 1, Len(txtTO) - 1)
clsSendObject.SendObject acSendReport, "MultipleVouchers", accOutputSNP, _
txtTO, , , txtSubject, txtMessage, True

rs.Close
db.Close
Set clsSendObject = Nothing


End Sub

 

[kaiana]

Ok, I have since read that if a query's criteria is based on an unbound form (which mine is) you have to use querydefs. Not quite sure how to do this in my code, have tried a few things with a basic sql statement but it is not seeing the email address (). My full sql (copied from the sql view of the query) is coming up with red lines everywhere. Have had very little experience writing sql statements in code. Here is my sql statement as copied directly from the query:

SELECT Members.MemberID, Members.LastName, Members.FirstName, MexNames.NameID, MexNames.MexName, MaxofCard.MaxOfColourID, CardColours.Colour, [FirstName] & " " & [LastName] AS Member, [MexName] & " " & [LastName] AS MexicanName, Members.Email, Members.Address, Members.Address2, Members.City, Members.State, Members.Postcode
FROM CardColours INNER JOIN ((MexNames INNER JOIN Members ON MexNames.NameID = Members.NameID) INNER JOIN MaxofCard ON Members.MemberID = MaxofCard.MemberID) ON CardColours.ColourID = MaxofCard.MaxOfColourID
WHERE (((MexNames.MexName) Like [Forms]![MultipleVouchers]![Text24] & "*&quot AND ((MaxofCard.MaxOfColourID) Like [Forms]![MultipleVouchers]![Combo18] & "*&quot)
ORDER BY Members.LastName;