Date event e-mail

[Cran29]

Would it be possible to automatically have access e-mail certain people should a date event be reached for a certain field. If this is possssible how would I do this.

[June7]

This level of automation is tricky. I do this by a combination of Windows Scheduled Tasks, VBScript file, and VBA code in Access.
At specified time each day Task Manager executes VBScript
VBScript opens Access project (in the code, substitute servername\path\filename with your values)

' ProcessKillLocal.vbs
' Sample VBScript to kill a program
' Author Guy Thomas http://computerperformance.co.uk/
' Version 2.7 - December 2010
' ------------------------ -------------------------------' 
Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill 
strComputer = "."
strProcessKill = "'msaccess.exe'" 
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
 objProcess.Terminate()
Next 
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit 
'End of WMI Example of a Kill Process 
 
Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""\\servername\path\filename.accdb""

VBA code behind default form checks current date meets conditions, if it does, procedure is called and email is sent

[TheShabz]

The only way I've been able to do this is via Outlook, and only in specific cases. You would first have to put some code into Outlook to bypass the security "feature" (more like pain in my ass) that wont let an email be sent by code. Further, Outlook has to be open and logged in (meaning you cant do this as a nightly batch job).

I've seen posts on the web that attempt to this via SMTP like through a gmail account. However, I've never seen any of these actually work. If using Outlook will suffice, follow the instructions here. I use this and can vouche for it.

[June7]

My process does require Outlook but did nothing special with Outlook, no coding. Does require that my computer be on and probably logged on. I just lock my computer every night, never log off. I usually leave Outlook open but think the code runs even if Outlook closed. Will have to test that again. In the code, substitute servername\path\filename with your values.

Public Sub ConstructionExtract()
'exports data to ConstructionExtract Access file
'copies file to zip folder
'opens Outlook and attaches file to msg and sends
Dim strZip As String
Dim strExtract As String
Dim Catalog As Object
strZip = "\\servername\path\filename.zip"
strExtract = "\\servername\path\filename.accdb"
'create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strExtract & ";"
Set Catalog = Nothing
'import tables to the ConstructionExtract Access file
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT * INTO Bituminous IN '" & strExtract & "' FROM ConstructionBIT;"
DoCmd.RunSQL "SELECT * INTO BituminousMD IN '" & strExtract & "' FROM ConstructionBMD;"
DoCmd.RunSQL "SELECT * INTO Concrete IN '" & strExtract & "' FROM ConstructionCONC"
DoCmd.RunSQL "SELECT * INTO Emulsion IN '" & strExtract & "' FROM ConstructionEMUL;"
DoCmd.RunSQL "SELECT * INTO PGAsphalt IN '" & strExtract & "' FROM ConstructionPG;"
DoCmd.RunSQL "SELECT * INTO SoilsAgg IN '" & strExtract & "' FROM ConstructionSA;"
DoCmd.RunSQL "SELECT * INTO SampleInfo IN '" & strExtract & "' FROM ConstructionSampleInfo;"
DoCmd.SetWarnings True
'create empty zip folder
'found this on web, no idea what the Print line does but if it isn't there, this won't work
Open strZip For Output As #1
Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
'copy file into zip folder
Dim objApp As Object
Set objApp = CreateObject("Shell.Application")
'variable for source file doesn't seem to work in this line
'also double parens not in original code but won't work without
objApp.Namespace((strZip)).CopyHere "\\servername\path\filename.accdb"
'open Outlook, attach zip folder, send e-mail
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "email address here"
''.cc = ""
''.bcc = ""
.Subject = "text here"
.HTMLBody = "text here"
.Attachments.add (strZip)
''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.send
End With
'delete zip folder and ConstructionExtract.accdb
Kill strZip
Kill strExtract
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Updates SET ConstructionExtract=#" & Date & "#"
DoCmd.SetWarnings True
End Sub

[TheShabz]

How do you bypass the Outlook security? With automation, I've always run across this


without the code to bypass it.

[June7]

It's been months since I set this up. I don't remember dealing with that. Other than setting the VBA Reference, which I should have mentioned earlier, to Microsoft Outlook 12.0 Object Library.

Even the Outlook Trust Center is set to "Warnings for signed macros; all unsigned macros are disabled." which I believe is the default.

I know it is working. One went out yesterday. I have copy in my Outlook sent folder and the receiver has confirmed previous transmissions.