run-time error 2306

Question

I use DoCmd.SendObject to send a query's result to the user with XLS format as following.
DoCmd.SendObject acSendQuery, "qryMyQuery", acFormatXLS, "[e-mail]", , , "My Query", , False

The syntax is correct, but it pops up an error message saying, "There are too many rows to output, based on the limitation specified by the output format...". I know the reason is because of the default to an old version of Excel which only allows 16K rows while mine has more than that. And one solution is use DoCmd.OutputTo acOutputQuery, "My Query", acSpreadsheetTypeExcel9, "C:\Document\MyFile.xls", True

But I want to send the excel file directly to the user, not to my local machine. Also, it had worked well before until now?? Do you know how to fix this issue? Thank you.

 

Answer 1

I use DoCmd.SendObject to send a query's result to the user with XLS format as following.
DoCmd.SendObject acSendQuery, "qryMyQuery", acFormatXLS, "[e-mail]", , , "My Query", , False

The syntax is correct, but it pops up an error message saying, "There are too many rows to output, based on the limitation specified by the output format...". I know the reason is because of the default to an old version of Excel which only allows 16K rows while mine has more than that. And one solution is use DoCmd.OutputTo acOutputQuery, "My Query", acSpreadsheetTypeExcel9, "C:\Document\MyFile.xls", True

But I want to send the excel file directly to the user, not to my local machine. Also, it had worked well before until now?? Do you know how to fix this issue? Thank you.

Try this:

DoCmd.SendObject acSendQuery, "qryMyQuery", "Microsoft Excel 97-10 (*.xls)", "[e-mail]", , , "My Query", , False

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Dirk, thanks for responsing. When I change to DoCmd to yours, it pops up an error message at acSendQuery", "Compile error - invalid outside procedure" (?). Thanks

Answer 3

Dirk, thanks for responsing. When I change to DoCmd to yours, it pops up an error message at acSendQuery", "Compile error - invalid outside procedure" (?). Thanks

That error message usually means that the line of code has been placed in a module but outside the bounds of any Sub or Function procedure.  Did you put the line of code in place of your previous line, or were you testing it someplace else?  Does the line appear to be inside a Sub or Function -- between the opening line and the End Sub/End Function line?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Yes, I did put the line of code in place of my previous line. And yes, the line is inside a Public Sub procedure, between the opening line and the End sub. There are codes in the form's On Timer event, in turn call a sub procedure. From this sub procedure, there is an IF statement, if it meets a condition then call this Public Sub.  

Answer 5

Yes, I did put the line of code in place of my previous line. And yes, the line is inside a Public Sub procedure, between the opening line and the End sub. There are codes in the form's On Timer event, in turn call a sub procedure. From this sub procedure, there is an IF statement, if it meets a condition then call this Public Sub.  

Would you mind posting the complete code of the whole module?  Although I could easily have posted code with an error, the error message you're getting doesn't seem appropriate to the situation.  Maybe there's an If statement without an End If somewhere, or an unmatched quote that is confusing the compiler.

Is the sub with the SendObject statement in the form's module, or is it in a separate module?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

The sub with the SendObject statement is in a separate module (not in the form's module; maybe that's why it pops up that error message, but I am not sure).  In the form's On Timer event, a public sub (1) is called.  Then (1) in turn calls another Public Sub (2). Here is the code of this sub (2).

Public Sub MailExelFile
If Monday() or Tuesday() Then
   Call sendfileToUser

End If

End Sub

-------
Public Sub sendfileToUser()
DoCmd.SendObject acSendQuery, "qryMyQuery", "Microsoft Excel 97-10 (*.xls)", "[e-mail]", , , "My Query",,False

End Sub

---

Answer 7

The sub with the SendObject statement is in a separate module (not in the form's module; maybe that's why it pops up that error message, but I am not sure).  In the form's On Timer event, a public sub (1) is called.  Then (1) in turn calls another Public Sub (2). Here is the code of this sub (2).

Public Sub MailExelFile
If Monday() or Tuesday() Then
   Call sendfileToUser

End If

End Sub

-------
Public Sub sendfileToUser()
DoCmd.SendObject acSendQuery, "qryMyQuery", "Microsoft Excel 97-10 (*.xls)", "[e-mail]", , , "My Query",,False

End Sub

That doesn't look like a copy & paste from a module (judging from the font change).  Did you leave things out? 

The DoCmd.SendObject statement itself works fine for me, when executed from the Immediate window.  Does it work for you, if you try it from the Immediate window? 

What I asked for and was hoping to see was the entire code of the module, copied and pasted, so that I can see whether there is something else wrong in the code that accounts for the error message, "Invalid outside procedure".

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

Yes, I did leave some of things out because there are too many repeatitions; there are a lot of DoCmd.SendObject statements, and because others are workings fine except this SendObject statement. 

I try this DoCmd.SendObject (with "Microsoft Excel 97-10 (*.xls)" replaced the old one) in the Immediate window and this time it pops up, "Compile error: Expected function or variable".

  

Answer 9

Yes, I did leave some of things out because there are too many repeatitions; there are a lot of DoCmd.SendObject statements, and because others are workings fine except this SendObject statement. 

I understand your desire to save time and bandwidth, but that makes it impossible for me to attempt to debug the error message you reported.  An error like "Invalid outside procedure" requires context to figure out, since the error isn't in the statement itself.  If you don't want to post the whole module, but are willing to post some of it, post everything from the start of the preceding procedure to the end of the procedure reporting the error, leaving no lines out.  Maybe we can see what's wrong from that. 

But under no circumstances should you manually transcribe the code into a message for debugging -- it's too easy to accidentally modify it that way.

I try this DoCmd.SendObject (with "Microsoft Excel 97-10 (*.xls)" replaced the old one) in the Immediate window and this time it pops up, "Compile error: Expected function or variable".

What *exactly* did you put in the Immediate window?  If you began it with a question mark, that was an error.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 10

I just put your above statement,

DoCmd.SendObject acSendQuery, "qryMyQuery", "Microsoft Excel 97-10 (*.xls)", "[e-mail]", , , "My Query", , False

the Immediate window without a question mark. This time it pops up the original error message, error 2306.  Here is the complete codes.

Private Sub Form_Timer()  
    Call EmailTasks

End Sub
=====
Public Sub EmailTasks()
    Call MailExelFile

End Sub
=====

Public Sub MailExelFile
    Dim varHour As Variant
    Dim varDay As Variant
    varDay = Weekday(Date)
    varHour = Hour(Time)
   
    If (varDay = 2) Or (varDay = 3) Then
                  Call sendFiletoUser
    End If

End Sub
======
Public Sub sendfileToUser()
DoCmd.SendObject acSendQuery, "qryMyQuery", "Microsoft Excel 97-10 (*.xls)", "[e-mail]", , , "My Query",,False

End Sub 

     

  

Answer 11

If entering it in the Immediate window gives a different result this time, then I imagine you entered something different last time; maybe a typo.

I think we're getting closer, though, if only because you got the original, meaningful error again.  What version of Access are you running?  If it's an older version, maybe the format "Microsoft Excel 97-10 (*.xls)" is not supported.

I should ask also, how many rows does your query return?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 12

It's an old Access but is converted to Access 2007. I checked the Access Button -> Resouces -> about MS Office Access 2007 SP2

There are more than 18K rows. Thanks.

Answer 13

I've been checking into this a bit further, and it looks like the format string I gave you doesn't really work -- the format is ignored.  However, this one seems to, at least for me:  "Excel 97 - Excel 2003 Workbook (*.xls)".  So try your SendObject line like this:

    DoCmd.SendObject acSendQuery, "qryMyQuery", "Excel 97 - Excel 2003 Workbook (*.xls)", "[e-mail]", , , "My Query", , False

I used it to send myself a query returning 50,000 rows, so I have some hope that it will work for you.  Please let me know if it works; I'll keep my fingers crossed.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 14

Yes, it works !!!!! Thanks a lot, Dirk. Out of curiosity, why do you use Excel 97 - Excel 2003 Workbook (*.xls)" while I have Access 2007? Thanks again. 

Answer 15

Yes, it works !!!!! Thanks a lot, Dirk. Out of curiosity, why do you use Excel 97 - Excel 2003 Workbook (*.xls)" while I have Access 2007?

I started down this path without knowing what version of Access you were using, and you mentioned using acSpreadsheetTypeExcel9, which would be Excel 97-2003 format.  And then, even after I realized you were using Access 2007, I decided that choosing the 97-2003 format would be a sort of "lowest common denominator", such that your recipients could read the spreadsheet whether thay had any version of Excel from 97 to 2010, while still accomodating the number of rows you needed.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 16

Thanks again for your expertise.