SendObject code bombing

K

Ken Smith

When I run the following code, to send a query as an Excel file:

DoCmd.SendObject acSendQuery, "qryFeesRpt", "MicrosoftExcelBiff8(*.xls)",
"DACSS@********", "", "", "Fee Report " & Forms("Assembly Documentation Menu
B").SubjLine, "DO NOT EDIT EMAIL SUBJECT LINE", True, ""

I get the following error message:

The formats that enable you to output data as a Microsoft Excel, rich-text
format, MS-DOS text, or HTML file are missing from the Windows Registry.


This seems to be documented in the KnowledgeBase article

http://support.microsoft.com/kb/226526/en-us

which suggests the fault may be caused by:

“The path that you specify does not exist.â€

My VBA skills are just short of what I’d call “weak.†I don’t understand
where I would use a path statement in this line of code. I have not stored a
file named qryFeeRpt.xls on the system.

Do any of my fellow and sister (but wiser) Access gurus have a suggested
correction to the line of code and would you be so kind as to show what it
looks like?
 
J

Justin Hoffman

Ken Smith said:
When I run the following code, to send a query as an Excel file:

DoCmd.SendObject acSendQuery, "qryFeesRpt", "MicrosoftExcelBiff8(*.xls)",
"DACSS@********", "", "", "Fee Report " & Forms("Assembly Documentation
Menu
B").SubjLine, "DO NOT EDIT EMAIL SUBJECT LINE", True, ""

I get the following error message:

The formats that enable you to output data as a Microsoft Excel, rich-text
format, MS-DOS text, or HTML file are missing from the Windows Registry.


This seems to be documented in the KnowledgeBase article

http://support.microsoft.com/kb/226526/en-us

which suggests the fault may be caused by:

"The path that you specify does not exist."

My VBA skills are just short of what I'd call "weak." I don't understand
where I would use a path statement in this line of code. I have not stored
a
file named qryFeeRpt.xls on the system.

Do any of my fellow and sister (but wiser) Access gurus have a suggested
correction to the line of code and would you be so kind as to show what it
looks like?
Click to expand...


Normally the format should be one of the following constants:

acFormatDAP
acFormatHTML
acFormatRTF
acFormatTXT
acFormatXLS

So your code should look like this (note the lack of quotes):

DoCmd.SendObject acSendQuery, "qryFeesRpt", acFormatXLS,

Of course, you could specify another format using quotes:

DoCmd.SendObject acSendQuery, "qryFeesRpt", "Microsoft Excel 5-7 (*.xls)"

but perhaps "MicrosoftExcelBiff8(*.xls)" is causing the problem.

Also, including Forms("Assembly Documentation Menu B").SubjLine all in the
same line might not be wise. I would separate it out, and assign it to a
string variable with the Nz function. You could also try the use of named
arguments to make you code easier to understand and debug and also a bit of
error handling wouldn't be bad:


Private Sub cmdEmail_Click()

On Error GoTo Err_Handler

Dim strSubject As String

strSubject = Nz(Forms!MyForm.txtSubject, "")

DoCmd.SendObject acSendQuery, _
ObjectName:="qryWhatever", _
OutputFormat:=acFormatXLS, _
To:="[email protected]", _
Subject:=strSubject, _
MessageText:="MyMessageText", _
EditMessage:=True

Exit_Handler:
Exit Sub

Err_Handler:

Select Case Err.Number

Case 2501
' User has cancelled the e-mail. Ignore this error.

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number

End Select

Resume Exit_Handler

End Sub
 
Top