VBA code to automatically send an email containing row values to an allocated recipient once a date is reached.

[drapes2022]

Hi guys,

I've been lurking around these parts for a while and have come across some amazing coding. Unfortunately I can't seem to piece together exactly what I need.

I work in Health & Safety and we have time critical corrective actions for accidents and incidents, the managers need to be reminded of their corrective actions to resolve outstanding actions. Unfortunately once we notify them of what they need to do, we are chasing them up constantly.

So to facilitate this I would like to put together a VBA code to send an email to send the values contained in a row, to the managers email address found in that row.

Could anybody please help me with this?

 

[Micron]

Shouldn't be too hard - once you figure out what the trigger is and when it should be "pulled". Suppose you want to do this after a certain amount of time/days has passed. The trigger isn't the date, nor is it the elapsed days between dates. Those are parameters that go into the decision to send or not IF the trigger is pulled. Suppose the trigger is the opening of the workbook. That can happen 20 times in a day, so send the email 20 times in one day to the same person? That will be appreciated for sure . So another parameter is "only if not already sent" ? Perhaps you can see that a lot of thought needs to go into these things.

If you want help, perhaps copy/paste a sheet portion that contains the info to be sent, in the columns it will be in and post the body text of the email message if this will be sent in Outlook. Include well defined parameter(s) and what the trigger will be, which btw, could just be a sheet button.

 

[drapes2022]

Hi @Micron

That's great advice, thank you.

I suppose the trigger would be when the document is opened.

So I have made a sheet button in excel that will send an email containing a selection, however I don't know how to make this automatically fill the address bar in outlook with the recipient found within the selection.

Additionally whilst the Status cell is 'Overdue'; a date is reached within the 'Due' cell, for example 06/05/2022, I would like to be able to programme a VBA module to send a message that would then be sent to the email found in 'email' cell with the body of the text containing something like "Your outstanding corrective action is:" with the cell range below automatically copied into the body:

ID Depart Action Email Opened Reminder Status Due Description Chart

I43

RH Whse

Review fitting specific mast cameras for telescopic masts

jefferynaples@email.co.uk

31/03/2022

25/04/2022

Overdue

06/05/2022

Combi driver was lifting a stillage off a truck and caught the top stack and whilst moving away pulled the rest of stack over 14/03/2022 ##### waiting for camera company to provide MAC addresses for IT 29/04/2022 - ##### waiting for IT to sort out wi-fi 03/05/2022 - Currently sat with IT, some input required from ####. Linked to I31

Yes

The idea is that this would be sent once every 3 days past the due date until the Status cell is manually changed to 'Completed'


I hope that clears things up.

 

[Micron]

You already have a button click event (trigger) that will be used to send email? If you use that as the event/trigger you can use selection property to know which row you need to deal with, but if it's the wrong row that could be a problem for you.

WRT workbook opening, I thought this was going to be simpler than having to count and loop over every used row in a sheet, looking for missing values, looking for a 3 day difference between due date and the current date on every row - then updating the sent date IF the email was sent but not if it failed/cancelled? Then there's whatever I haven't even thought about yet. I'd say doable, but I don't have code for something that complicated. If user were to manually change something in the row, I have that type of code. F'rinstance, user sets the Reminder date to today. Then the row to send becomes the target row - much easier. Still have to look for email parameters that might be missing though.

It's looking like you need a paid programmer, or someone here who has the code already. My issue is that I'm much more adept at Access vba and am delving more in Excel vba so there's a lot of Excel stuff I have to research. Something like this would probably take me 3 hours; maybe more, especially if I go down some rabbit holes so to speak. Now that you've provided lots more info, you'll probably get a solution soon.
Wishing you luck!