Output via Excel with Name saving issues

[JoseSharp]

With MS Access 2000 I am outputing information in excel via an email. Within the excel file name I need to to enter a Company Number. The CN is part of the data and is retreivable via a select query. My questions is how to I get the CN in the saved name of the file/attachement? Below is some of the code I think should work but does not.

'Get Company name via query F1=CN
strSQL2 = "SELECT qrySelectInvoices4.F1 FROM qrySelectInvoices4 WHERE [AutoNumber] = " & EmailAdr2 & ""

'save spreadsheet
strFile2 = "6th Domestic Backup as at " + strSQL + CStr(Format((Now()), "dd-mm-yyyy"))
strOutFile2 = "C:\temp\" + strFile2 + ".xls"

Any ideas??

 

[JoseSharp]

Thanks Pat, great bit of code which has really helped me out