Cycling Through Records in a Table

[Don]

So close yet so far......

I have a make table query that creates s table
called "temptable"

I want to, through code, cycle through the records in that
table and create one large string from one of the field.

This is part of my code (I am still a newbie to VBA). I
set a type mismatch error in this line of code:

Set rstClass = db.OpenRecordset("temptable")

It looks ok to me, unless I can't open tables using the
OpenRecordset command. Can anyone help me?

I feel I am very close. Thanks in advance,
Don


Dim db As Database
Dim rstClass As Recordset
Dim strTo As String

'Declare the needed variables
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim ccRecips As String
Dim strClassName As String, strClassDate As String,
strClassTime As String

Set db = CurrentDb()
Set rstClass = db.OpenRecordset("temptable")

rstClass.MoveFirst
Do Until rstClass.EOF
strTo = strTo & rstClass(StudentID).Value & ";"
rstClass.MoveNext
Loop

MORE CODE...........

'Add the To recipents to the message
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = olTo

 

[Bilal]

if you are reading Emails from the records and want to
send one Email to All, you should do the following

rstClass.MoveFirst
Do Until rstClass.EOF
Set objOutlookRecip = .Recipients.Add(rstClass!
[EmailField])
rstClass.MoveNext
Loop

 

[Richard Killey]

Don

Are you using dao references?

Richard Killey
MS Access VBA articles at www.comeandread.com/access
including an explanation of DAO

So close yet so far......

I have a make table query that creates s table
called "temptable"

I want to, through code, cycle through the records in that
table and create one large string from one of the field.

This is part of my code (I am still a newbie to VBA). I
set a type mismatch error in this line of code:

Set rstClass = db.OpenRecordset("temptable")

It looks ok to me, unless I can't open tables using the
OpenRecordset command. Can anyone help me?

I feel I am very close. Thanks in advance,
Don


Dim db As Database
Dim rstClass As Recordset
Dim strTo As String

'Declare the needed variables
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim ccRecips As String
Dim strClassName As String, strClassDate As String,
strClassTime As String

Set db = CurrentDb()
Set rstClass = db.OpenRecordset("temptable")

rstClass.MoveFirst
Do Until rstClass.EOF
strTo = strTo & rstClass(StudentID).Value & ";"
rstClass.MoveNext
Loop

MORE CODE...........

'Add the To recipents to the message
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = olTo

-------------
Richard Killey
www.comeandread.com/access

latest article on my site shows
how to use multi-select listboxes
as criteria for reports

 

[Don]

I confess I don't know, am I?

I got the code to write to Outlook from Microsoft's site.
That works fantastic. It's cycling through these records
creating the "To" string that is giving me the problem.

My original message shopuld have said... I GET a type
mismatch error (not set).

I went to out VBA guru and he said I should be using ADO

 

[mlindsay]

Open you VBA module in design mode and under Tools-References see what is
checked.

You should have at least:
"Visual Basic for Applications"
"Microsoft Access ??? Object Library"
"Microsoft DAO ??? Object Library"
"Microsoft Outlook ??? Object Library"

The ??? will vary with the version(s) of software you are using. The DAO
module could also be CDO, ADO, or any of several others. The DAO, ADO, CDO
question is more a matter of personal preference and whaich is easier to use
for what you need to do as far as I'm concerned.

Then try the following change:
Dim db As DAO.Database
Dim rstClass As DAO.Recordset

The DAO is optional if it is the only data access object being used but if
you are using more the one at the same time you need to specify. I tend to
specify it anyway out of habit.

MLindsay
(e-mail address removed)
(An old dog still trying
to learn new tricks...)