SendObject sends Excel attch in "old" excel format

[Guest]

I am trying to figure out how to get the Access query that I am attaching to
an email to be formatted in the newer Excel 97-2003 version so that my
500+end users can open on their blackberries and PDAs. For some reason, it
won't send the attachment in the newer Excel version even though I specify
the format as acSpreadsheetTypeExcel97 in place of acFormatXLS. If I
manually intervene and open the attachment and "Save As" ..excel 97-2003,
then send it, they can open it. But I can't manually intervene except during
a simple test b/c this ends up being 500 separate emails to 500 separate
users with 500 separate sets of results in their respective query attached.

This is the code I am using:
DoCmd.SendObject acSendQuery,
"qry1stStalledReport-4-FinalResultsFilterforEmails",
acSpreadsheetTypeExcel97, rscriteria![login id], , , "Can you open this file
on yoru blackberry?", "This is a test.", -1

 

[pietlinden]

I am trying to figure out how to get the Access query that I am attaching to
an email to be formatted in the newer Excel 97-2003 version so that my
500+end users can open on their blackberries and PDAs. For some reason, it
won't send the attachment in the newer Excel version even though I specify
the format as acSpreadsheetTypeExcel97 in place of acFormatXLS. If I
manually intervene and open the attachment and "Save As" ..excel 97-2003,
then send it, they can open it. But I can't manually intervene except during
a simple test b/c this ends up being 500 separate emails to 500 separate
users with 500 separate sets of results in their respective query attached.

This is the code I am using:
DoCmd.SendObject acSendQuery,
"qry1stStalledReport-4-FinalResultsFilterforEmails",
acSpreadsheetTypeExcel97, rscriteria![login id], , , "Can you open this file
on yoru blackberry?", "This is a test.", -1

I suppose you could use automation and CopyFromRecordset to dump the
data into Excel and then save & attach the new file to your message...
but then you couldn't use SendObject, you'd have to automate your e-
mail client.

 

[DAVID]

I'm surprised that works at all - I'll have to
pull out a current copy of Access to see what it
does. In my old version of access, the value of
acformatxls is:
"Microsoft Excel (*.xls)"

and the value of acSpreadsheetTypeExcel97 is
8

acformatxls was used for SendObject and OutputTo,
acSpreadsheetTypeExcel97 was used for TransferSpreadSheet.

TransferSpreadSheet is a thin wrapper on a
JET export query, using the JET xls IISAM.

OutputTo and SendObject are separate code
that reads values, and writes the values
out as text. I think they were probably
originally written in Access Basic, as part
of the utility module.

OutputTo exports in BIF, and I expect that
SendObject does too, although I haven't checked.

BIF is Binary Interchange Format. If is a very
old cross-platform spreadsheet format, as old
as Excel. Excel silently opens spreadsheets in
BIF format, it doesn't tell you that it is
'converting'. Most spreadsheet viewers can
open BIF files: Access/Jet can't, so you can't
import files that you export this way.
Disapointing if blackberries and PDAs don't
recognize this, but you should check if they
have BIF as another option.

You can use the JET IISAM formats, if they
are supported by your version of Access, if
you write additional code to attach the files
and send email. It's more complex, but it's
standard code that you can download. Ask again
in a new message specifically about that.

(david)