Sending an email based on date

[alm395]

Is it possible to send an email reminder based on a specific date & time via Excel? If it is possible, does Excel have to be open to send or can we set the dates and basically forget about the spreadsheet until it needs to be updated?

Below is a checklist where tasks are to be completed before a storm hits 72 hours prior, 48 hours prior, 24 hours prior, 12 hours prior, 6 hours prior, and within 1 hour of the storm hitting.
The "End Time" shows those timeframes based on the "Anticipated Date" of the storm. The "Start Time" is 6 hours before the "End Date."

Task 1 is assigned to AAAA. Since it is indicated that "Task 1" should be completed in the "72 Hrs Prior" field, I would like an email sent to AAAA at the "Start Time" indicating their task is "Task 1." This would need to continue for all tasks assigned based on their timeframe.

I am open to suggestions on other MS applications that may be able to accommodate this request.

Thanks for reading & helping!!! ?

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	PRE-STORM CHECKLIST
2
3	TYPE OF STORM:		FEB. 2022 - WINTER STORM
4	ANTICIPATED DATE OF ARRIVAL:		Thursday, February 03, 2022 @ 06:00:00
5	TODAYS DATE:		Wednesday, February 2, 2022
6						72	48	24	12	6	1
7					START TIME:	1/31/22 00:00:00	2/1/22 00:00:00	2/2/22 00:00:00	2/2/22 12:00:00	2/2/22 18:00:00	2/2/22 23:00:00
8					END TIME:	1/31/22 06:00:00	2/1/22 06:00:00	2/2/22 06:00:00	2/2/22 18:00:00	2/3/22 00:00:00	2/3/22 05:00:00
9		ASSIGNED	DATE	INITIAL WHEN COMPLETE	TASKS	72 HRS PRIOR	48 HRS PRIOR	24 HRS PRIOR	12 HRS PRIOR	6 HRS PRIOR	1 HR PRIOR
10	TEAM 1	AAAA			TASK 1	X
11		BBBB			TASK 2	X
12		CCCC			TASK 3		X
13		DDDD			TASK 4		X
14		EEEE			TASK 5			X
15		FFFF			TASK 6			X
16		GGGG			TASK 7				X
17		HHHH			TASK 8				X
18		IIII			TASK 9					X
19		JJJJ			TASK 10						X
Sheet1

Cell Formulas
Range	Formula
F7	F7	=$F$8-6/24
G7	G7	=$G$8-6/24
H7	H7	=$H$8-6/24
I7	I7	=$I$8-6/24
J7	J7	=$J$8-6/24
K7	K7	=$K$8-6/24
F8:K8	F8	=$C$4-F$6/24

 

[6StringJazzer]

It is possible to do something like this in Excel using VBA. A couple of things to understand up front:

For this to work, Excel must be running.
Ideally this particular file must be open in Excel (there might be ways to get around that but I don't recommend that)
You will have to have email addresses in the file
I assume you will use Outlook to send the email
You must have a way to indicate that the email has been sent, to avoid sending it repeatedly

You must decide what you want to use as a trigger for checking whether it's time to send any particular email. For example, it can check them all when the file is opened. If the file stays open for a long time (>1 day) a timer can be set to check once a day, or twice a day, or whatever you want.

Another possibility might be that when the file is first opened, timers are set for all the emails that haven't been sent yet. (I haven't taken that approach before but it should work pretty well.) The problem with that is that the timers stay alive as long as Excel is open, but if the file itself does not stay open in Excel then the timers will barf when they try to get the data from the file to send the email.