Solved

Need Access 2010 module to export and email an XLS file

Posted on 2015-01-12
3
241 Views
Last Modified: 2016-02-11
I am working with Access 2010. I have a bunch of modules I use to export a report to a PDF and then attach and email this pdf.

I would like a module that exports a query into an XLS file and then attaches and emails the XLS file.

This is the code I am currently using:

Function send_gray_ssoc_mtd()
   On Error GoTo Err_Send_Click
 
    Dim mydb As Database, RS As Recordset
    Set mydb = DBEngine.Workspaces(0).Databases(0)
    Dim attachPDF As String
    Dim docname As String, ctl As Control, strTo As String
    Dim path As String, subject As String, body As String
    Dim attach As String, blnSuccessful As Boolean
         
     Application.Echo False
   
    Set RS = mydb.OpenRecordset("qry_email_gray")
       
    path = "c:\temp\"
    docname = "gray_ssoc_mtd"
    attachPDF = path + docname & ".pdf"
    subject = "Gray - SSOC MTD"
    body = "Month to Date Report is Attached"
    DoCmd.OutputTo acOutputReport, docname, acFormatPDF, attachPDF
 
 Do Until RS.EOF
      strTo = RS!Email
       blnSuccessful = FnSafeSendEmail(strTo, subject, body, attachPDF, "", "")
       RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
    Set mydb = Nothing
 
exit_send_click:
     
    Application.Echo True
    Exit Function
 
Err_Send_Click:
    Resume exit_send_click
     
End Function
0
Comment
Question by:Cole100
3 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40545988
Here is some fairly basic code to do what you have asked here:
export and email an XLS file

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strAttPath As String

strAttPath = "C:\yourFolder\YourFile.xls"

    'Export the query to an Excel File
    DoCmd.OutputTo acOutputQuery, "qryYourQuery", acFormatXLS, strAttPath
    
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    With objOutlookMsg
        ' Add the To recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("YourEmailAddressHere")
        objOutlookRecip.Type = olTo

        ' Set the Subject, Body, and Importance of the message.
        .Subject = "Email Excel File"
        .Body = "Here is your Access Query as an Excel file"

        Set objOutlookAttach = .Attachments.Add(strAttPath)

        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next

        .Save
        .Send
        
    End With
'Cleanup
Set objOutlook = Nothing

Open in new window


...Based on the popular code here:
http://support.microsoft.com/kb/161088

When you need to email a file other than an Access object, ( or otherwise control the email) ...you cannot use SendObject, ...you have to create an email with automation.
With this code you will have to add a reference to the MS Outlook object Library (In the VBA editor, click: Tools-->References)

;-)

JeffCoachman
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40597981
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA: insert new column and re-adapat string with lower letterS 4 27
Help with DoEvents 8 26
Clear Filter 8 38
sort column using vba 2 17
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

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