after delete export

[arcootje]

i've got a database with a query that combines several tables.
i want a macro or a module what makes the folowing automatacly:
msgbox: "are you sure that you want to export to sales.xls?
first delete file: sales.xls
export query sales to sales.xls
mail sales.xls

can anybody help me??

 

[smozgur]

Hi arcootje,

Following code might help. Please change the 3 variables as you need, file path & name, query name and email address.
It doesn't have a delete routine since it will overwrite the existing file automatically.

Sub ExportFile()
Dim objxls As Object
Dim objwrk As Object
Dim strFileName As String
Dim strQuery As String
Dim strEmailAddress as String

  If MsgBox("Are you sure that you want to export to sales.xls?", _
                            vbQuestion + vbYesNo) = vbNo Then Exit Sub
  
  '*****CHANGE THESE PARAMETERS*****
  strFileName = "C:\sales.xls"
  strQuery = "MyQueryName"
  strEmailAddress = "someone@email.com"
  '*********************************
  
  DoCmd.TransferSpreadsheet acExport, _
                            acSpreadsheetTypeExcel9, _
                            strQuery, _
                            strFileName
  
  'Send email by using Excel SendMail method.
  Set objxls = CreateObject("Excel.Application")
  Set objwrk = objxls.workbooks.Open(strFileName)
  objwrk.SendMail strEmailAddress , strFileName 'You can put a subject instead using strFileName here
  objwrk.Close False
  objxls.Quit: Set objxls = Nothing
End Sub

I hope it helps.
Suat