CODEWITHSUNDEEP
vbaemailms-accessexport-to-csv
R. Verhagen
R. Verhagen

Reputation: 19

How to email query result in csv format in Access

I have an access database and want to save the query result in CSV format and email this via outlook. Is there a way to do so?

I have tried DoCmd.TransferText command but the Specific could not be found

Public Sub export_query()
    DoCmd.TransferText acExportDelim, "Specific", "Query1", "C:\test\Query1.txt", True
End Sub

Upvotes: 1

Views: 841

Answers (1)

hisoka
hisoka

Reputation: 354

If you want to use a specification, you need to first do the export to text manually through the wizard. You probably haven't saved your specification or you are using the wrong name.

  1. Open your query.
  2. On the ribbon, select the External Data tab.
  3. Select the option to Export to text. (Alternatively, you could just right click your query in the navigation pane and export to text)
  4. When the wizard shows up, do not check off any of the options presented to you and click OK
  5. In the assistant screen, click Advanced.
  6. Setup the specification to your liking.
  7. Click Save as to save your specification and give it a name.

Make sure to use this name in the specification field of your DoCmd.TransferText. Also, change the extension to .csv rather than .txt for your output filename parameter.

And you could use the following function to both export and email your .csv :

Sub export_query()
    Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(olMailItem)

    ' Export to CSV
    DoCmd.TransferText acExportDelim, "Specific", "Query1", "C:\test\Query1.csv", True
    ' You could also try it without any specifications. (Untested)
    ' DoCmd.TransferText acExportDelim, "", "Query1", "C:\test\Query1.csv", True

    ' Send the email
    With objMail
        .To = "[email protected]"
        .Subject = "This is the subject."
        .Body = "This is the body."
        .Attachments.Add ("C:\test\Query1.csv")
        .Display '(If you want to see the email and send it yourself)
        '.Send '(If you want to send the email without seeing it)
    End With

    ' Clean up the objects
    Set objMail = Nothing
    Set objOutlook = Nothing
End Sub

Upvotes: 1

Related Questions