none
Email reminder sending on alternate days RRS feed

  • Question

  • Hi All!

    I have this code which I need it to send reminder emails out every alternate days according to the due date indicated in the cell.

    Relevant portion is inserted below. The code basically says that email will be send 14 days after due date.

              If DateDiff("d", Now(), Bcell.Offset(0, 13)) = -14 Then
           
               iTo = Bcell.Offset(0, 15)
                   
               iSubject = Bcell & " Overdue"
    I would like to ask if there is any way to simplify the code such that it will send every 2 days after due date until closed (which I have other lines that instruct it to stop).

    Thursday, January 5, 2017 1:49 AM

All replies

  • Hi SakuraiHiro,

    I have a confusion with your description.

    in the title you had mentioned that  "alternate days" and in the body you had mentioned that, "every 2 days".

    here I assume that you want to execute it on alternate days after due date.

    First try to swap the parameters in DateDiff function like below.

     If DateDiff("d", Bcell.Offset(0, 13), Now()) >= 14 Then
    

    so you will get the result in positive numbers.

    also add ">=" sign instead of only "=" sign.

    so whenever you run the code and if due date is gone then it will enter in the if condition.

    then you need to store the result of DateDiff function in variable.

    then use Mod Function. like below.

    Sub demo()
    If vardiff Mod 2 = 0 Then
    
    Debug.Print ("Executed")
          'place your code here
          'it will execute on alternate days
    End If
    End Sub

    Note:

    you did not mentioned that how you are going to call this macro.

    are you going to open this file everyday and run the code manually?

    if yes, then it will only send the mail when you run it. if you do not run the code then it will not send the mail by itself.

    if you want to run this code by itself everyday then you need to used "Task Scheduler" to call this macro.

    Regards

    Deepak



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 6, 2017 7:32 AM
    Moderator
  • Hi Deepak! Thank you for your response!

    What I intend to do is for the email to be sent every 2 days after the due date i.e. 2 days, 4 days 6 days... until maybe 30 days after the due date specified in the reference cell.

    and I did consider using task scheduler but it couldn't work to what I intend it to, also due to company`s policy, I cant place script in the company`s shared drive. so yea currently im opening it manually everyday. for those emails that falls on weekend, I will send them personally.


    • Edited by SakuraiHiro Friday, January 6, 2017 8:29 AM
    Friday, January 6, 2017 8:28 AM
  • Hi SakuraiHiro,

    First of all you need to run the VBA code everyday to check the due date.

    as per your current code if the difference between due date and current date is equal to 14 or more then that then it will enter in the if condition.

    I suggest you to create an another column beside due date which stores the date on which we will send mail.

    every time when it will enter in the if condition it will check this cell. if it match with current date then we will send mail to client. after that we will again set the date of after 2 days in the same cell.

    so when next time it will check then it always have a new date to match.

    another thing you need to do that.

    when it match with the current date at that time you need to find the difference between due date and current date. if it more then 30 days. then you not need to send the mail and go to the else part and instead of setting new date after 2 days. you have to set a status in that cell something like "after due date period over" and move to next cell to repeat the process.

    hope you will get the idea. this is my thinking to solve this issue.

    you can also try to implement your own logic that solves the issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 7:57 AM
    Moderator
  • I think I do get your Idea, but "I suggest you to create an another column beside due date which stores the date on which we will send mail." don't seems feasible. The intention was to sent every 2 days after due date.. up to 30 days (aka 2 days, 4, 6, 8...30 days) after due date. So if I include all the date to be sent then the excel would be very long, which on its own is already extend to more then 50 columns.

    So I was thinking if there is any formula or wordings like if

    If DateDiff("d", Bcell.Offset(0, 13), Now()) >= -even Then ...

    If not I could just copy paste the same thing just with different negative even values.. though it wil feel like its creating unnecessary coding

    Tuesday, January 10, 2017 12:45 AM
  • Hi SakuraiHiro,

    you had mentioned that,"I think I do get your Idea, but "I suggest you to create an another column beside due date which stores the date on which we will send mail." don't seems feasible. The intention was to sent every 2 days after due date.. up to 30 days (aka 2 days, 4, 6, 8...30 days) after due date. So if I include all the date to be sent then the excel would be very long, which on its own is already extend to more then 50 columns.

    So I was thinking if there is any formula or wordings like if

    If DateDiff("d", Bcell.Offset(0, 13), Now()) >= -even Then ...

    If not I could just copy paste the same thing just with different negative even values.. though it wil feel like its creating unnecessary coding".

    I think you did not understand what I want to tell you.

    you not need to save all the dates in new column.

    you just need to save only 1 date in a cell. that date is after 2 days.

    after 2 days when you match with that date and send mail then you need to set the next date in the same cell.

    so when you run the code again there is a date available of after 2 days.

    this cycle will run till 30 days.

    so there is no long list of dates. there is only 1 date.

    hope you understand my logic.

    if you think it is not feasible to you then you can develop your own logic.

    this is developer forum so we can only suggest the code to solve the issue.

    if you want formula then you can post your issue in forum below.

    Excel It pro discussions Forum

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 10, 2017 1:03 AM
    Moderator