Solved

Email Access Generated CSV to User Group

Posted on 2011-03-25
2
269 Views
Last Modified: 2012-05-11
Hi All,

I have developed an application that exports a number of custom report queries to a users desktop.

Today, my boss has asked me to include the option to email any of these queries to to either a user, or an email group.

I do not have an email address or group table inside the current application.

My initial thoughts are that I would add a seperate command button to initiate the email process (thus making sure it is seperate from the export to excel process), however, I have not a clue about how this would work from a coding prospective as I have never worked with email from access before.

I have included the current procedure that generates an export file to the users desktop as a reference point.

Can anyone help me here?

Shaun
Public Function exportReport(uQueryName As String)
    
    Dim currentPath, folderPath, savePath, saveFileName, nowDate, nowTime, nowDateTimeStamp, msoFileTypeAllFiles
    Dim strtst, vFY As Long
    'specifying the file & folderPath
    currentPath = CurrentProject.Path
    
    'Report Definitions with Numbers
    '[1] = Q_SAME_DAY_OPPORTUNITIES_PART_B
    '[2] = Q_SAME_WEEK_OPPORTUNITES_PART_C
    '[3] = ****  not specified as yet *****
    '[4] = Q_LESS_THAN_FULL_PALLET
    '[5] = Q_VENDOR_DATE_CODE_COMPLIANCE
    '[6] = Q_RAW_DATA_EXTRACT_SKU
    '[7] = Q_RAW_DATA_EXTRACT
        
    Select Case uQueryName
        Case Is = "Q_SAME_DAY_OPPORTUNITIES_PART_B"
            Call addLogFile("Admin", 4, "REPORT 1 executed")
        Case Is = "Q_SAME_WEEK_OPPORTUNITES_PART_C"
            Call addLogFile("Admin", 4, "REPORT 2 executed")
        Case Is = "Q_LESS_THAN_FULL_PALLET"
           Call addLogFile("Admin", 4, "REPORT 4 executed")
        Case Is = "Q_VENDOR_DATE_CODE_COMPLIANCE"
            Call addLogFile("Admin", 4, "REPORT 5 executed")
        Case Is = "Q_RAW_DATA_EXTRACT_SKU"
            Call addLogFile("Admin", 4, "REPORT 6 executed")
        Case Is = "Q_RAW_DATA_EXTRACT"
            Call addLogFile("Admin", 4, "REPORT 7 executed")
        Case Else
           'NOTHING TO DO
        End Select
        
    Call showProcessStatus("Creating your IEA report. Please wait...")
    
    folderPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    
    nowDate = Format(Date, "yyyymmdd_")
    nowTime = Format(Time, "hhmmssAMPM")
    nowDateTimeStamp = nowDate & nowTime
    saveFileName = "IEA_EXPORT" & "_" & nowDateTimeStamp
    
    saveFileName = InputBox("Please choose a file name (without *.xls extension)", "Save As...", saveFileName)
       
    If saveFileName <> "" Then
        saveFileName = Replace(saveFileName, ".xls", "")
        savePath = folderPath & "\" & saveFileName & ".xls"
        DoCmd.TransferSpreadsheet acExport, , uQueryName, savePath, True
           
        strtst = Dir(savePath)
        If Len(strtst) > 0 Then
            MsgBox "Your exported IEA report has been saved to your desktop. Press OK to continue.", vbInformation
        Else
            MsgBox "An error occured. The file has not been exported."
            Call addLogFile("Admin", 6, "ERROR! AN ERROR OCCURRED WHILE RUNNING A REPORT.")
        End If
       
    Else
        'CLEAN UP THE ORIGINAL FORM
        Call hideProcessStatus
        'formating the from location selections
        Forms!UF_REPORT_SELECTION.Controls("FromLocationText").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("DCStart").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("DCStart").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("FromLocName").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("FromLocName").Visible = False
        
        'formating the to location selections
        Forms!UF_REPORT_SELECTION.Controls("ToLocationText").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("DCEnd").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("DCEnd").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("ToLocName").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("ToLocName").Visible = False
        
        'formatting the week selections
        Forms!UF_REPORT_SELECTION.Controls("StartDate").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("EndDate").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("lblRefNo").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("RefNo").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("RefNo").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("SKUName").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("SKUName").Visible = False
        Forms!UF_REPORT_SELECTION.Controls("ChkAll").Value = 0
        Forms!UF_REPORT_SELECTION.Controls("ChkAmbient").Value = 0
        Forms!UF_REPORT_SELECTION.Controls("ChkChiller").Value = 0
        Forms!UF_REPORT_SELECTION.Controls("ChkFreezer").Value = 0
        Forms!UF_REPORT_SELECTION.Controls("ReportList").Enabled = True
        Forms!UF_REPORT_SELECTION.Controls("WeekFrom").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("FromWeekMonday").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("WeekTo").Value = Null
        Forms!UF_REPORT_SELECTION.Controls("ToWeekSunday").Value = Null
        'setting the current date
        Call getPlanningWeekNo(vFY, "UF_REPORT_SELECTION")
        Forms!UF_REPORT_SELECTION.Controls("WeekFrom").Value = Forms!UF_REPORT_SELECTION.Controls("CurrentFiscalWeek").Value
        Forms!UF_REPORT_SELECTION.Controls("WeekTo").Value = Forms!UF_REPORT_SELECTION.Controls("CurrentFiscalWeek").Value
        
        Exit Function
    End If
    
    Call hideProcessStatus
End Function

Open in new window

0
Comment
Question by:creativefusion
[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
  • 2
2 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35215881
acces has ane email option built in

docmd.sendobject....

it has lots of parameters / options
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 500 total points
ID: 35215911
so you could use that to send the query/report object..

docmd.SendObject acSendQuery, "qryname", outputformat, to, cc, bcc, subject, messagetext, editmessage true / false, template

theres good stuff in access help about its output formats etc.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

691 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