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 sending automatic email to owners near due dates

Discussion in 'Business Applications' started by saif0000, May 26, 2016.

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

    saif0000 Thread Starter

    Joined:
    May 26, 2016
    Messages:
    7
    Hi, I have a risk register which has action by dates for owners of that particular risk. I would like to set up a VBA code that automatically sends each owner an email reminder, through outlook, 5 days before the action date. One owner may have multiple risks due on the same date so I would like the code to send 1 email detailing all the item numbers that the owner has to look at (however this is not at the top of my requirements if it causes issues). I would like the email to include the "Number" (Col. A) in the body listing which risks the owner should look at along with a message reminding them that the action by date is near. Also i want this to be automated, I want the code to run once and send emails as time goes on. Also i will need to change the action by dates as new information is available, can the code account for this change and send another email when the new action by date is near? Thanks for your help I really appreciate it. Using excel 2010
     

    Attached Files:

  2. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Try the attached solution.

    How this works:

    Seeing as you wanted to group all the actions per owner into one email, I had to use sheet2 to build the email strings up for each owner. The code will add each owner from sheet1 into sheet2 column A (removing duplicates)

    it will then go through all the dates on sheet1 and if they are 5 days or below it will find the owner on sheet2 and add the string in column B

    I have used a vlookup within the code so that for each owner on Sheet2 it can find the correct email address from sheet1

    once all the above it done, it will generate an email for each owner with their actions.

    currently the emails are all displayed rather than sent automatically, you can change

    .Display to .Send and they will be sent without displaying.

    Hope this helps.
     

    Attached Files:

  3. saif0000

    saif0000 Thread Starter

    Joined:
    May 26, 2016
    Messages:
    7
    Thank you very much, greatly appreciated.
     
  4. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Not a problem I assume it worked? if so can please mark the thread as solved

    otherwise let us know if there are changes you require.

    thanks,
     
  5. saif0000

    saif0000 Thread Starter

    Joined:
    May 26, 2016
    Messages:
    7
    If i change it to .Send then will it send a reminder each time I open the spreadsheet even if a reminder has already been sent? And if I change the action date within the spreadsheet will it automatically send the email when the new date approaches?
     
  6. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    if you change .Display to .Send, every time you open the workbook it will send and email automatically. If you don't want this to happen, then there is a solution. What we can do is enter a date stamp of the last time the email was sent, so for example if you open the workbook today and the emails sent out, the date stamp would be "27/05/2016" now the next time your workbook opens, the first thing it will do is check the date stamp if the date is todays date, it will not send any further emails.

    Do you want this implemented?

    What do you mean by this?

    Do you want the email today sent immediately as you change the date or are you asking whether it will send the email the next time you open the workbook?

    if you want it to send the email immediately after changing the date, then do you want the code to send the email ONLY to the owner of that particular risk or do you want all emails to be resent?
     
  7. saif0000

    saif0000 Thread Starter

    Joined:
    May 26, 2016
    Messages:
    7
    Sorry if i didn't explain it better. What happens is that we have a meeting where everyone discuses their particular risks on the register, afterwards dates are put into the register by which actions have to be taken (action date) if the risk is no longer apparent then the item is taken of the list but new developments occur the action date is moved forward. So for example the action date of a risk is 1.06.16 and we have a meeting tomorrow where it is changed to 15.6.16 I want the email sent 5 days before on both occasions.

    Also, if i use the .Display feature is there a way of me knowing if I have already sent a reminder to someone the next time i open the sheet in order to not send duplicate emails to the same person
     
  8. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Ok lets take the attached and work from that so we can understand what the requirement is, as, as of right now i am a little confused.

    Currently i have 7 risks in this workbook all with the 'Action by date' as 30/06/2016

    Now when you open this workbook today (27/05/2016) nothing should happen as the date is in the future 34 days from now.

    Now you have a meeting and decided that risk 4 has to be bought forwards, so you open the workbook (nothing happens) and you change the date for Risk 4 to 01/06/2016

    This new date is in the future 5 days from today...

    So here is the question.

    As soon as you change the date do the emails trigger OR do you want it to trigger the next time the workbook is open?

    because in this instance an email will be generate for risk 4 as it is within the parameters you specified (due in 5 days).

    We can do two things:

    1) When you change the date send an email immediately IF the date is within the specifications.
    2) Do nothing until the workbook is opened next.

    Now you mention the due date of 01/06/2016 of course, the code will send an email for this as its due in 5 days, BUT if you change the date to 15/06/2016. Nothing will happen until 10/06/2016.

    Lets focus on this first so we can nail one item at a time.


    let me think about this
     

    Attached Files:

  9. saif0000

    saif0000 Thread Starter

    Joined:
    May 26, 2016
    Messages:
    7
    For the first part I would like the emails to be sent the next time the worksheet is opened and only to the owners I changed the actions dates for. Thank you.
     
  10. saif0000

    saif0000 Thread Starter

    Joined:
    May 26, 2016
    Messages:
    7
    I populated the sheet and am now getting a Run time error '13' mismatch with the following lien highlighted ,

    If DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(BCell.Offset(0, -1), "dd/mm/yyyy")) <= 5 And DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(BCell.Offset(0, -1), "dd/mm/yyyy")) >= 0 Then
     
  11. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Ok I have made the following changes

    When an email is generated to an Owner, a date stamp is added to column P
    If you open the workbook again on the same day the emails will NOT trigger again

    if however you change a date in column 'L' it will reset all the date stamps for ONLY that owner. so for example in the attached we have

    Code:
    Owner              Action Date                  Date Stamp
    
    A                       01/06/2016                  27/05/2016
    
    A                       01/06/2016                  27/05/2016
    
    B                       01/06/2016                  27/05/2016
    
    C                       01/06/2016                 27/05/2016
    
    B                       01/06/2016                 27/05/2016
    
    A                       01/06/2016                 27/05/2016
    
    D                       01/06/2016                 27/05/2016
    Ok so when you open the workbook nothing will happen as the date stamp is todays date.

    If after your meeting you decided that the first item for owner A has to be changed to 31/05/2016. All of the date stamps for owner A will be cleared. So from the list above 1, 2 and 6 date stamps will be cleared - this means when you reopen the workbook it will generate the emails as there will be no date of last sent.

    Code:
    Owner              Action Date                  Date Stamp
    
    A                       01/06/2016                 
    
    A                       01/06/2016                 
    
    B                       01/06/2016                  27/05/2016
    
    C                       01/06/2016                 27/05/2016
    
    B                       01/06/2016                 27/05/2016
    
    A                       01/06/2016               
    
    D                       01/06/2016                 27/05/2016
    Only owner A will receive an email
     

    Attached Files:

  12. 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/1171902

  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