creativefusion
asked on
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?
Shaun
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
docmd.sendobject....
it has lots of parameters / options