+ Reply to Thread
Results 1 to 9 of 9

Need Excel to calculate days and then send email based on outcome

  1. #1
    Registered User
    Join Date
    02-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    Need Excel to calculate days and then send email based on outcome

    Hello,

    I've struggled with this for several days and have finally decided I can't figure it out. Any help would be greatly appreciated as I am supposed to have this figured out by tomorrow (Friday). Here we go....

    I need for column D to be subtracted from the day it is =today() and the outcome in days to be put in column E. If there is no date in column D then the return should be 60. When the days in column E = 120, I need it to send an automatic email to the addresses in column L, M and C. After that I need it to send an email every 60 days until a date is put in the received column F. Last but not least I need for Column C to be linked to the "CQI Schedule" file column C so that the C column in the "PandPupdate" file are updated. My understanding is that the email option won't run unless the file is open so I'll have to figure out how to get the windows task scheduler to open the file, but at the moment that is the least of my worries. I've attached the two files I need help with. As you'll probably be able to tell, I tried to use Ron Debruin's code for auto sending the emails. It worked great till I had to add all the other stuff.

    Thanks in advance for anyone's help. You'll possibly be saving my job. I definitely got in over my head this time.

    Tara
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    Hi there,

    Try these.

    When the macro is run, it will start a cycle and generate 1 email for each person that is:
    at 120 days, 180 days, 240 days, etc. unless there is something in the received column.

    Let me know if that works.CQI Schedule.xlsxPandPupdate Try 1.xls

    Note: If this is a viable solution, you'll have to drag formulas down. Also, you might have to adjust the formulas in column N so that it will reflect all the addresses you need. column p is only used when the macro is run and is used as a flag so that the it will generate emails for the next person.
    Last edited by zoudazou; 03-29-2012 at 08:08 PM.

  3. #3
    Registered User
    Join Date
    02-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    First of all I can't thank you enough for your help. I really appreciate it.

    I think it may be close. I need it to look in column C, J and L for the email addresses to send it to and it needs to automatically send the email once column E hits 120 and then 60 days there after until something is put in column F. Something neat that Ron Debruin's code had was that it would populate the Mail Send column (in this case columns G,H, and I) if the mail was sent or not and if it was sent, it wouldn't send it again every time the macros was run. Everything needs to auto run when the spreadsheet is opened (which I have to figure out how to make Windows Scheduler do). The people that will be using this spreadsheet are even more Excel or VB illiterate than I am. You can change the spreadsheet however you need to. I deleted the columns that just had 60 in them since it didn't look like they were being used. Oops... I also need for the message to grab the info from column A and B.

    Thanks again!
    Tara
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    Do you have excel 2007? I noticed there was a compatibility error when I saved it.

  5. #5
    Registered User
    Join Date
    02-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    Yes I do. It's probably because I started off with Ron's sheet. Must have been 2003.

  6. #6
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    -------G--------H----------I---------
    120 Days 60 Days 60 Days

    Are these markers for whether or not the mail has been sent after 120 days, then sent after 180 days, then sent after 240 days?

  7. #7
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    Try this.

    The macro will run as soon as you open the book. It also records which day the mail was sent at. It will not send mails if there is anything in column F. The user must update the links when opening the book before the macro runs in order to have up-to-date info in columns that are linked to the CQI Schedule doc. There is likely a macro that can do that, but here is what I've got so far.

    Also, you can go into the code (which is located in "This Worksheet", not in a module) and change .Display to .Send if you want the emails to send automatically without the user having to press send.

    Is there a specific thing you need in the subject line?

    The subject line is taken from one of the cells in column N.

    Let me know if there's anything else that I forgot or that would be super cool to add.

    Edit: I just realized that it won't send emails every 60 days after the initial send.
    Paste this formula into K2 and drag it down.

    =IF(AND(OR(G2="Not Sent",G2<E2),F2="",E2>100,MOD(E2,60)=0),ROW(),"")

    This will make it so that it will send if E2 is greater than 100 days, and if the number is divisible by 60 evenly, and if Column F is blank and if either column G says "Not Sent", or the value in column G is less than the value in column E.
    Attached Files Attached Files
    Last edited by zoudazou; 03-30-2012 at 03:20 PM.

  8. #8
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    The code to update links is this:

    Please Login or Register  to view this content.
    Just paste that at the beginning of the code and it should update the links without the user having to do so. Just make sure that the location of the document is correct.

  9. #9
    Registered User
    Join Date
    02-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Need Excel to calculate days and then send email based on outcome

    I saw this post after I sent the email. Thanks so much. My email has a few other little issues.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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