Solved

Need Access 2010 module to export and email an XLS file

Posted on 2015-01-12
3
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 47

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
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…

737 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