+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    07-10-2018
    Location
    Rio de Janeiro, Brazil
    MS-Off Ver
    Windows 7
    Posts
    6

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

    Hello!

    I'm a complete VBA newbie, and I'm attempting to create what is for me a quite complex macro for my bosses and I. I've already done the visual part of the report, but they also want the document to send emails via Outlook when any documents have their due dates expired. I'm on Office 16 btw.

    In my mind, it would go as follows:

    1. The macro would check in a range of cells (from F5 to N) if any of the documents are past their due date, whether by checking the date or seeing if the conditional formatting rules I've added (red filling, bold letters) affected any of these cells;
    2. If true, the macro would collect the document number(s) from the cell(s) in column A to add onto the body of the email;
    3. And then fill the "To:" field in the email with the owner of the document(s).

    I also thought of an alternative in which they would hit a button to make this check, and it would prompt a box instead. If there aren't any expired dates, the box would tell them. Otherwise, it would give them the option to send via email the number(s) of the document(s) with any expired dates.

    I've already searched the web for solutions, and I couldn't find anything. Maybe it's something very specific, so I'm hoping any of you guys can help me.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: 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?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    07-10-2018
    Location
    Rio de Janeiro, Brazil
    MS-Off Ver
    Windows 7
    Posts
    6

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

    Hello!

    So, in the conditional formatting rules I added it demands that the document owner fills the next space that isn't a "due date" column so it stops being overdue, until it reaches the "Printing" stage. When I tested it, there was only one cell with the "overdue" formatting per row. It's supposed to be like this, so that these documents aren't forgotten in our workflow. Thus, I'm not sure if there are any improvements to be made...

    Thanks in advance!

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

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

    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.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

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

    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-10-2018
    Location
    Rio de Janeiro, Brazil
    MS-Off Ver
    Windows 7
    Posts
    6

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

    Thank you very much, dflak! This is just perfect for what I needed. I hope I can be as useful as you one day in this forum!

  7. #7
    Registered User
    Join Date
    07-10-2018
    Location
    Rio de Janeiro, Brazil
    MS-Off Ver
    Windows 7
    Posts
    6

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

    Hello!

    One further question: how do I make the tables in this workbook work properly on another one? I've already moved the tables from one workbook to the other, and edited the references so there are no errors in the formulas. However, when I hit the "Send Mail" button an error appears with this line of code to "debug".

    macro error.PNG

    As always, thanks in advance

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

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

    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.

  9. #9
    Registered User
    Join Date
    07-10-2018
    Location
    Rio de Janeiro, Brazil
    MS-Off Ver
    Windows 7
    Posts
    6

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

    Hey, dflak! Thanks once again for the help!

    I did as you told, and now the issue appears at the line of code below. From what I can gather, the macro is looking for information from the original table instead of the current one, and for it to work it demands that the original workbook is open. Below are the pictures to show it.

    20180720 1.PNG

    20180720 2.PNG

    As always, thanks in advance!

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

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

    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.

  11. #11
    Registered User
    Join Date
    07-10-2018
    Location
    Rio de Janeiro, Brazil
    MS-Off Ver
    Windows 7
    Posts
    6

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

    Well, now everything seems to be working fine.

    I can't thank you enough!

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

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

    Glad it worked out for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Expiry dates - to send automated emails
    By jake.jay in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-26-2016, 03:48 PM
  2. Replies: 7
    Last Post: 02-24-2016, 11:34 AM
  3. Send Emails once expiry date is reached, and generate report based on emails sent
    By demonicscorpion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2014, 05:36 AM
  4. [SOLVED] Conditional Formating expired dates
    By Sandra_Sada in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-13-2014, 04:44 PM
  5. [SOLVED] Conditional Formatting highlighting expired dates
    By scolsen in forum Excel General
    Replies: 4
    Last Post: 01-13-2014, 01:33 PM
  6. Replies: 5
    Last Post: 12-31-2011, 04:33 AM
  7. Conditional Formatting in VBA for expired dates
    By xcelnerd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2010, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1