Forum Discussion
Looking for a macro for automatic sorting by due date and emailing oneself when item is due
Hi everyone,
I am trying to see if someone could help me with making a macro that will automatically sort by closest due dates. In my table there are 2 columns one titled assessment date and due date. In my assessment date column it contains dates where paperwork was done. The due date column contains the day before but 2 years out (when the next round of paperwork is due). I have the due date column with conditional formatting saying that if todays date is past the due date then it is highlighted red. If the due date is within the 2 week mark of being due it is highlighted yellow and if it further out than 2 weeks then it is highlighted green. I wanted to see if there was a macro out there that will automatically sort by what is past due and if it is past due then it could email me to let me know so I can contact the client to fill out the paperwork. I tried finding other macros but the closest one that I could find just automatically sorts by the date not when an item is due. If anyone could help that would be great.
Thank you in advance!
- JKPieterseSilver Contributor
JaimeSanchezRamos In order for a VBA macro to work, your file needs to be opened in Excel. This means you will have to be logged on to your machine and open the file. Since you're opening the file anyway, you can then filter the column for the # of days remaining before the due date and see which are up for action. At that point, sounds to me like sending yourself an email isn't really helpful.
If you are using Microsoft 365 and your file is stored on either Sharepoint or OneDrive, you can write a small Office Script that filters your table for the remaining days. You can then create a power Automate flow and set it to run once a day to email the filtered table to you. That sounds more helpful to me 🙂