Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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.
0
P-Daddy
Asked:
P-Daddy
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
Nick67Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
P-DaddyAuthor Commented:
Nick67 -

Thanks, but it has to be a CSV file format and the SendObject does not appear to support CSV.
0
 
P-DaddyAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
in the
RunCode part of your macro, there is a Function Name where you can specify the Function to run


Function Name   ExportMyQuery()

in a regular module, create  a function

Function ExportMyQuery()

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

End Function
0
 
P-DaddyAuthor Commented:
Rey - That got it! Thanks for the assist!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now