Zip A Spreadsheet, Then Send Via Email

[cdun2]

Hello,
I'm a non_VBA coder who has been asked to update the following Function:

**************************

Function EMAILER_REV_BY_ACCTCODE_MACRO()

On Error GoTo RA_EMAILER_Err

DoCmd.SetWarnings False
DoCmd.SelectObject acForm, "SalesAssoc", False
DoCmd.SendObject acQuery, "Rev by AcctCode", "MicrosoftExcel(*.xls)", Forms![SalesAssoc]![e-mail], "", "", "Spreadsheet of Core Revenue for Current Month by Account", "Attached please find an Excel spreadsheet of the Core revenue for your Accounts. ", False, ""
DoCmd.RunCommand acCmdRefreshPage
DoCmd.RunCommand acCmdRecordsGoToNext

RA_EMAILER_Exit:
Exit Function

RA_EMAILER_Err:
MsgBox Error$

End Function
********************************
The function works fine, but the size of the Excel file is too large to send via email (mailbox size restriction). The user has WinZip installed on their computer. My thought is that the code needs to be modified so that the query is called and the results sent to an Excel file, then zip the file, then email the zip. I've tested zipping the Excel file, and the resulting size is well within the mailbox size limit.

How can I modify the above code to create the Excel file, fill the file with the query results, zip the file, then send it via email?

The code is contained within an MS Access 2000 Module. The O/S is Windows XP SP2.

Thank you for your help!

cdun2

 

[PHV]

Hav a look to the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cdun2]

Thanks, I will look into this.

 

[cdun2]

I got this to work:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Rev by AcctCode", "C:\Learning\ZipExcelFile\Results.xls"

The problem is, "Rev by AcctCode" is an MS Access Query that takes the following parameter value; Forms![SalesAssoc]![e-mail].

I can run the code without the parameter, and I get results into a spreadsheet, but I need the parameter value. How can I do this?

Thanks again!

 

[PHV]

Be sure that the SalesAssoc form is open and populated at the time you create the spreadsheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886