Solved

Email Access Generated CSV to User Group

Posted on 2011-03-25
2
265 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

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