Solved

Access Function to email a query in CSV format

Posted on 2014-12-08
7
311 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

932 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