Auto Email Help

[Apprentice]

I've been fighting this code for way too long, can someone help? Trying to
send an email from access and it hangs on this line:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

I have DAO Library referenced and can't get past this line

Private Sub Form_Close()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT EmailAddress FROM %Staff " & "Where StaffMember Like '" &
Me.ActivityLead & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!EmailAddress & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
"A new Activity has been assigned to you in the Review Tracker", True


End Sub

 

[Pendragon]

What is the error message? Have you tried dropping the dbOpenSnapshot and
simply having Set rs = db.OpenRecordset(sql)? All of my code with recordsets
is simply either a coded Select statement or a string variable (like yours)
and then manipulate the data as you have done.

 

[Stuart McCall]

I've been fighting this code for way too long, can someone help? Trying
to
send an email from access and it hangs on this line:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

I have DAO Library referenced and can't get past this line

Private Sub Form_Close()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT EmailAddress FROM %Staff " & "Where StaffMember Like '" &
Me.ActivityLead & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!EmailAddress & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
"A new Activity has been assigned to you in the Review Tracker", True


End Sub

If this:

%Staff

isn't just a typo, then it may be the problem. Try this:

Create a new query
Include the %Staff table
Add all the fields
Close and save the query as qryStaff
Use qryStaff instead of %Staff in your sql

 

[Apprentice]

Thanks to both Pendragon and Mr. McCall, I used both suggestions and its
ticking perfect now. Thanks alot! Here is the completed code for others:

Private Sub Form_Close()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT EmailAddress FROM AutoEmailQuery" & "Where StaffMember Like '"
&
Me.ActivityLead & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)

Do Until rs.EOF
ToVar = ToVar & rs!EmailAddress & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
"A new Activity has been assigned to you in the Review Tracker", True


End Sub