Outlook 365 Email Notification for updated rows

[dakupferer]

Hello everyone,

I am in the process of phasing out from a retail business, and one of my daily tasks is onboarding their employees. We use an excel spreadsheet for this, but it has gotten quite large. To help minimize the possibility of any new employee getting missed, I wanted to write a script that, when a row is created or updated, it would send an email to more than 1 individual.

I have done some research on this and have attempted to write this myself, but have failed. Because of time constraints, and my already jammed work schedule, I wanted to take a chance with hope that someone more knowledgeable might help me here.

I have found various 'notification' threads here on MrExcel, but none are exactly what I am needing.

I am hoping to learn in this process as well so I can use, then pass on the knowledge I gain here.

Thank you for your time, and any efforts, in advance!

 

[Micron]

You might save anyone time and questions by posting what you tried but doing so within code tags (use vba button on posting toolbar).
IMO the difficulty lies in whether or not to create the email because a new row and editing an existing row trigger the same event (Worksheet_Change), so there's no easy way to tell that I started to edit a cell, then put back what was there so that when I leave, it remains the same value. As far as the sheet is concerned, that is a change so the code would run. I think your options would be to display the email and not send when that would be a mistake, or use a different type of event. Worksheet_BeforeRightClick might do the job for you. Right click on a cell in the column you choose and away you go. If any other column is right clicked on, exit the sub.

 

[dakupferer]

Thank you for your reply, Micron!

I am always finding that I should have researched further as I was not aware (atleast can't recall finding it) about Worksheet_BeforeRightClick.

because a new row and editing an existing row trigger the same event (Worksheet_Change),

This would actually be ok if I'm understanding what you mean. The designated persons would want to know when a person is added, and anytime they are edited. We have a few statuses for Onboarding. There is 1st Link, 2nd Link, Manager Sign, Hired, and No Show. The are all according to how far along a person is in the hiring process.

I apologize, but I only have this base script to share that I was attempting to adopt. It is not much, but I kept deleting new parts to it when it wouldn't run.

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Set fill color to FFFFFF for range 2224:2224 on selectedSheet

selectedSheet.getRange("2224:2224").getFormat().getFill().setColor("FFFFFF");

}

My original thought was that when any of the statuses listed above are entered in a designated column, the script would color code the row accordingly, and then email the designated persons.

I will go experiment with Worksheet_BeforeRightClick. and report back.

I also might be better off using Microsoft Flow but wanted to try this route first.

Thank you, again.

 

[Micron]

when any of the statuses listed above are entered in a designated column, the script would color code the row accordingly

That is a new and additional requirement.

and anytime they are edited

That could be interpreted in so many ways. When it comes to changes, I've been talking about changes to cell values (including additions in a particular column), not some attribute in some column for any person. It would be best if you copied and pasted before and after data, the "after" part showing what changes trigger email code to run. As for the right click idea, that is purely manual of course, so if code changes a value that should trigger email code, AFAIK, code doesn't fire change events. I'm pretty new to Excel vba, but that code looks like javascript because of the braces { }.

 

[dakupferer]

Good morning Micron!

Thank you for the input here. I am super new to trying to figure this out, posting to forums such as this, and also communicating my end goals to folks such as yourself. Everything you have mentioned is something I am adding to my overall understanding

With the color coding being a seperate requirement, I would be perfectly fine with nixing it. Getting the email sent out is a higher priority. The color coding was a personal effort.

It would be best if you copied and pasted before and after data, the "after" part showing what changes trigger email code to run.

First: Name is added to sheet from the Onboarding dept. When I see this, I would change the color to help me keep track of their progress through our hiring process. The color would not change until the status is changed to Hired.

Onboarding (version 1).xlsx
	A	B	C	D	E	F				J	K	L	M
1	Name	Employee Personal Email:	Submitted ON:	Start Date:	Promotion:	Paychex Status				PT to FT Benefits Elibability date	Title	Location	Direct supervisor:
2	Ramon Car	rc89@gmail.com	4/3/23	04/06/23		1st link					Team Lead
3	Mady Elli	melli@gmail.com	4/5/23	04/10/23		1st link					Pet Consultant
4	Carly Fausen	cfausen12@gmail.com	4/5/23	04/10/23		1st link					PT Pet Consultant
5	Andrew Skeiert	andeskeie@aol.com	4/5/23	04/11/23		1st link					PT Pet Consultant
6	Vany Devarez	vandan@yahoo.com	4/7/23	04/14/23		1st link					PT Pet Consultant
7	Ernest Mcenroe		4/7/23	4//7/23		1st link					Pet consultant
8	Kam Alvarez	ka87@gmail.com	4/10/23	04/15/23		1st link					Team Lead
9	Florida Carey	fjc0303@icloud.com	4/11/23	04/18/23		1st link					Pet Consultant
Onboarding

Hired:

Onboarding (version 1).xlsx
	A	B	C	D	E	F				J	K	L	M
1	Name	Employee Personal Email:	Submitted ON:	Start Date:	Promotion:	Paychex Status				PT to FT Benefits Elibability date	Title	Location	Direct supervisor:
2	Ramon Car	rc89@gmail.com	4/3/23	04/06/23		Hired					Team Lead
3	Mady Elli	melli@gmail.com	4/5/23	04/10/23		Hired					Pet Consultant
4	Carly Fausen	cfausen12@gmail.com	4/5/23	04/10/23		Hired					PT Pet Consultant
5	Andrew Skeiert	andeskeie@aol.com	4/5/23	04/11/23		Hired					PT Pet Consultant
6	Vany Devarez	vandan@yahoo.com	4/7/23	04/14/23		Hired					PT Pet Consultant
7	Ernest Mcenroe		4/7/23	4//7/23		Hired					Pet consultant
8	Kam Alvarez	ka87@gmail.com	4/10/23	04/15/23		Hired					Team Lead
9	Florida Carey	fjc0303@icloud.com	4/11/23	04/18/23		Hired					Pet Consultant
Onboarding

 

[Micron]

My original thought was that when any of the statuses listed above are entered in a designated column

Here are my latest musings on the problem. Perhaps this could be accomplished as
Use worksheet_SelectionChange event:
- if active cell (using the target column property, I'd guess) is not one cell in column F then do nothing, otherwise get the target value
- target value is passed to a module level variable.

Use worksheet_Change event:
- when a cell in F is edited, if the new and old value are the same, do nothing.
- if there is a new value and it equals one of the status values, then create the email. Further design would be required if the status accidentally went backwards (assuming there is such a possibility). Perhaps declaring constants of type number could avoid that - if const was 2 and new value = const 1, don't send email.

I'm not clear on the cell (or is it row?) colouring part.

The designated persons would want to know when a person is added

The addition is done manually after this workbook opened, or did some other process update it via code and you don't see it until you open this wb?