Auto send email when condition is met

Hello All
I am hoping you can help me with the below.

What I want
I have an excel spreadsheet containing a list of tasks that need completing. I have coded the sheet with formula to automatically indicate when items require an update (example attached). What I am after is for an automatic email to be sent to me for each item listed as ‘Update Required’. The body needs to contain the brief description of the item, so for example the ‘Update Required’ indicator is in column F and the item description is in column B, so would require something like ‘ActiveCell.Offset(-4,0)’ to select and copy the description.

What I have
Below is my attempt at the code required but it isn’t working right. This is what I want:
• Email to only be sent for items listed as ‘Update Required’ in column F
• Email to still work even when the worksheet is not open
• Email body to contain the brief description contained in column B

Spreadsheet condition trigger

Please Login or Register  to view this content.

Email process

Please Login or Register  to view this content.

Appreciate this is a lot to ask but am grateful for any assistance I can receive

Many thanks All
G1

Hello Tinbendr

Thank you for your help with this. I really was stuck in a rut. I have changed my code as you had mentioned which now looks like the code below. Unfortunately I am encountering a few issues which I feel may take some variable additions to correct, and I have no idea where to start.

Please Login or Register  to view this content.

Issue are:

When I amend the code to capture all instances of the word ‘Update Required’ from a range (Say if only 1 instance is triggered between range F14:F19), instead of sending just one email, I am getting 6. What I want is to receive just one email per instance, so if the date changed and suddenly three items got flagged as ‘Update Required’ then I only want those three emails generated; at the moment I would get 18 duplicates
Is there a way to get the worksheet change value to trigger if the workbook is closed? Currently an email is only generated if I click on the cell with the word ‘Update Required’ in it. Ideally I want to be reminded of something even when I am not actively in the workbook (not sure if this is maybe a stretch too far for Excel
I hope I am not being to expectant sir and I fully appreciate all of the advice you have already given.
Thanks again Tinbendr

G1

Hello Tinbendr

Thanks once again for your quick reply.

Yes I guess when I think about it, if Excel was that versatile then there would be no need for half of these new web based systems being created. After all I should remember that Excel is not some multileveled database application that is capable of creating whatever the user can dream of.

But I am grateful for what you have given me so far Tinbendr, and I will just have to work with what I have. It is still pretty amazing.

Thanks again and take it easy
G1

Hello Tinbendr

Thanks once again for your quick reply.

Yes I guess when I think about it, if Excel was that versatile then there would be no need for half of these new web based systems being created. After all I should remember that Excel is not some multileveled database application that is capable of creating whatever the user can dream of.

But I am grateful for what you have given me so far Tinbendr, and I will just have to work with what I have. It is still pretty amazing.

Thanks again and take it easy
G1