Problem when processing more than 1 mass Email query 1

[LeeJames75]

This is my code to run an overall mass mail of all emails in a query in BCC.

This works fine.

Public Sub Command4_Click()

Dim otk As Outlook.Application
Dim eml As Outlook.MailItem
Dim rs As Recordset
Dim Strlist As String

' Opens the table/query and builds the distribution list
Set rs = New ADODB.Recordset

With rs
.Open "qry_email", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
.MoveFirst
Set otk = CreateObject("Outlook.Application")
Set eml = otk.CreateItem(olMailItem)

Do While Not .EOF
Strlist = Strlist & .Fields("Email Address").Value & ";"

'This bit is for automatic same report emails -

'eml.Subject = "Test Message"
'eml.Body = "This is another test from access email program."
' use the following to add multiple Attachments
'eml.Attachments.Add "S:\ESC\LJames\invoice.xls"
'eml.Attachments.Add "S:\ESC\LJames\invoice2.xls"

.MoveNext
Loop
.Close
eml.BCC = Strlist

'This will show the email you are sending ideal for manual editing
eml.Display

'This will automatically send the email without showing the email

' eml.Send

End With

End Sub


I then have 2 more queries which relate to 3 groups,
Skaters, BMXers and Blades.

Therefore 3 more buttons and 3 queries.

I have replaced each query name in .OPEN

However, when I hit any of the command buttons for the Skaters or BMXers or Blades.

I get the following error.

RUN TIME ERROR 3021

Either BOF or EOF is TRUE, or the current record has been deleted.
Requested Operation requires a current record.

The overall email query has 10 test records.
So do the other 3 queries.

What have I missed out. Do I need to reset a value ??

All solutions will get a star !!!!

Regards

Lee James
England

 

[SiJP]

You need to reset the recordset.

e.g. for each query, Set rs = New ADODB.Recordset

Otherwise you will still be using the recordset that you first opened for the first query.

------------------------
Hit any User to continue

 

[LeeJames75]

But I am reseting the Recordset.

I have a new sub command for each query.
I just copy the code, change the click number and then
change to the query I am using.

 

[SiJP]

On what line does the code break into?

------------------------
Hit any User to continue

 

[LeeJames75]

I Solved my problem....

I referenced the DAO.RECORDSET
And set rs = CurrentDb.OpenRecordset("qry_email", dbOpenSnapshot)

I also took out the MoveFirst and used While Not .EOF instead of Do While Not .EOF.

The processing time is quicker, and the memory useage is alot less than previous, very useful !!!



Dim otk As Outlook.Application
Dim eml As Outlook.MailItem
'Dim rs As Recordset
Dim rs As DAO.Recordset
Dim Strlist As String

' Opens the table/query and builds the distribution list
'Set rs = New ADODB.Recordset
Set rs = CurrentDb.OpenRecordset("qry_email", dbOpenSnapshot)


With rs
'.Open "qry_email", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
'.MoveFirst


Set otk = CreateObject("Outlook.Application")
Set eml = otk.CreateItem(olMailitem)

'Do While Not .EOF

While Not .EOF
Strlist = Strlist & .Fields("Email Address").Value & ";"

'This bit is for automatic emails -

eml.Subject = "Overall Email"
'eml.Body = "This is another test from access email program."
'eml.Attachments.Add "S:\ESC\LJames\invoice.xls"
'eml.Attachments.Add "S:\ESC\LJames\invoice2.xls"

.MoveNext

'loop

Wend
.Close
eml.BCC = Strlist

'This will show the email you are sending ideal for manual editing
eml.Display
Set otk = Nothing


'This will automatically send the email without showing the email
'<Code> eml.Send <Code>

End With

 

[SiJP]

Cool, glad its sorted

------------------------
Hit any User to continue

 

[LeeJames75]

The First command button accepted fine,
When I hit the 2nd Command button the program kept breaking at .Movefirst

I can now run all 4 command buttons.

Regards
Lee James
England