Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Need Access 2010 module to export and email an XLS file

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
Cole100
Asked:
Cole100
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Martin LissRetired ProgrammerCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now