Sending Query with sendObject command

[William K]

Hi all,

Working with Access 2003. Am trying to write a little sub that will send a
query as an excel attachment on an email. I done this before, but never with
a query. The query by itself runs without error, but when I try to run the
below code I get the error "Query must have at least one dstination field."
What does that mean and how do I fix it? Thanks much in advance for your
help.

Sub send_query()
DoCmd.SendObject acSendQuery, "store_report", acFormatXLS, _
"(e-mail address removed)", "(e-mail address removed)", , _
"Store Reports", "Here are your reports.", True
End Sub

 

[DomThePom]

Hi Willian

Does the query run OK if you open it in the database window?

You normally get this message when you (inedverantly) remove all the X's
from the show text boxes in teh query design view

 

[William K]

Hi Dom,

Well I discovered that if I run the below sub and it is canceled or errors
out that my query is basically erased. It still shows as an object, but if
you open it all the guts are gone. Wierd. I can recreate the query but if I
run the sub again and have an error the query is erased again. Any Idea how
to prevent that? Thanks.

 

[DomThePom]

That is strange - just running a select query should not result in a change
to the SQL behind it!

What kind of errors are you getting prior to the SQL being messed with?

 

[William K]

Wow, when I close out the email withouut sending it the query guts dissapear.
I also discovered that even if I do send the email my query still
disappears. So this command:

DoCmd.SendObject acSendQuery, "store_report", acFormatXLS, _
"(e-mail address removed)", "(e-mail address removed)", , _
"Store Reports", "Here are your reports.", True

seems to be literally sending my query as an email attachemnt and leaving
nothing behind. Is something wrong with the command?

 

[William K]

Update. I also discovered If I right click on the query and choose the
option to export it to an excel file that will cause the query guts to
dissapear as well. Sometimes it does work correctly though, other times the
sql behind the query is simply erased.

 

[Lisa K]

Have you been able to resolve this problem? I have a procedure that does the
same thing. The email is submitted and then the SQL in my query is gone.

 

[Stuart McCall]

Have you been able to resolve this problem? I have a procedure that does
the
same thing. The email is submitted and then the SQL in my query is gone.

<SNIP>

I don't have an explanation, just a workaround to at least get you going.
Save the current SQL, do your emailing thing, then put the SQL back.
Something like:

Dim qdf As DAO.QueryDef
Dim savedSQL As String

Set qdf = CurrentDb.QueryDefs("MyQuery")
savedSQL = qdf.SQL

'Do the email

qdf.SQL = savedSQL

Set qdf = Nothing