limit emails to one batch per day

    I have a w book that generates and auto sends emails depending on the due date and
    if yes is in column k in all sheets.
    It sends the email to the email in the code and what is in column e.


    This is saved on a shared drive as more than one person needs to open it.


    The problem is that it generates an e mail(s) each time it is opened , so if 3 different people open it
    and there are 10 overdue items it would send 30 e mails.


    What i would like it to do is to send the first batch of emails when the file is opened then may be
    add todays date in another cell so that if it is opened on the same day no further mails are sent, but
    if it is opened on the next day it would check the date realise either the date is blank or post and send
    another batch of emails and so on.


    For some reason i could not past the word book but i have added a drop box link and my code is below


    https://www.dropbox.com/s/dspa…pread%20Sheet%20test.xlsm





    Any help would be appreciated


    Cheers


    Peter

    Re: limit emails to one batch per day


    Change the start of your code to:


    Code
    For Each w In Worksheets
        If w.CodeName <> "Sheet2" And w.[a1] <> Date Then
            strto = ""


    and the end of your code to:


    Code
    End If
        [a1] = Date
    Next w
    Application.EnableEvents = True
     
End Sub


    I have used cell A1 as the cell to use to place the current day's date - change this to suit your worksheet (must be the same cell in each sheet.


    Also note you have an error in your code, change:


    Code
    .bcc = "[EMAIL="[email protected]"][email protected][/EMAIL]"


    to:


    Code
    .bcc = "[EMAIL=""[email protected]""][email protected][/EMAIL]"


    Extra quotation marks were needed.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    Choose a cell somewhere to hold the date. I'll assume cell Z1. Then, at beginning of code:

    Code
    If Date <= Sheet2.Range("Z1").Value Then 
'Already ran
Exit Sub 
Else
'Add flag for next time
    Sheet2.Range("Z1").Value = Date
End If
'Rest of code...

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

    Re: limit emails to one batch per day


    Almost 'Snap', Luke!


    My code has an extra safeguard, in so much as , in the unlikely event of the first computer to open the workbook on any day, crashes while the code is running not all emails would be sent.


    However when the workbook was reopened by that, or any other computer, your code would mean that all e-mails would appear to have been sent.


    My code would send any unsent e-mails should such a crash happen.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    Thanks guys for the quick response


    Looks much simpler than i was trying :) i will apply and let you know


    will it allow any update made or will any changes wait until it is opened the next day ?


    Cheers Peter

    Re: limit emails to one batch per day


    Both codes will limit e-mails to one batch per day - exactly as your title requests!


    Any changes to the sheets will get e-mailed the following day, assuming the workbook is opened on that following day.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    hi Kjbox


    Yes both codes work well thank you both,


    the update query was at the moment if the job is done the person would
    enter y in the box then the number of days until next check, at that point the date would change straight away (which it still does) and the due date text changes once the wb has been closed and re opened, i notice that the text does not change on re opening now and wondered if it would the next day???



    cheers


    peter

    Re: limit emails to one batch per day


    I cannot test this or reproduce the error you are getting because when downloading from dropbox, I got repeated messages to say that such and such a sheet contained errors and did I want to continue with the download. I clicked 'yes' each time and the workbook did download, but without any macros!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    hi
    I have managed to up load a w book


    Not sure which of the codes i have added to this version that you guys sent,



    Both will limit to one batch of emails per day looking at the date in sheet 2 j1 ( all good)


    The problem is that the operator should enter a y in H when he has done the task and a box
    opens so that he can add the number of days until the next inspection(this is only so they dont enter
    an incorrect date type in F) this then changes the date in F.


    The text in G then should update once the book is saved and re opened showing number of day until due or
    overdue, this now will not update untill the next day,which means if some one open the file it might look as though the task is still
    outstanding.


    I have tried puttiing the code in different places but this sems to stop the y drop box function.


    Is there a way around this? or is there away to have the text update straight away when the date is changed)


    hope this makes sense


    Peter

    Re: limit emails to one batch per day


    Hi,


    There were a number of things wrong.


    You used LukeM,s code, but did not use it correctly, it was meant to be added at the start of your code and you added it at the end! That meant that when the workbook was opened and J1 on sheet2 was either empty, or not today's date, or even today's date, the Workbook_Open event code ran. You had written the code in such a way that Events were disabled at the start but even though you had code there to enable events at the end, that line was being skipped so events remained disabled. Therefore the Worksheet_Change Event macros for each sheet could not run.


    Here is the code for the Workbook_Open Object module corrected:



    Note: I have made a change to the 'Date Difference' section of the code, you had the first IF staement as If ddiff >0 , I am certain that should have been if mdiff>0, so I changed it.
    Also this bit of code:


    Code
    For j = 1 To UBound(sn)
                 If j = 1 Then frRowCol = " bgcolor=#a9bcf5 " Else If Application.IsOdd(j) Then frRowCol = " bgcolor=#f2f5a9" Else frRowCol = " bgcolor=#f5ecce"
                 c01 = c01 & "<tr" & frRowCol & " ><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"
          Next j
          c01 = c01 & "</table><P></P><P></P>"


    assigns values to the string variable c01 as the code loops through the array sn, then c01 is immediately replaced with a new string. I do not know which string is the one you want, you can delete the line for the one you do not want.


    I have also amended the code for the Worksheet_Change event modules, the new code is:



    using

    Code
    LCase(Target) Like "y*"


    means that if the user enters 'Y' or 'y' or 'Yes' or 'yes' the code will run.


    Replace existing code in Workbook and each Worksheet object modules with the above codes.


    Hope this works for you.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    Hi Kjbox


    Thank you for tidying up the code it has been messed around with trying to get it to work.


    i Have attached a new wbook with your amended code, but for some reason it is not populating j1 with a date
    Also if you look at the last entry on the fire sheet it still says due today even though the due date is 07 it
    now wont update until todays date changes.


    Is there a more simple way to have this due date text populate as the next due date is entered ??


    once again thank you very much for looking at this.


    Ps Would i put your code in the same place? as you sasid it had a crash back up.


    Peter

    Re: limit emails to one batch per day


    Sorry, my mistake at start of workbook code. Delete existing code and replace with this:



    Today's date is now added after all the e-mails have been sent.


    Delete code in each Worksheet_Change Event module and replace with this:



    I have changed the message box to either force an entry of Number of Days, or to cancel if "Y" was entered by mistake.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    once again Thank you for this.


    Have you any thoughts on the status text update, before it used to up date if the sheet was saved and re opened so
    it was clear that the task had been done or not.


    It now waits untill the date change the following day which could confuse the issue.


    It would be ideal if it could responed to the date change as its inputted but dont know if this is possible


    Cheers


    Peter

    Re: limit emails to one batch per day


    OK I understand what you mean now!


    However, it is midnght here and I am half asleep, can't sort it now. I will do it first thing in the morning and so should have a solution ready for you when you get to work tomorrow.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    The input box asks the user to "Please enter the number of days from the previous due date for the next inspection."


    Why don't you have another column in your tables for the date of last inspection?


    Then the due date for the next inspection column could populate automatically, and the status column automatically update every day.


    If this is a possibility let me know and I will code accordingly.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    Hi Peter,


    Attached is a workbook with a copy of the Fire sheet modified and automated, and the Keep As Last Sheet sheet (Renamed Summary. Codes also changed to suit.


    I have added notes about what I have done, and how to use the sheets, on the Fire sheet.


    Have a play around with it and let me know if all OK, or any changes needed.

    Re: limit emails to one batch per day


    Have you looked at the file I uploaded in post #17? I put the extra column in the same place.


    I forgot to put in the notes that the column for Frequency as days and the lead time column could be hidden as they are used only for the formulas and do not need to be in view.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: limit emails to one batch per day


    Hi Kjbox


    Wow that looks good, i will transfer some data onto this as see how it goes, and let you know.




    Thank you very much


    Peter

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login