Solved

Access Function to email a query in CSV format

Posted on 2014-12-08
7
316 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

770 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