Auto Email Help

  • Thread starter Thread starter Apprentice
  • Start date Start date
A

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
 
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.
 
Apprentice said:
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
Click to expand...

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
 
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
 
Back
Top