Solved

Need Access 2010 module to export and email an XLS file

Posted on 2015-01-12
3
246 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

820 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