Error 3421

[jonney]

I'm getting this runtime error every time I run this procedure:
Private Sub btnEmailAttendees_Click()
Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCountSQL As String


Set oApp = New Outlook.Application

strCountSQL = "SELECT Count([TBL INDIVIDUALS].) AS [CountOfEmails] "
strCountSQL = strCountSQL & "FROM [TBL EVENT TYPES] INNER JOIN ([TBL EVENTS] INNER JOIN ([TBL INDIVIDUALS] INNER JOIN [TBL ROLES] ON [TBL INDIVIDUALS].[INDIV ID] = [TBL ROLES].[INDIV ID]) ON [TBL EVENTS].[EVENT ID] = [TBL ROLES].[EVENT ID]) ON [TBL EVENT TYPES].[EVENT TYPE ID] = [TBL EVENTS].[EVENT TYPE ID] "
strCountSQL = strCountSQL & "HAVING ([TBL INDIVIDUALS].[OWN EMAIL FLAG]=True "
strCountSQL = strCountSQL & "AND [TBL ROLES].[ROLE ID] <> 9 "
strCountSQL = strCountSQL & "AND [TBL ROLES].[BOOKING TYPE ID] = 6 "
strCountSQL = strCountSQL & "AND [TBL ROLES].[ROLE STATUS] = True "
strCountSQL = strCountSQL & "AND [TBL ROLES].[EVENT ID] = " & EVENT_ID & " ) "

strSQL = "SELECT [TBL INDIVIDUALS].[EMAIL] "
strSQL = strSQL & "FROM [TBL EVENT TYPES] INNER JOIN ([TBL EVENTS] INNER JOIN ([TBL INDIVIDUALS] INNER JOIN [TBL ROLES] ON [TBL INDIVIDUALS].[INDIV ID] = [TBL ROLES].[INDIV ID]) ON [TBL EVENTS].[EVENT ID] = [TBL ROLES].[EVENT ID]) ON [TBL EVENT TYPES].[EVENT TYPE ID] = [TBL EVENTS].[EVENT TYPE ID] "
strSQL = strSQL & "WHERE ([TBL INDIVIDUALS].[OWN EMAIL FLAG]=True "
strSQL = strSQL & "AND [TBL ROLES].[ROLE ID] <> 9 "
strSQL = strSQL & "AND [TBL ROLES].[BOOKING TYPE ID] = 6 "
strSQL = strSQL & "AND [TBL ROLES].[ROLE STATUS] = True "
strSQL = strSQL & "AND [TBL ROLES].[EVENT ID] = " & EVENT_ID & " ) "
strSQL = strSQL & "ORDER BY [TBL INDIVIDUALS].[EMAIL]"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, EMAIL)



Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
rs.MoveFirst
' All recipients will get the same email
Do While Not rs.EOF
If Len(Trim(rs!EMAIL)) > 0 Then
Set objOutlookRecip = .Recipients.Add(rs!EMAIL)
objOutlookRecip.Type = olBCC
End If
rs.MoveNext
Loop
.Subject = " QUALITY SCOTLAND - E-Mail Attendees"
'.Body =""
.Save
'.Send

End With
End Sub

This seems to be the offending line:

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, EMAIL)

Any help would be very much appreciated.

Jonney

 

[PHV]

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, EMAIL)
what is EMAIL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jonney]

EMAIL is a text field.

Jonney

 

[jonney]

Think I see your point PHV. There is no place for a text field in teh recordset syntax. I took the field out and it seemed to work. Do you agree?

Jonney

 

[PHV]

Does this text field really contain a valid value for the 3rd parameter of the OpenRecordset method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886