e-mailing to query results 2

[DanIT]

I am trying to send an e-mail to all addresses in a query's result. Query is called qryEMailAddresses; the field with address is email.

I have this code associated with a button. Here is the code:
Private Sub cmdSendEmail_Click()

'constants for sending the e-mail
Dim objOutlook As Object
Dim objOutlookMessage As Object
Dim objOutlookRecip As Object
Dim strOutlookAttach As String 'from form entry
Dim strSubject As String
Dim strBody As String


'constants for creating address list
Dim dbs As Database
Dim rst As Recordset
Dim strString As String
Dim strSQL As String 'this will be all names to send to

'create the string for the address
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(qryEMailAddresses)

rst.MoveFirst

Do Until rst.EOF
With rst
strString = strString & " " &
.MoveNext
End With
Loop

' Send the mail
strSubject = txtSubject.Value
strOutlookAttach = txtFileName.Value
strBody = txtBody.Value
Set objOutlook = CreateObject("Outlook.application&quot
Set objOutlookMessage = objOutlook.createitem(0)
With objOutlookMessage
.bcc = strSQL
.Body = strBody
.subject = strSubject
.attachments.Add strOutlookAttach
.send


End With

Set objOutlookMessage = Nothing
Set objOutlook = Nothing

End Sub
*****************
When I run this, it creates an error at the line "Set rst = dbs.OpenRecordset(qryEMailAddresses)".

Any help would be appreciated.

Dan

 

[KyleS]

The query name needs to be in quotes:"Set rst = dbs.OpenRecordset("qryEMailAddresses")". Kyle :

 

[DanIT]

Thanks, Kyle. I added the quotation marks but still get a "Run time error - Type mismatch" when it runs, and the debugger points to the "set rst=dbs..." line.

I'm trying to concatenate the e-mail addresses and will add a ";" between them.

What am I missing?

Dan

 

[KyleS]

what version of Access are you using? If it's 2K or XP you'll have to set a reference to "Microsoft DAO 3.6 Objects" and Dim your objects ad "DAO.Database" and "DAO.Recordset" etc... Kyle :

 

[DanIT]

Kyle,

Thanks. I'm very close to getting this done, but am stuck on one final point. How do I refer to the values that are returned by the query in this section? The query is called qryEMailAddresses, and the field name is email.

The code doesn't create anything besides a string of ;'s.

**************
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryEMailAddresses&quot
rst.MoveFirst

Do Until rst.EOF
Debug.Print strString
With rst
strString = strString & " ;" & email
.MoveNext
End With
Loop

**************

Thanks,
Dan

 

[cainemart]

Set rst = dbs.OpenRecordset("qryEMailAddresses",dbDynaset)

 

[cmmrfrds]

strString = strString & " ;" & rst!email

 

[DanIT]

Thanks, cmmfrds. That did the trick. I appreciate the help.
Dan