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.

Thank you for your help. I have been playing with it all day but I can only get it to send the email when I hit "run" after that I get a message saying the macro is disabled or nothing happens at all.

Originally Posted by Coltara

Thank you for your help. I have been playing with it all day but I can only get it to send the email when I hit "run" after that I get a message saying the macro is disabled or nothing happens at all.

Test the new file
1. I added the line

Please Login or Register  to view this content.

in Function readTime

In the old code after the last send_mail of the day the code will turn off the Timer. The new code after the last send_mail of the day sets the timer to the first time of the next day.

2. in the ThisWorkbook module I add

Please Login or Register  to view this content.

The old code will not turn off the timer when the file is closed and the new code will turn off the timer.

3. Add content to the send_mail sub

4. I removed your sheets to test only the code.

5. I ran the file. For the test I set the time to 01:05 (Saturday), and when it is 01:05 the sub send_mail code is executed. If your send_mail does not start I do not know why.

I think first chance I get I will take a class to learn how to use VBA in depth because this seems to be getting more complicated then I thought it would be. I will try playing with this new code tomorrow. is there not a way to put the formula I have in R26 in the code for email notifications so they both trigger at the same time? or even a different way to do the notification part of this other then an email that I just don't know about?

You already have the send_mail code. The problem is when the send_mail code is to be executed. For example, on Saturday at 06:00 the send_mail code is to be executed. You need something that triggers send_mail. This could be a change in some cell. Then you can use Worksheet_Change to trigger send_mail. But Worksheet_Change requires that the change in the sheet is made by user action and not by calculating a formula. In other words, calculating a formula does not trigger Worksheet_Change, so you cannot use R26, P26, P28, ... for this because they contain formulas. Only a change made by the user triggers Worksheet_Change. But it makes no sense to sit at the computer and at 06:00, 07:00, 10:00 ... make changes, e.g. in A1 to trigger Worksheet_Change. Because instead the user will simply start the send_mail sub at 06:00, 07:00, 10:00 ..., right? That's why I came up with the Timer code.

By the way, what is an example calculation (result) of the formula in R26? I'm asking because I have Excel 2013 and there is an error.

the result is that when P28 (current time) reaches say 12:00 then all of the site numbers of all the sites that have a shift that starts at that time that day (from the table I created on another sheet in the workbook) will be listed. So if there is more than one site it spills over into the cells below. P28 has the

Formula:

Please Login or Register  to view this content.

and will also only stay current if there is a change in the workbook so i usually use a VBA macro to put a digital clock

Formula:

Please Login or Register  to view this content.

in the corner that I can hide which keeps time to the second but that seems to trigger the send_mail code every second.