Send custom excel file name

[AgDawg]

I am currently using the macro to email a query. The query name is qryCAO so it puts the file name qryCAO.xlsx in the mail message.

I have a need to rename the file to include todays date. So it would pull up the outlook mail message with the excel file attached that is named "Computer Cost Add-on_20130111"

I have searched and can come up with the code to produce the email but have no idea how to rename.

 

[Alansidman]

I don't use macros in Access, but below you will find some code for exporting a query to the desktop of a Win XP machine and it adds the date to the file name.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim reportName As String
Dim theFilePath As String

reportName = "Join--View"
theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"
theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
      
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True

MsgBox "Look on your desktop for the report."

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
    
End Sub

This also outputs the file to Excel 2003 format. For later versions, you will need to modify the transferspreadsheet line.

Look here: http://msdn.microsoft.com/en-us/library/office/bb214134(v=office.12).aspx