+ Reply to Thread
Results 1 to 5 of 5

Showing overdue asset loans and then emailing users

  1. #1
    Registered User
    Join Date
    07-30-2018
    Location
    Dubai
    MS-Off Ver
    2016
    Posts
    13

    Showing overdue asset loans and then emailing users

    I have a sheet for managing x number of assets which are going on loan to customers. The attached is a concept and non actual data.

    I want to build a system to filter/show all loans which are overdue and then a function to contact these users by email

    This is the approach I have taken;

    I have generated data including simulated dates for out/return
    I have added a column "returned" - (Yes or No.)
    I added a column "late" which returns true/false based on the date being >today.

    I added a pivot table in a second sheet - this currently returns all instances where the return date is in the past.

    I have three questions;
    If the "returned" column (F) is "Yes" - then the asset has been returned and the Late column should show "false". I assume I need to nest IF functions to achieve this, but I do not know the correct syntax.
    Is it possible to automate an email to the users generated by the pivot table? (imagine there were usernames captured and not "person 1").
    The email would send to the names given, and the body would be the same apart from a reference to the asset in question which would be dynamic. Is this kind of thing possible in MS excel?

    If you want me to explain anything in more detail please let me know.
    Attached Files Attached Files
    Last edited by TommyTommyTommy; 08-12-2018 at 11:45 AM.

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

    Re: Showing overdue asset loans and then emailing users

    This spreadsheet sends an email if Late is True and the item has not been returned and a notice has not previously been sent.

    It does not reference the pivot table. It uses the Excel table. Note: if you move the columns around, you may break the code.

    When you send a mail, the Notice Sent Column gets date stamped so a notice will not be sent in the future. If you want to turn this feature off, comment out this code"
    Please Login or Register  to view this content.
    Make sure you clear out data in the Notice sent column.
    Attached Files Attached Files
    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-30-2018
    Location
    Dubai
    MS-Off Ver
    2016
    Posts
    13

    Re: Showing overdue asset loans and then emailing users

    Quote Originally Posted by dflak View Post
    This spreadsheet sends an email if Late is True and the item has not been returned and a notice has not previously been sent.

    It does not reference the pivot table. It uses the Excel table. Note: if you move the columns around, you may break the code.

    When you send a mail, the Notice Sent Column gets date stamped so a notice will not be sent in the future. If you want to turn this feature off, comment out this code"
    Please Login or Register  to view this content.
    Make sure you clear out data in the Notice sent column.
    This is great thank you for your input. Very powerful and it helps me understand the capabilities of Excel. Did you write the modules yourself? "ModMailer" "ModOverdue"

    I am new to excel VB and I need to learn this. I would be capable of modifying the code you wrote to reference a column with email addresses stored but I am hopeful there are shortcuts to learning fast.

    Is there a tutorial series on the main Visual Basic functions that you would recommend? Or, is there sample code out there with every line commented out so you could reverse engineer? Is there a shortcut in VB so I can select a range of code and break it down or see what it does - for instance I highlight where it says "DIM" "String" and then look up what it is short for?

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

    Re: Showing overdue asset loans and then emailing users

    ModMailer is something I do a lot, so I wrote the code in such a way as to pass parameters to it. I rarely have to touch the inside of the code itself. I do this with a lot of functionality. For example, I make extensive use of Excel Tables and I often have to clear old data out of them. So I have a module that actually has two macros in it. One of them clears the filters on the table, and the other uses this macro as part of clearing the table. So I pass a sheet name and a table name to ClearFilter or ClearTable depending what I want to do.

    So when it comes time to program a larger application, I pull these pieces "off the shelf" and bolt and stitch them together like Dr. Frankenstein with bits and pieces of VB code.

    The good thing about putting code in Modules is that you can Export the module to an Excel folder to a *.bas file. You can import BAS files into your code. I have a folder that has dozens of modules that do different things, ModMailer is one of them. As for ModOverdue, that was the custom part for this application.

    As for recommending a VB tutorial or reference book, I suggest that you ask that question in the Water Cooler forum on this board. I have experience with other coding languages, so I never studied VBA formally. I get some of the knowledge from the VB macro recorder which at least gives me the syntax I need to do a lot of things. The macro recorder writes sloppy code, but it can often be "cleaned up." Also I stand on the shoulders of giants. I've gotten a lot of help here and on other fora.

    The three pieces of advice I give people who are getting into VBA are:

    It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
    Please Login or Register  to view this content.
    This option requires all variables to be declared and will give a compiler error for undeclared variables.

    While you are there, also uncheck Auto Syntax Check - if you have a syntax error, it will appear in red in the VB editor. If this box is checked, you have to clear a dialog box before you can leave the line. Sometimes you want to copy / paste bits for code, and the dialog box makes the process more cumbersome.

    The second piece of advice is always tell Excel where you want it to be. Your idea of active cell, active sheet or active workbook may be different than Excel's.

    Finally, if you use the macro recorder and it produces code like:
    Please Login or Register  to view this content.
    You can almost always replace it with
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-30-2018
    Location
    Dubai
    MS-Off Ver
    2016
    Posts
    13

    Re: Showing overdue asset loans and then emailing users

    Hey dflak, I've learned a lot from the vba code you shared a while ago, so thanks very much for that.

    Question on a dashboard/Reporting

    I have data for asset, user, team & overdue status. I'm thinking about how I could best present this in a dashboard.

    Do I start with a Pivot table?

    I can make something like this;

    Capture.PNG

    I guess that will do? (i can format to make it look good/remove "grand total" etc)

    Or, I can show a count;

    Capture2.PNG

    Is this all best practice? I want to be able to show on a dashboard the total number of overdue assets, with the ability to "dive in" and see the users/team breakdowns. Do I just need to go away and learn pivot tables more? I'm looking for a push in the right direction before I invest time into this.

+ 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. Automate Macro script needed to Mark Overdue ,SLA-HOLD,Soon to be Overdue by Hours
    By britishidol200 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-04-2015, 12:55 PM
  2. How to prevent users from Showing Formulas
    By WillYoung351 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2015, 04:34 PM
  3. track changes not showing as strikethrough for some users
    By meghanalissa in forum Word Formatting & General
    Replies: 4
    Last Post: 05-09-2014, 05:06 AM
  4. Formula to determine total overdue values by length of overdue
    By Midnight_Dragon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 02:03 AM
  5. Prevent users from emailing workbook
    By VBA FTW in forum Excel General
    Replies: 3
    Last Post: 12-17-2012, 12:43 PM
  6. 'Force' users to enter data in all cells before emailing
    By Ducatisto in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-07-2011, 10:44 AM
  7. [SOLVED] Column showing "overdue"?
    By StargateFanFromWork in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2005, 01:05 PM

Tags for this Thread

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