Compile error on OpenQueryDef

[Raymond]

Why do I get a compile error on OpenQuerydef?
It saids "Function or interface marked as restricted, or the function uses
an automation type not supported in Visual Basic".

Thanks,

Raymond

 

[pietlinden]

Why do I get a compile error on OpenQuerydef?
It saids "Function or interface marked as restricted, or the function uses
an automation type not supported in Visual Basic".

Thanks,

Raymond

If you open a code module and go to Tools, References, is DAO 3.X
listed? IF not, it needs to be.

 

[Raymond]

It is not listed or checked off. Where can I get it from?

Thanks

 

[fredg]

It is not listed or checked off. Where can I get it from?

Thanks

The full name to look for is
Microsoft DAO 3.6 Object Library
(or the highest number you have in your list.)

Place a check mark in it's check box and exit.

Then, when declaring objects, use the syntax of:
Dim qdf as DAO.QueryDef

 

[George Nicholson]

1) Per the DAO Help file, Database.OpenQueryDef is an obsolete method. Use
the Querydefs collection instead.

2) If the OP is using Access 2007 and working in an Access 2007 format file,
they will not have a separate DAO reference listed. The "Access 12 Object
Library" includes DAO.

HTH,

 

[Raymond]

Thank you for your help. Here is my code but it keeps getting stuck on
(rsRecip.UserID("EMail")) part of the code specfically the userID which is
the field that contains the email address in the query. Can anyone help.


Sub SendMessage()
Dim qdf As DAO.QueryDefs
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim clsSendObject As accSendObject

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

' Open the recordset so you can loop through it.
Set rsRecip = DBEngine(0)(0).QueryDefs("Current Case Query Within
15 Days")

Do Until rsRecip.EOF

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rsRecip.UserID("EMail"))
objOutlookRecip.Type = olTo



' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft"
Outlook ""
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

1) Per the DAO Help file, Database.OpenQueryDef is an obsolete method. Use
the Querydefs collection instead.

2) If the OP is using Access 2007 and working in an Access 2007 format file,
they will not have a separate DAO reference listed. The "Access 12 Object
Library" includes DAO.

HTH,

 

[George Nicholson]

1)

Set rsRecip = DBEngine(0)(0).QueryDefs("Current Case Query Within 15 Days")

A QueryDef is the definition of a query, not its results (i.e., a
Recordset).

Since rsRecip is a Recordset, I would think you want something more like:
(additions)
Dim db as DAO.Database
Set db = CurrentDB
(Change)
Set rsRecip = db.OpenRecordset("Current Case Query Within 15 Days")

2) I think your loop is out of sequence, and missing the actual Loop
statement.
Its been a while since I've worked with Outlook objects, so I'm not exactly
sure what you are trying to do.
a) Send each recipient a separate email
b) Send one message with multiple recipients. Since CreateItem is outside
the Loop, I'm assuming you are doing this.
I think you want something structured like the following aircode.

With objOutlookMsg
' Since you only need to set Subject, Body, and Importance of the
message ONCE, do it here
' (your code)

Set rsRecip = db.OpenRecordset("Current Case Query Within 15 Days")
Do Until rsRecip.EOF
' Add recipient
' (your code)
rsRecip.MoveNext
Loop

?Check to make sure there is at least one recipient before you resolve
& send?
' Resolve all recipients
' (your code)
.Send
End With


HTH,

Thank you for your help. Here is my code but it keeps getting stuck on
(rsRecip.UserID("EMail")) part of the code specfically the userID which is
the field that contains the email address in the query. Can anyone help.


Sub SendMessage()
Dim qdf As DAO.QueryDefs
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim clsSendObject As accSendObject

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

' Open the recordset so you can loop through it.
Set rsRecip = DBEngine(0)(0).QueryDefs("Current Case Query Within
15 Days")

Do Until rsRecip.EOF

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rsRecip.UserID("EMail"))
objOutlookRecip.Type = olTo



' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft"
Outlook ""
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[Raymond]

George,

Thank you for your reply, here is my code now but it still does not work. It
does not compile. It hangs on the UserID field which is the field from the
query that contains the email address. What is the correct sytax to pull the
email address from a query to place it in the TO: field?

Thanks,
Raymond

Sub SendMessage()
Dim qdf As DAO.QueryDefs
Dim db As DAO.Database
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim clsSendObject As accSendObject

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
Set db = CurrentDb

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

' Open the recordset so you can loop through it.
Set rsRecip = db.OpenRecordset("Current_Case_Query_Within_15_Days")

Do Until rsRecip.EOF
' Add recipient
' (your code)
Set objOutlookRecip = .Recipients.Add(rsRecip!UserID("EMail"))
objOutlookRecip.Type = olTo
rsRecip.MoveNext
Loop


With objOutlookMsg
' Add the To recipient(s) to the message.


' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft"
Outlook ""
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[George Nicholson]

1)

Set objOutlookRecip = .Recipients.Add(rsRecip!UserID("EMail"))

The leading dot in ".Recipients.Add" assumes its within the "With
objOutlookMsg... End With" block and it isn't. Either move the "With.."
start or change the above line to
"objOutlookMsg.Recipients.Add"

2)

rsRecip!UserID("EMail"))

rsRecip!UserID should return the value of the UserID field from the current
record of the recordset.
I don't think rsRecip!UserID("EMail") is correct syntax and it won't compile
or return anything, afaik.

3)

Next
.Send

What is that "Next" for? Pretty sure it serves no purpose, is improper
syntax & won't compile. Comment it out.

4)

What is the correct sytax to pull the
email address from a query to place it in the TO: field?

No idea, sorry. Been too long since I've used the Outlook object model. But
what you have may work if the other problems are addressed.

5)

It does not compile.

Stating what the error message is can be a clue and help us help you. In
this case you probably got a "Object not set" error (calling attention to
the missing objOutlookMsg reference). However, there were multiple problems
on that line, so I'm not entirely sure what the compiler would have choked
on first.


HTH,