Set rs = db.OpenRecordset - Fails

ehorde

Registered User.
Local time
Today, 18:36
Joined
May 21, 2009
Messages
21
Trying to generate an email from email addresses pulled from a query. I have parts of this code working in other areas But his one never makes it past the
line:

Set rs = db.OpenRecordset("qry_EmailAddresses")

No errors, no nothing. I have put message boxes in to see where the code stops, so I know it stops at that line. The query exists and is spelled correctly.

I have seen many example that use these lines of code, why is mine failing?

Thanks in advance...

Here's the full code......

Private Sub Command331_Click()

Dim stmailList As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_EmailAddresses")

stmailList = ""
rs.MoveFirst

Do
stmailList = stmailList & rs.Fields("EmailAdx")
rs.MoveNext
Loop Until rs.EOF

rs.Close
Set rs = Nothing
Set db = Nothing

Dim ol As Outlook.Application
Dim NewMessage As Outlook.MailItem
Dim stmailList As String
Set ol = New Outlook.Application
Set NewMessage = ol.CreateItem(olMailItem)
NewMessage.Subject = stmailList
NewMessage.To = stmailList
NewMessage.Display

End Sub
 
Try:
Code:
Private Sub Command331_Click()

   Dim stmailList As String
   Dim db As [COLOR="Red"]DAO.[/COLOR]Database
   Dim rs As [COLOR="Red"]DAO.[/COLOR]Recordset
   Set db = CurrentDb
   Set rs = db.OpenRecordset("qry_EmailAddresses", [COLOR="Red"]dbOpenDynaset[/COLOR])

   stmailList = ""
   rs.MoveFirst

   Do
      stmailList = stmailList & rs.Fields("EmailAdx")
      rs.MoveNext
   Loop Until rs.EOF

   rs.Close
   Set rs = Nothing
   Set db = Nothing

   Dim ol As Outlook.Application
   Dim NewMessage As Outlook.MailItem
   Dim stmailList As String
   Set ol = New Outlook.Application
   Set NewMessage = ol.CreateItem(olMailItem)
   NewMessage.Subject = stmailList
   NewMessage.To = stmailList
   NewMessage.Display

End Sub
 
Thanks RuralGuy! That got me going.. Glad to know I'm not the only one working late Saturday night ;-)

Here's the working code for the button.
-------------------------------------------
Private Sub Command331_Click()

Dim stEmailList As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_EmailAddresses", dbOpenDynaset)

stEmailList = ""
rs.MoveFirst

Do While Not rs.EOF
stEmailList = stEmailList & rs.Fields("EmailAdx")
rs.MoveNext
Loop

Dim ol As Outlook.Application
Dim NewMessage As Outlook.MailItem
Set ol = New Outlook.Application
Set NewMessage = ol.CreateItem(olMailItem)
NewMessage.Subject = "A message from your friends at " & stCompany
NewMessage.To = stCompany & " Registered Guests"
NewMessage.BCC = stEmailList
NewMessage.Display

rs.Close

Set rs = Nothing
set db = Nothing

End Sub
 

Users who are viewing this thread

Back
Top Bottom