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 unzipped 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 zipped file. 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 populate the Excel 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




Re: Winzip A Spreadsheet, Then Send Via Email

cdun2


I have added the following line of code, and it seems to work;

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

The only problem is, "Rev by AcctCode" is an MS Access query that takes the following parameter; Forms![SalesAssoc]![e-mail]. Can I use 'TransferSpreadsheet' if I need to pull data using a query that requires a parameter

Thanks again!

cdun2






Re: Winzip A Spreadsheet, Then Send Via Email

ADG

See the answer to the post

How to create a .zip file with VBA in WindowsXP without any third party software

This should enable you to produce a zip file for mailing





Re: Winzip A Spreadsheet, Then Send Via Email

cdun2

Thank you, I will look into this!

cdun2