Auto email for tasks by start date (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 11:42
Joined
Dec 5, 2017
Messages
766
Hi All -

In our mfg inspection db, I enter new (upcoming/not yet started) jobs. Basic data will include scheduled start date. This may be next week or four months from now or something random like 37 days from now. In other words, not every 3 months, 6 months, etc.

I would like to have Access send me an email reminder a week in advance of the scheduled start date.

I would appreciate some guidance on how to approach this.

The scheduled date is a field in tblJobs and I could easily add an email field to tblPersonnel.

Thanks a bunch for any guidance,

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:42
Joined
Oct 29, 2018
Messages
13,733
Hi Tim. You could create a query to list all upcoming jobs for the following week and then use code at startup to check it. If there are any, send the email reminder.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 28, 2001
Messages
18,727
More specifically, try a DateAdd function where you add -7 days to the anticipated start date and see if that is >Date().

However, there is the issue that you would need to include a marker to say "Already sent this" - so you don't get barraged by e-mails for things about which you have already been reminded.
 

Isaac

Lifelong Learner
Local time
Today, 08:42
Joined
Mar 14, 2017
Messages
3,273
I was just wondering, do you have access to a SQL Server database? Along with any meaningful level of a) control, or, b) cooperation from the dba's?
 

Zydeceltico

Registered User.
Local time
Today, 11:42
Joined
Dec 5, 2017
Messages
766
I was just wondering, do you have access to a SQL Server database? Along with any meaningful level of a) control, or, b) cooperation from the dba's?
We do have SQL Server but I do not have - or should I say - will not be given - permissions for that interaction. :) I'm certain.
 

Isaac

Lifelong Learner
Local time
Today, 08:42
Joined
Mar 14, 2017
Messages
3,273
We do have SQL Server but I do not have - or should I say - will not be given - permissions for that interaction. :) I'm certain.
Fair enough. I was thinking of storing just enough data in a SQL-side table, then creating a SQL stored procedure that included sp_send_dbmail in it.
(Even if you didn't have any access to scheduling capacities, like SQL Agent Jobs or SSIS, but if you had that procedure, you could run it from your Access database, it would still be far superior to Access automating Outlook).

It was a long shot question. :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 28, 2001
Messages
18,727
Don't actually need to create an Outlook Application object if you look up what you need for Collaboration Data Object (CDO). Or if you have Outlook available on your system, there is such a thing as DoCmd.SendObject that could also send the mail for you through Outlook.

The trick is to run this often enough. The SIMPLE case is you run it manually every work day. Run whatever it is that would send the mail. If it is Monday and something would have come due next Saturday or Sunday, the mail will be a day or two later than your seven-day notice. But there are implications about scheduling a Saturday or Sunday start time anyway.

The other is more involved but if the 7-day lead-time is critical AND you can start projects on Saturdays or Sundays, there IS a way. You COULD make a Macro that has essentially two steps: Step one is a RunCode of a function (because RunCode doesn't run subroutines) that you would only use for the purpose of evaluating and sending this mail, and step two of the macro is the Application.Quit so that the Macro doesn't leave anything hanging after you send the mail. THEN if you could get permission to do this on your machine, have the Windows Task Scheduler run Access (as you) using a command line. Point it to your FE file. Supply the command line with option /X:macro-name after the FE file spec. This could run once daily as long as your machine is on at the time.

Note that if you are required to turn your machines off at night and over the weekend, you do better to use the simple method and run it daily by hand or by automation if you have an "Opening Form" that you can run that would test who is running it, then if it IS you, run your query and your send-mail procedure at startup. If it happens that you include a marker that remembers when you have sent notices, then relaunching Access later in the day simple finds nothing to send right now.
 

Users who are viewing this thread

Top Bottom