Email that auto-populates based on Active Row Values when Cell is changed

Hi,

I have seen many of these threads come up on Google search, but whenever transposing some of the VBA data I can never get it to match what I am after.

Please also note that I am new to Excel Forum, I have tried reading the rules and if I have forgot anything please just ask me to add it, I apologise in advance of any rule breaking.

So I am at work, and due to Covid-19 we are anxiously trying to solve a problem with people working from home.

This is what is already working/in place:
I have an excel form which users in the office fill once they have finished their phone call with any work that can be completed by back office (current home workers), this auto-populates a spread sheet that I have set up. This form works and the population works on the spread sheet that I call "Back Office Requests" This is where I run into my problem.

The spread sheet defines the staff in a list on a separate tab and this works when data validation comes into play. What I want to work is when a team manager looks at the spread sheet, they can change "L" to "yes" and it will bring up an outlook pop up, with an auto-population of data, this data is just a snapshot of the active row that they have just changed. I would then like the "To:" part to be the person it was assigned to: column "K". Is this possible (I do not mind having to change the real names to emails)?

I really appreciate any help on this, been driving me crazy for ages - I know how to get it to work if it just pulls data from a predefined cell, but in this case I want the cells from the active cell row.

The spread sheet attached is what I am working with, I have removed all names but myself for data protection reasons, but please feel free to populate with your own names if you feel it would explain it to me easier.


Again really would appreciate help.

Thank you,
James

sintek, That works perfect - thank you very much.

Hi Sintek,

Quick question regarding this.

So the document is effectively a way to get team managers to auto send an email to an assigned member of staff and it works perfectly when it is not shared.

I need the document however to be shared as there will be forms inputting data to this sheet and team managers looking into it, not to mention quality control.

When I share it however, and now try to "auto send email" it comes up with Runtime error 2147417848 - automation error, the object invoked has disconnected from its clients. Any help would be greatly thanked. Is this something that can be done without altering the compatibility properties of excel. I don't have permissions to change properties of excel.

Does anyone know if this is possible? Thanks