help with VBA conditional notification emails

I am new to VBA coding and I have tried to figure this out but my head is starting to hurt so I thought I would ask for help. I want excel to automatically send an email to multiple recipients when the formula in R26 finds the information that meets the correct criteria and populates with data but not when it goes back to an unpopulated cell. I have found a lot of different codes that send different types of emails but I am having a hard time figuring out what each line does and I am not sure how to adapt it to what I want.

that meets the correct criteria and populates with data but not when it goes back to an unpopulated cell.

Does the macro need to check the data for accuracy prior to sending the email/s ... or, can the email/s be sent anytime the cell has data within ?

Please Login or Register  to view this content.

Thank you so much for your help. I will try it out the next time I go in to work

alright so I put in the code but nothing happened I tried F5 and ALT+F8 but the option to select it was not there am I missing something?

The macro goes in the Sheet level module. Do not paste it into a Regular module.

Then, when R26 changes from BLANK to anything, the email macro fires.

okay so now the email is sending but it is doing so every second even though the cell has nothing in it but the formula. The formula in R26 does reference the current time which seems to be what is effecting the email so every time the clock updates the macro sends the email rather then when the cell populates with a value. Is there a way to work around this problem?

What is pasting the time into the cell R26 ?

Give me the entire code.

Formula:

Please Login or Register  to view this content.

The formula presents a very unique issue concerning the sheet level macro.

The code line in BOLD solves this problem but also prevents the macro from running again until cell A1 has been cleared of data. Let me know what step/s are required next.

Please Login or Register  to view this content.

I understand your description in your last post. Now ... explain to me what you are expecting your workbook to do and break it down in steps. Also what is the goal ?

I work at a security company and some of our onsite officers are not showing up to their posts. My boss has asked me to put something together so that my department will know what sites to call at the beginning of each shift. I put a table together that has all of the sites listed and the times they start (that page is currently hidden). I then made a a reference section on the first sheet of the workbook where the current day and time are listed so that when the current day (cell p26) matches the specified day in the table (so today it would look for Thursday) it will then look up the current time (cell P28) and if that matches the start time of the shift all sites with that time will show in cell R26 and below (so usually 1- 4 sites). knowing my coworkers they are not going to pull up the sheet every 30-60 minutes just to see if it has been populated so I am trying to get it set up so that when those sites get listed an email will be sent to both sides notifying them that there are sites to be called. The thing is I need it to be doing it all automatically, so P28 will update when a change is made in the worksheet but they are not always in there to look things up (I also have the officers and their phone numbers listed in the worksheet for easier access) so p28 can't update frequently enough but I have found that if I have the digital clock running it will update P28 automatically making the rest of it work. Now I just need the notification portion of it to work on it's own making it pretty much user friendly.

So how often should the email be auto sent ?

Once per hour .... three times per hour ... once every 2 hours ?

I thought i had hidden the confidential information, thanks for letting me know. that's the trick I only need the one email sent at the start of each shift every day but the time frames very each day. for instance some days the start time might be 1:00am, 07:00am, Noon, 12:30, and 02:00pm and the next there is no noon or 02:00pm but a 03:00pm and a 09:00pm

We can create a macro that specifies when this email macro is to be activated.

Can you specify in chronological order when the email is to be sent ? Are the times always the same each day ?

If the times change from day to day it is going to be more involved.

Changes in cells such as R26, P28 do not trigger Worksheet_Change because these are changes caused by calculating formulas, so they cannot be a signal to send an email.

For each day, you need to provide hours, e.g. in A1, A2, ..., in ascending order.

Then:
1. When the Excel file is opened, the code will load the hour from A1 and call Application.OnTime with this hour and the send_mail procedure.

2. Sub send_mail will send an email and load the next hour from A2, A3, ... and call Application.OnTime with this hour and the send_mail procedure.

If there is no more hour, it calls Application.OnTime to delete the send_mail procedure.

That's the idea I have, but I don't want to write the code.

it does change day to day but not week to week so every Monday has the same times and so forth. Sunday: 12am, 6am, 7am, 8am, 10am, 11am, 2pm, 3pm, 4pm, 4:30pm, 6pm, 7pm, 9:30pm, 11:30pm. Monday and Tuesday: 5am, 6am, 7am, 7:30 am, 8am, 8:30am, 11am, 12pm, 1pm, 2pm, 3pm, 4pm, 4:30 pm, 6pm, 9:30, 11pm, 11:30pm. Wednesday: the same as Monday + 12am, Thursday: the same as Monday+1:30pm, Friday: the same as Monday+10am. Saturday:6am, 7am, 8am, 10am, 11am, 1pm, 2pm, 3pm, 4pm, 4:30pm, 6pm, 7pm, 9:30pm, 11pm, 11:30pm.

1. I didn't use OnTime because I didn't have similar issues. I don't know if it works well. Test thoroughly.

2. I added a sheet "Schedule, where I entered the hours for Sunday -> Saturday

3. I added Module1 with the code

Please Login or Register  to view this content.

Correct the send_mail sub to your needs.

4. Code in the ThisWorkbook module

Please Login or Register  to view this content.