Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Email Access Generated CSV to User Group

Posted on 2011-03-25
2
Medium Priority
?
283 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

618 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