Send email at a specific time from a multi user environment 2

[DevelopV]

I have a database that is used in a multi user environment. It is spit (back end and front end)

I want to send a report via email at predefined times of the day. For example 12:00 (Noon)
I know how to send reports via email.
My question is: what logic do I use to ensure that the report gets sent?
I can't put the code in the back end because the back end is never actually opened.
There are 3 users using the front end, and I don't want the email sent 3 times!

Many thanks in advance

 

[PHV]

I don't want the email sent 3 times
Use a table to record the fact that the email is sent.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DevelopV]

OK
But how do I send the mails at pre-defined times?

 

[PHV]

You may use the Timer event procedure of your main form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KornGeek]

My problem with using a timer is that it wouldn't work if nobody is using the database at that time.

I would simply create another fully-automated front-end that opens, emails the report, and then exits. I would then set up a task to automatically run this front-end at the selected time. Of course, the main drawback to this approach is having to maintain another version of the front-end. If the report is used by the other front-end and changes often, this becomes a horrible solution.

It's definitely not the only solution, and may not even be the best solution, but it's how I would approach it.

 

[vbajock]

You have to set it up on a stand-alone PC or a server session. You put a timer function in the start up form that checks what time it is, and when the clock hits it sends it. You can't do it from a user frontend unless you want a world of headaches from something that won't work half the time. Do it in Access 2007 or later, 2003 on down bugs out from memory leaks.

 

[jeffwest21]

I have a process that runs via a scheduled task that opens a standalone Access database which has a autoexec that just runs the email send out.

The code knows where to pick up the reports from as they are exported out via another scheduled task 2 minutes before hand.

If the report can be exported out to a file folder, the process can then pick them up as attachments and email them out.

'Clever boy...'

 

[DevelopV]

jeffwest21, can you share your process with us?

 

[jeffwest21]

This is run by an autoexec macro, it reads from 1 of three tables were the email address's are held. I run this by using a program called System Schedular to open the database, it just uses your outlook email account.

Option Compare Database

Function Email_me()
Dim varX1 As String
Dim varX2 As String

 varX1 = DLookup("[Email_Address]", "Email_Address")
 varX2 = DLookup("[Email_Address]", "Email_Address2")

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String
Dim strAttach2 As String
Dim strAttach3 As String
Dim strAttach4 As String

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strAttach1 = "C:\Users\JeffW\Documents\Reports\Daily_Report.pdf"
strAttach2 = "C:\Users\JeffW\Documents\Reports\Daily_Agent_Today.pdf"
strAttach3 = "C:\Users\JeffW\Documents\Reports\Daily_Report_Hours.pdf"
strAttach4 = "C:\Users\JeffW\Documents\Reports\File_Report.pdf"

'Generate email
With objEmail
'.To = ""
.To = varX1
.CC = varX2
.Subject = "Reports"
.Body = "Find attached the hourly reports."
.Display
.Attachments.Add strAttach1
.Attachments.Add strAttach2
.Attachments.Add strAttach3
.Attachments.Add strAttach4

objEmail.Send
End With

End Function

Function Email_me2()
Dim varX2 As String

 varX2 = DLookup("[Email_Address]", "Email_Address3")

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strAttach1 = "C:\Users\JeffW\Documents\Reports\Agent_By_Hour.pdf"

'Generate email
With objEmail
'.To = ""
.To = varX2
'.CC = varX2
.Subject = " Reports"
.Body = "Agent Hourly Calls."
.Display
.Attachments.Add strAttach1


objEmail.Send
End With

End Function

'Clever boy...'