Runtime 3701 on .MoveNext

[TJS]

I'm trying to create a Bcc list of 400 people instead of sending 400
individual emails. If I comment out the "DoCmd.SendObject...." I will
get a runtime error 3701 on the ".MoveNext" command. Makes NO sense to
me! Help?



Public Function SendNewsletter()
Dim MyDb As DAO.Database
Dim MySet As DAO.Recordset
Dim email As String

Set MyDb = CurrentDb()
Set MySet = MyDb.OpenRecordset("qryNewsletterEmails", dbOpenDynaset)

With MySet
Do Until .EOF
email = email & MySet! & ";"
' remove the next line causes a runtime error on .MoveNext
DoCmd.SendObject .... etc ' <editted for length>
.MoveNext
Loop
End With

' want to move the DoCmd here

MySet.Close
MyDb.Close
End Function

 

[datAdrenaline]

This is MAY happening because VBA may think you are modifying the field
'email' in your recordset instead of setting the variable email. Also, one
you use the With, you no longer need to reference the recordset object inside
of the with block as you do in the line that sets the email (strEmail)
variable ... (note: I also changed the recordset type to dbOpenSnapshot ..
which is a readonly type and will probably be a little faster)

Try this:
Public Function SendNewsletter()
Dim MyDb As DAO.Database
Dim MySet As DAO.Recordset
Dim strEmail As String

Set MyDb = CurrentDb()
Set MySet = MyDb.OpenRecordset("qryNewsletterEmails", dbOpenSnapshot)

With MySet
Do Until .EOF
strEmail = strEmail & ! & ";"
' remove the next line causes a runtime error on .MoveNext
.MoveNext
Loop
End With

DoCmd.SendObject .... etc ' <editted for length>

End Sub

Hopefully these slight changes will get the code back on track!!!

 

[TJS]

This turned out to be bad data in the query that was causing the
runtime error.