Solved

Access Function to email a query in CSV format

Posted on 2014-12-08
7
309 Views
Last Modified: 2014-12-08
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
Comment
Question by:P-Daddy
  • 3
  • 3
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40487239
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40487258
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40487283
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:P-Daddy
ID: 40487484
Nick67 -

Thanks, but it has to be a CSV file format and the SendObject does not appear to support CSV.
0
 

Author Comment

by:P-Daddy
ID: 40487502
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40487529
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
 

Author Closing Comment

by:P-Daddy
ID: 40487554
Rey - That got it! Thanks for the assist!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now