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.

Originally Posted by Coltara

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

What is this clock for? This clock only shows the time, it is not used for anything. The change in P28 is not used to alarm that it is 11:00, 12:00 etc. because this change does not trigger Worksheet_Change

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.

Who is telling you to change the UpdateClock sub? I wrote that you need to correct the send_mail sub because I do not know what you are sending and to which address. But at the end of the send_mail sub there must be UpdateClock

Please Login or Register  to view this content.

What is this clock for? This clock only shows the time, it is not used for anything. The change in P28 is not used to alarm that it is 11:00, 12:00 etc. because this change does not trigger Worksheet_Change

The time formula is so excel could match the current time with the time in the shift change schedule so that my lookup formula would work. using the now() formula excel was reading them as two different things, without that in cell P28 the formula in R26 does not work.

Who is telling you to change the UpdateClock sub? I wrote that you need to correct the send_mail sub because I do not know what you are sending and to which address. But at the end of the send_mail sub there must be UpdateClock

no one told me to use it. I was using it so that P28 would update on it's own (as the formula in that cell keeps the current time but ONLY when there has been a change in the worksheet) and I started using that BEFORE I started this thread for help with the email notifications. I did try the send_mail w/ update clock that you gave me but the email only sent after I hit run and after that it kept telling me that the macro was unavailable or nothing happened at all.

Originally Posted by Coltara

The time formula is so excel could match the current time with the time in the shift change schedule so that my lookup formula would work. using the now() formula excel was reading them as two different things, without that in cell P28 the formula in R26 does not work.
no one told me to use it. I was using it so that P28 would update on it's own (as the formula in that cell keeps the current time but ONLY when there has been a change in the worksheet) and I started using that BEFORE I started this thread for help with the email notifications. I did try the send_mail w/ update clock that you gave me but the email only sent after I hit run and after that it kept telling me that the macro was unavailable or nothing happened at all.

I wish you good luck.

Do Not change your sheet SCHEDULE or the following macro code. Between the two, you have everything you need to send emails at the set times and day of the
week.

QUESTION

Why do you have multiple listings for 23:30 in the SCHEDULE sheet ? Do you really require sending
multiple copies of the 11:30 pm email ?

Please Login or Register  to view this content.

I don't need multiple copies I just need one email sent that says to check the spreadsheet at 11:30 pm. The schedule on my workbook is for each site so if there are multiple start times at 11:30pm then there are multiple sites to call at that time but only one email needs to be sent. but I think I see the problem I might have typed 23:30 twice but I actually meant to put 21:30 (9:30pm) and 23:30 (11:30PM). Thank you I will give it a try.

We really just want to check if the send_mail sub is being called, right? You claim that the send_mail sub is not being called. To check this, we write the simplest code. If there is data in column J, it means that the send_mail sub is being called.

Please Login or Register  to view this content.

I will provide the file again, but you must ensure:

1. The file contains only 1 sheet Schedule and my code, without your sheets and codes. Because we will only check my code, right? We do not know what your code does, and whether it does not affect my code. And we only want to check my code.

2. You cannot change, add, delete anything.

3. Download the attached file --> run the file --> leave it for a few hours --> see if there is data in column J entered by sub send_mail.

code in ThisWorkbook

Please Login or Register  to view this content.

code in Module1

Please Login or Register  to view this content.

I added in function readTime

Please Login or Register  to view this content.

In the previous readTime the code reaches the last hour and turns off the clock. With the above line the code reaches the last hour then takes the first hour of the next day.

okay thank you. I really appreciate both of you for helping me on this but the only thing i ever changed on any of it was the "yourmailhere@example.com" I don't trust my skill set enough to change anything. I will use the file you attached this time rather than just copy and paste into my own file.

okay so i let it run for yesterday and a few hours today and it does seems to post two timestamps in J at the designated times. Is that what we are looking for?

Originally Posted by Coltara

okay so i let it run for yesterday and a few hours today and it does seems to post two timestamps in J at the designated times. Is that what we are looking for?

When the send_mail sub is executed it places an entry in column J - day and time. So at such times the send_mail sub is called. The code works. Now we need to replace writing to column J with sending the email.

I've reviewed all of the sheets in the workbook and fail to find a cell in Column J that is requiring an entry.

Please assist me in this matter.

I think it's about the code from post #32

Ok ... tracking if the email was sent is nice but not a requirement ? Is the project complete now ?

When the send_mail sub is executed it places an entry in column J - day and time. So at such times the send_mail sub is called. The code works. Now we need to replace writing to column J with sending the email.

Okay, so that would be this right?

Formula:

Please Login or Register  to view this content.

Originally Posted by Coltara

Okay, so that would be this right?

Formula:

Please Login or Register  to view this content.

When we have done the test and we see that the send_mail sub is called at the given times, we will change the content of the send_mail sub: instead of entering the day and time in column J, the code sends the email.
Your code is correct.

okay i will try it for a couple days to verify it works and let you know.