Email Access Generated CSV to User Group

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?

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
    '[3] = ****  not specified as yet *****
    Select Case uQueryName
            Call addLogFile("Admin", 4, "REPORT 1 executed")
            Call addLogFile("Admin", 4, "REPORT 2 executed")
        Case Is = "Q_LESS_THAN_FULL_PALLET"
           Call addLogFile("Admin", 4, "REPORT 4 executed")
            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
            MsgBox "An error occured. The file has not been exported."
            Call addLogFile("Admin", 6, "ERROR! AN ERROR OCCURRED WHILE RUNNING A REPORT.")
        End If
        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

Who is Participating?
Simon BallConnect With a Mentor Commented:
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.
Simon BallCommented:
acces has ane email option built in


it has lots of parameters / options
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.