Solved

Email Access Generated CSV to User Group

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now