1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

E-mail reminders from Excel

Discussion in 'Business Applications' started by Bradford83, Dec 30, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Bradford83

    Bradford83 Thread Starter

    Joined:
    Dec 30, 2009
    Messages:
    3
    Hi im trying to sort out a system for work that allows me to receive e-mails about 2 weeks before a piece of equipment is due to be calibrated. This runs on a yearly cycle but we have enough equipment to be doing this every week. I have set up an excel sheet with all the information on and currently have to scroll through this all manually which is time consuming. I noticed the thread

    http://forums.techguy.org/business-applications/636786-set-email-reminder-base-excel-3.html

    And this sounds like what I am after but I have no idea what any of the info means or where to start, can anyone help me please.

    Or does anyone know of a good free software for asset tracking and data logging???
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to the board.

    "I have set up an excel sheet with all the information on and currently have to scroll through this all manually which is time consuming"

    If time-consuming is the issue, why not just add a "Reminder" column, populate it with formulas such as

    =LastDate<=(TODAY()+14)

    and Autofilter on that?
     
  3. Bradford83

    Bradford83 Thread Starter

    Joined:
    Dec 30, 2009
    Messages:
    3
    Time consuming is an issue, but I want it to generate an e-mail so that a group of colleagues can see it.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Right.

    I set up a test sheet with several dummy rows & a "Reminder" column.

    I filtered on the "Reminder" column, then clicked the Email button on the standard toolbar.

    The "To/Cc/Subject" fields opened up. I clicked on "To" & selected a dummy recipient (i.e. me). I guess a group (of colleagues) to send to could be set up fairly easily, one time -- depending on your client.

    Then I clicked "Send this sheet" and received a mail with just the filtered rows in the body.

    If there's something particularly complicated about the above, I guess I'm missing it.
     
  5. Bradford83

    Bradford83 Thread Starter

    Joined:
    Dec 30, 2009
    Messages:
    3
    so will this just send an e-mail with the equipment due to be calibrated when excel realises its date has been reached??? or does this just send the full sheet?? sorry I might be being completely dumb lol, ill have a go with your suggestion and see what it does.

    Cheers for your help
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    As stated before, I clicked "Send this sheet". Nevertheless, "does this just send the full sheet?" raises an important point.

    Rows can be filtered or hidden. Columns can be hidden, but (unless they reinvented the wheel with Excel 2K7) can't be filtered.

    So if you have a whole bunch of columns, chances are the filtered rows in your email will be all out of synch wrapping-wise and just look pants, basically.

    Even so, there's an easy fix without VBA -- a pivot table.

    Open the attached. Column C of "Records" has formulas liked I mentioned. The pivot table is on "Pivot"; you can see from the dropdown @ B2 that you can flip between showing items on "Records" where "Reminder" = TRUE or FALSE. One "fancy" formula is used in the construction of the pivot table, so not a big deal.

    Make sure Pivot!B2 is set to TRUE (A,B & C showing), then switch back to "Records". Change DueDate for ItemC to 21/01/2010 -- "Reminder" changes to FALSE.

    Switch back to "Pivot", you'll see that nothing has changed (A,B & C still showing). Not to worry; save the file, close it, re-open it. Now ItemC's hidden because the pivot table's "Refresh on open" option is "turned on".

    So, to sum up: you could hide rows 2:5 + column B on "Pivot" & mail that (sheet) to your group as & when. & they'll just see a bog-simple "Items due for calibration" table in the body. But as to "when excel realises its date": well, you do have to open the file, switch to "Pivot" and click the Email button. Otherwise they could, like, fire you or something. ;)

    HTH :)
     

    Attached Files:

  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/889493

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice