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.

Solved: Excel Send E-mail (via Outlook) When Date Reached

Discussion in 'Business Applications' started by charli_2na, Nov 21, 2013.

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

    charli_2na Thread Starter

    Joined:
    Nov 21, 2009
    Messages:
    21
    Hi all,

    I know there has been a lot on this subject (so apologies) but as my knowledge in this area is limited and as I can't find anything that matches my exact needs I am struggling.

    Basically, as part of my job I manage records in the office but I sometimes forget when things needs renewing etc.

    What I would like is a for an e-mail to be sent through and to my outlook when a date has been reached.

    Please see the attached: CSCS.xlsx

    If CSCS tests had to be done every year for example, Paul Smith would be due another on 01/01/2014. I would like to set up a code so that when it got 2 weeks before that time, I got an e-mail saying 'CSCS Test due' and 'Smith' (i.e.. cell C1 and Column A for reliant person, in this case A3).

    Is this something that is do-able and easy to set up?

    Thanks, charli
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Charli


    Attached is a simple example of sending an email based on your dates. The email will be generated when the file is opened. The question is, will you open the file every day? If not, is there another file that you do open every day that has the same basic information i.e. Employee name and Expiry? If, not, you will need to set up a scheduled task although some companies will not allow you to do that.

    This routine will run every time the file is opened. The routine will check each row to see if the expiry check date has been reached and if it has will add that name to the email and will update the expiry date to the next year.


    You can view the macro in the ThisWorkBook module where you will have to put in your email address.

    If you are not comfortable with Macros you may want to consider using a calendar event (recurring every year) instead of sending yourself an email.



    When you click on the link choose "Save" instead of "Open" and then open it. If you want to perform more tests change the expiry dates to meet the send criterion.

    Give this a try and see which way you want to go.
     

    Attached Files:

  3. charli_2na

    charli_2na Thread Starter

    Joined:
    Nov 21, 2009
    Messages:
    21
    Thank you so much for your time and comments.

    This looks like exactly what I need and whilst coding and macros are no in my skill set, I think I should be able to work it out with your notes.

    Really appreciate your time, I will let you know how I get on next week.
     
  4. charli_2na

    charli_2na Thread Starter

    Joined:
    Nov 21, 2009
    Messages:
    21
    Hi XCubed,

    I have been playing today with the macro today and it will be just what I need :)
    I can run it when my laptop boots or write a script to open/close it each day which will send the e-mails etc. so thanks a lot.

    I do have some queries though if you have 5 mins - I have written some comments on the marco and as follows;

    1. What I would like is to just send the e-mails when the date is reached and then change the cell colour (rather than move the date on). The next time it runs it needs to ignore the cells that have the new colour. This way users can input dates and change the colour back to confirm it has been done.
    2. This will run for ‘CSCS Tests’ but I could do with the code to do the same for the next item ‘Check H&S Kit’ – hopefully I can then copy the code to do the others and any more I add.

    I hope these changes are do-able and thanks again for your help.

    Thanks, Charli
     

    Attached Files:

  5. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Charli


    Changes are included in the attached.

    I've coded it so that if you want to add a column for another check the macro will pick it up.
    I've un-merged the CSCS header so that I could label the email row appropriately.

    Test this with different combinations of people and expiry dates and see if this will do.

    Also, as it stands the email will be sorted by category and then person i.e. so that all CSCS expirees will be listed, then all H&S Kits etc. If you want to group by person then simply move the macro lines


    "For i = 3 to lRow"

    above the line

    "For col = 5 to lCol"


    and the Line "Next i" below "Next col"
     

    Attached Files:

  6. charli_2na

    charli_2na Thread Starter

    Joined:
    Nov 21, 2009
    Messages:
    21
    Hi XCubed,

    Perfect, I have now applied that my whole spreadsheet and it is running like a dream :)

    The final thing (!!) I would like to include is that it only runs the search for dates where row 2 = Expiry.

    The reason being (if you see the attached) we have records which are historic and don't need to be updated - like when they join the company ('start date'). If it only searched those where row 2 = Expiry I can store all the info. in one place.

    Sorry to ask for more help but this will be it!

    Thank again for the support, Charli
     

    Attached Files:

  7. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    ok give this a try
     

    Attached Files:

  8. charli_2na

    charli_2na Thread Starter

    Joined:
    Nov 21, 2009
    Messages:
    21
    Brilliant!

    Thanks XCubed :)
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'd strongly recommend you declare your variables (Dim). Start doing that as a habit and you'll wonder why you ever went without them. ;)
     
  10. 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/1113564

  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