Link to home
Start Free TrialLog in
Avatar of P-Daddy
P-Daddy

asked on

Access Function to email a query in CSV format

I'm a fairly novice user, looking for a way to send a query result set in CSV format to my client via email. If email presents a challenge, I'm fine with writing to a local directory and I can script something else to pick it up and email it.

If you propose a function, please also let me know how to call this function from a Macro step.

Thanks.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

first , you need to create an export specification


1.right click on the table/query
2.select export > Text file
   click on Browse and locate the destination folder
3. (you can accept the proposed name or change it)
click Save, then click OK
4. In the export text wizard select the type (Delim )
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acExportDelim, "ExportSpecName", "NameOfQuery", "C:\myCsv.csv", True
here is a simple code to send email with attachment

Sub SendMail(strTo,strBody,strAttachment)
On Error Resume Next
Dim olApp As New Outlook.Application
Dim olAppMail As Outlook.MailItem, strThank
     Set olAppMail = olApp.CreateItem(olMailItem)

    With olAppMail
        .To = strTo
       
        .Subject = strSubject
            .Body=strBody
          If strAttachment & "" <> "" Then
               .Attachments.Add strAttachment
          End If
        .Display
        '.Send
    End With
End Sub
Just do the macro SendObject
https://support.office.com/en-us/article/SendObject-Macro-Action-de087df0-6d0c-4dfc-b1b6-ba1172d28ac5

Quick and dirty, but if your a novice, it might meet the need.
Avatar of P-Daddy
P-Daddy

ASKER

Nick67 -

Thanks, but it has to be a CSV file format and the SendObject does not appear to support CSV.
Avatar of P-Daddy

ASKER

Rey Obrero -

Thanks for your suggestion. I followed your steps but am unsuccessful. Where do I place this DoCmd.TransferText and how do I run it?

DoCmd.TransferText acExportDelim, "eSportsonline_Product_Feed Export Specification", "qry_1000_Select_3_FINAL", "\\ssgvrtf4\sapshared\Internet Dept\Consumer-Direct\eBay\Assortment\feed\automated\eSportsonline_Product_Feed.zip", True

I want to run it as my last Macro step, so I tried running it under RunCode, but that did not work.

Can you guide me a bit more? Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of P-Daddy

ASKER

Rey - That got it! Thanks for the assist!