How to select values using a loop

[Raven7738]

Hey,

I'm using Access 2007. I have a code to open a new message in outlook 2007.

Private Sub btnEmail_Click()

Dim ol As Outlook.Application
Dim inbox As Folder
Dim msg As MailItem
Dim rcp As Recipient

Set ol = CreateObject("outlook.application")
Set inbox = ol.Session.GetDefaultFolder(olFolderInbox)
Set msg = ol.CreateItem(olMailItem)

'for each record in the query

Set rcp = msg.Recipients.Add()
rcp.Type = olTo


'next

msg.Subject = ""
msg.Body = ""
msg.Display

End Sub

Im tring to pull values from a query that contains email addresses. I figured I would use a DO...LOOP function to pull those values individually and add them to the "To" field in outlook. The column is called "EmailId" and the query is called "Email". I figure that the code should be in this format:

Do
'function to specify field/value in column of query
Set rcp = msg.Recipients.Add("SelectedValue")
rcp.Type = olTo
Exit Do
'function that moves focus to next field
Loop Until ("FieldValue) is null

How can I go about this?

If anymore information is needed or unclear, let me know

Thank you

[Raven7738]

Hey,

I'm using Access 2007. I have a code to open a new message in outlook 2007.

Private Sub btnEmail_Click()

Dim ol As Outlook.Application
Dim inbox As Folder
Dim msg As MailItem
Dim rcp As Recipient

Set ol = CreateObject("outlook.application")
Set inbox = ol.Session.GetDefaultFolder(olFolderInbox)
Set msg = ol.CreateItem(olMailItem)

'for each record in the query

Set rcp = msg.Recipients.Add()
rcp.Type = olTo


'next

msg.Subject = ""
msg.Body = ""
msg.Display

End Sub

Im tring to pull values from a query that contains email addresses. I figured I would use a DO...LOOP function to pull those values individually and add them to the "To" field in outlook. The column is called "EmailId" and the query is called "Email". I figure that the code should be in this format:

Do
'function to specify field/value in column of query
Set rcp = msg.Recipients.Add("SelectedValue")
rcp.Type = olTo
Exit Do
'function that moves focus to next field
Loop Until ("FieldValue) is null

How can I go about this?

If anymore information is needed or unclear, let me know

Thank you

I have changed my code to this:

Private Sub btnEmail_Click()

Public rst As Recordset
Public qdf As QueryDef
Dim ol As Outlook.Application
Dim inbox As Folder
Dim msg As MailItem
Dim rcp As Recipient
Dim EmlTo As String

Set ol = CreateObject("outlook.application")
Set inbox = ol.Session.GetDefaultFolder(olFolderInbox)
Set msg = ol.CreateItem(olMailItem)

'for each record in the query

Set qdf = CurrnetDb.CreateQueryDef("", "SELECT EmailId FROM [Master] WHERE Email='yes';")
Set rst = qdf.OpenRecordset
If rst.BOF Then
    Exit Sub
Else
rst.MoveFirst
Do Until rst.EOF
    EmlTo = rst![EmailId] & ";"
Set rcp = msg.Recipients.Add(EmlTo)
rcp.Type = olTo
rst.MoveNext
Loop
End If
'next

msg.Subject = ""
msg.Body = ""
msg.Display

End Sub

Now I am getting an error message saying "Invalid attribute in sub or function" Referring to "Public".

Any ideas?

Thank you

[Raven7738]

I spelled Current wrong haha...
But now more roadblocks:

Private Sub btnEmail_Click()

'Declare Values
Dim rst As Recordset
Dim qdf As QueryDef
Dim ol As Outlook.Application
Dim inbox As Folder
Dim msg As MailItem
Dim rcp As Recipient
Dim EmlTo As String

'Create New Mail Item
Set ol = CreateObject("outlook.application")
Set inbox = ol.Session.GetDefaultFolder(olFolderInbox)
Set msg = ol.CreateItem(olMailItem)

'Create query to look up values from
Set qdf = CurrentDb.CreateQueryDef("", "SELECT EmailId FROM [Master] WHERE Email='yes';")
Set rst = qdf.OpenRecordset(,,,) As Recordset

'Take values and put them in the "To" Field of the ne message
If rst.BOF Then
    Exit Sub
Else
    rst.MoveFirst
Do Until rst.EOF
    EmlTo = rst![EmailId] & ";"
    Set rcp = msg.Recipients.Add(EmlTo)
    rcp.Type = olTo
    rst.MoveNext
Loop
End If

'Show/edit message
msg.Subject = ""
msg.Body = ""
msg.Display

End Sub

Now Im getting an error message saying
"Compile Error:
Expected: Expression"

Referring to:
Set rst = qdf.OpenRecords et(,,,) As Recordset

Looking for input.

Thanks again

[NeoPa]

Why do you have "As Recordset" at the end? It's the problem.

[Raven7738]

Why do you have "As Recordset" at the end? It's the problem.

Ive taken it away but I still come up with the same issue

[NeoPa]

It probably doesn't like trailing commas. Commas (,) should only be used to indicate parameter placement. If there are no further parameters the commas are unnecessary and possibly cause a problem.

[Raven7738]

It probably doesn't like trailing commas. Commas (,) should only be used to indicate parameter placement. If there are no further parameters the commas are unnecessary and possibly cause a problem.

If don't put in the commas I get an error saying too few parameters.

Adding parameters (e.g. .OpenRecordset (1,1,3)) Tells me that the database engine could not find '#Temporary QueryDef#'.

What should I do from here?

[NeoPa]

We seem to have two issues here (if I read aright that is).

1. I suspect when you tested it this last time you'd allowed the context to be lost. I read that message as indicating your qdf variable no longer points to the temp QueryDef.
2. As for the parameters, are they even valid ones? Press F1 when the cursor is within the word OpenRecordset to get a full list of valid entries for these parameters. When I looked (Access 2003) they all appeared to be optional though. I can't see why you would be getting anything indicating too few parameters. Confusing.

Let us know how you get on with this.

[Raven7738]

Alright, I figured it out. For those who might find this as a reference, this is my final code:

Private Sub btnEmail_Click()

'Declare Values
Dim rst As DAO.Recordset
Dim ol As Outlook.Application
Dim inbox As Folder
Dim msg As MailItem
Dim rcp As Recipient
Dim Eml As String
Dim EmlTo As String

'Create New Mail Item
Set ol = GetObject("", "outlook.application")
Set inbox = ol.Session.GetDefaultFolder(olFolderInbox)
Set msg = ol.CreateItem(olMailItem)

'Create query to look up values from
Set rst = CurrentDB.OpenRecordset("select * from imd where email=yes", dbOpenDynaset, dbReadOnly)

'Take values and put them in the "To" Field of the ne message
If rst.BOF Then
    Exit Sub
Else
    rst.MoveFirst
    Do Until rst.EOF
        EmlTo = rst![EmailId]
        Set rcp = msg.Recipients.Add(EmlTo)
        rcp.Type = olTo
        rst.MoveNext
    Loop
End If

'Show/edit message
msg.Subject = ""
msg.Body = ""
msg.Display

End Sub

[Raven7738]

We seem to have two issues here (if I read aright that is).

1. I suspect when you tested it this last time you'd allowed the context to be lost. I read that message as indicating your qdf variable no longer points to the temp QueryDef.
2. As for the parameters, are they even valid ones? Press F1 when the cursor is within the word OpenRecordset to get a full list of valid entries for these parameters. When I looked (Access 2003) they all appeared to be optional though. I can't see why you would be getting anything indicating too few parameters. Confusing.

Let us know how you get on with this.

I figured out the issue. In the program I am doing, I created a query that pulls a value from a search form and comes up with a list of results based on that value. The parameter it was searching for was that value put in that control which for some reason its wouldn't pull.

Thank you for your input.

[NeoPa]

Oh I see. A query parameter rather than a parameter for the method (.OpenRecordset ). Thanks for updating, and I'm pleased that you managed to resolve the problem :)