Excel due date alerts via outlook email

[Deborinski]

Hi, is there a macro that can send email alerts via Outlook when a due date is reached or passed on an excel spreadsheet without having to open the actual spreadsheet?

Please see example of spreadsheet attached

[dflak]

We need a bit more information. How is this file going to be processed? Is this just a single file or are you going to have a whole series of them? Will the due date always be in cell J6? Exactly what do you mean by "without opening the actual spreadsheet?" Do you have Microsoft Outlook? (It may work "cleaner" than a web mailer).

Here is what I think can be done. You can run a master spreadsheet. It will open the files in the background and get the due date. We'll need help on what you want us to parse out for the salutation. In the example, this appears the be the third element of an array based on a space delimiter - in other words, "OMAR." Can we depend on this?

You can either run the master program manually or have it kicked off by the windows task scheduler.

[Deborinski]

Hi dflak,

Apologies for the lack of information provided. I am not very proficient in VBA or Macro

We need a bit more information. How is this file going to be processed? - the file will be a manual input file)
Is this just a single file or are you going to have a whole series of them? - Yes there will be a series of these - generate approx 5 per day Will the due date always be in cell J6? - Yes
Exactly what do you mean by "without opening the actual spreadsheet?" - as there will be an accumulation of these over time the user wants to be alerted that the due date is due or expired without opening each spreadsheet.
Do you have Microsoft Outlook? - Yes it is our companies email (It may work "cleaner" than a web mailer).

Here is what I think can be done. You can run a master spreadsheet. It will open the files in the background and get the due date. We'll need help on what you want us to parse out for the salutation. In the example, this appears the be the third element of an array based on a space delimiter - in other words, "OMAR." Can we depend on this?
- Yes to Omar

The main aim of this alert is to alert the creator/user that this customers job is due and the creator/user needs to follow up on the status of this job, so I guess an email to the creator/user is not required, but some type of an alert

You can either run the master program manually or have it kicked off by the windows task scheduler.

[dflak]

Here is how this program works.

On the control panel sheet:

Cell B2 contains the directory path to the files to be processed. The program will process every XLS-like file in this directory.

Cell B3 contains the directory in which to place the files after they are processed so they don’t get processed more than once.

Fill these cells in with the directories you want.

This program reads the files and picks out the appropriate data and writes it to the table on the data sheet.

Then after it processes the current batch of files, it reads the table to find records with no dates in column E. These are records for which a message has not been sent yet.

The program then looks at the date in column A and decides if the date is due or overdue. If it is one of these statuses, then it mails the message to the indicated address and puts the current time in column E to indicate that the message was sent.

If you feel like it, you can clean this table out periodically by selecting the rows with dates in Column E and deleting them.

The program does this manually when the button is clicked. If you have admin rights to your computer, you can set it up so that this report will run in the background automatically, periodically during the day.

There may also be a way to open the program in the background and put in some timing loops, but this program would have to be run manually to kick it off. The task manager would be the way to go if you can do it.

Let me know and I can give you detailed instructions, with screen captures on how to set up the task manager.

Also, you may want to look at this part of the code to configure what you want for subjects and message body.

Please Login or Register  to view this content.