VBA to send emails based on Expired Due Dates (with Conditional Formatting)

I noticed one thing about your workbook. Once an item gets overdue, it stays overdue. There is no completion date to "cancel it out." This means that if the insertion date is overdue and then you go on and complete the Judicial analysis, the Insertion Date is still out there and overdue. Eventually (5 business days), the Judicial Review becomes overdue whether it is completed or not.

How do you want to address this issue?

I think I got it. I made several improvements:

I converted the range of data into an Excel Table. Tables have a lot of advantages, but the main ones used here is that tables know how big they are and they automatically copy down formulas, formats and validations.

I added an if statement in each of the formulas so the cells do not show a due date unless the cell to the left of it is not blank. I think I have the conditional formatting worked out.

I have a lookup sheet with a table on it. You can fill out the owners names and email addresses here. This table is used for data validation and lookup. So if you add someone new, you do not have to change the data validation in Column D . The drop-down lists in Column B will always reflect what is in this table. Column E on the Control Sheet has a VLOOKUP formula to lookup the email address.

Column C in the Lookup table is used by the program,. It shows the number of overdue dates an Owner has. Once you fill out the owners and their email addresses, you can hide this sheet.

There is a Table on the Overdue List sheet. This is a summary of overdue dates. It might be a useful report in itself, but is also used by the program.

There is also a pivot table, the pivot table is used to select overdue dates by owner and sort them by Document. The information on this sheet is used for the body of the email. This sheet may also be hidden.

When you get a new document, type in the name in Column A on the row immediately below the table. In the attached, this is Cell A9. Since this is a Table, this new cell and its entire row are now part of the table. Formulas and conditional formatting are copied down.

Enter the data, click the button and the rest is automatic.

For more information on Excel tables, see this article: http://www.utteraccess.com/wiki/Tables_in_Excel.

So when the next step is taken, then the previous due date is not considered. This implies that for each row, there is a maximum of one due date in effect. I'll proceed with this assumption unless you tell me otherwise.

Owner_Filter is a named range on the Pivot Sheet. Go to the Pivot Sheet and select Cell B1. At the upper left of the spreadsheet under the ribbon is the Name Box. It shows the upper left cell of whatever range of cells are selected. In this case, it should display B1. Click in this box and type in Owner_Filter and then press ENTER. This is the easiest way to assign a static range, highlight what you want, go to the box, type in the name and press ENTER.

There is also another named range on this sheet. But this one has to be entered using the Name manager.

See this wiki on how to use the name manager to add dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges. You can also use the name manager to assign static ranges.

The original sheet also had a third range defined, apparently a link to an external workbook.

Here are the three ranges
Overdue_Range =OFFSET(Pivot!$A$2,0,0,COUNTA(Pivot!$A:$A)-1,3)
Owner_Filter =Pivot!$B$1
Tipo_de_Usuário ='T:\Users\F8064093\Desktop\Formulários\[Cadastro Safedoc.xls]Plan1'!$B$2:$B$4

In addition to Owner_Filter, create one for Overdue_Range as shown above.

The print is kind of small, but I think this is what is happening. The pivot table is attempting to read the data from the old spreadsheet.

Select the pivot table, go to Analyze, Change Data Source, and type in the local name of the table.

Glad it worked out for you.