Export Access Data to Excel, Zip, Send 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 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? I've searched online for some ideas, but have not really found anything.

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

Thank you for your help!

cdun2

[Rabbit]

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? I've searched online for some ideas, but have not really found anything.

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

Thank you for your help!

cdun2

You'll have to figure out the exact command line syntax but you can use Call Shell("text") to run a command line program.

As for the e-mail, I've never e-mailed anything with Access so someone else will have to help you there.

[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 need the parameter to get the correct results into the spreadsheet. How can I do this?

I have the code to send the email attachment (SendObject), and I have the code to zip the file. All I need is to figure out is how to use a parameter query with 'TransferSpreadsheet' .

Thanks again!

[MMcCarthy]

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 need the parameter to get the correct results into the spreadsheet. How can I do this?

I have the code to send the email attachment (SendObject), and I have the code to zip the file. All I need is to figure out is how to use a parameter query with 'TransferSpreadsheet' .

Thanks again!

Make sure the SalesAssoc form is open to the appropriate record and the TransferSpreadsheet should work fine.

Mary

[cdun2]

But there are other controls on the form. How can there be any association of the value in Forms![SalesAssoc]![e-mail] to the 'Rev by AcctCode' parameter query? I wouldn't think that when using TransferSpreadsheet that when the query is a parameter query, that I can 'assume' the form field that holds the parameter.

[MMcCarthy]

But there are other controls on the form. How can there be any association of the value in Forms![SalesAssoc]![e-mail] to the 'Rev by AcctCode' parameter query? I wouldn't think that when using TransferSpreadsheet that when the query is a parameter query, that I can 'assume' the form field that holds the parameter.

It is the query that assumes the value. Try it and see.