Add cell value to email subject line

[shariq1989]

So I am working on a huge vessel management project and I am trying to embed email alerts into it. I have the gist of sending out generic email alerts out (contain the same subject and body regardless of which cell triggered it) and have been using that so that I would not have to deal with hundreds of modules for each variable. Now I am thinking I can just have the reference in the email code to pull the value that triggered the sub, along with the name of the component that the value is associated with. In my example I have four engines with their operating hours next to them.

I want the code to send out an email containing the engine name from Column A and the hours (Column B) at which the alert was triggered (for this example I'll say that the engines get serviced at "50" and overhauled at "100"). So when the email goes out, the subject line can be

"Maintenance Alert!"

and the body can be:

"Engine that triggered the alert" is that "X" hours

[AlvaroSiza]

Well I'm not 100% certain of what you are trying to accomplish. Your thread title states that you would like a cell value added to the subject line, while you narrative description states that you would like the Subject to be "Maintenance Alert!". Some clarification would be welcome.

As to the code included in your attached workbook, you are currently attempting to pass a range value (expressed as Range("whatever").Value) to a declared String variable. In order for this to work you need to remove the .Value. If it is your wish to include more than one Engine (and admittedly I cannot discern what constitutes a trigger), it could look something like this:

Please Login or Register  to view this content.

[mojo249]

Hi

I'm assuming you want to send out separate emails for each engine? If so, you need to add a loop which checks each row in column B and, if it's greater than 50, send an email using the engine number in column A of that row. Try something like the code below.

Dion

Please Login or Register  to view this content.

[shariq1989]

Hey,

Thanks for replying. Sorry, I changed by mind halfway through. But I think what I really want is to have the Subject line be "Maintenance Alert: "Engine from Column A"" and the body to have the engine that triggered the alert and its name. The trigger is when the hours go past a certain setpoint. 50 (midlife service) and 100 (overhaul) in this example.

[shariq1989]

@mojo, That works pretty darn well. I just tweak it a bit to make it work with my sheets. Hopefully, it'll cooperate.. Thanks!

[shariq1989]

So I tried adding the same procedure to my Inspections workbook. Kinda the same principle but we are dealing with Inspections and Days Remaining Till as opposed to Engines and Hours Remaining Till. In the workbook, I have a form that feeds data into a database (Past Inspections). The List sheet pulls the latest record for each Inspection category and is used to as a dashboard to see when the next inspection is due. I want to use the Days to Go column just the way we used the Hours column in the hoursexample workbook. Adapting the code to this workbook sounded a lot easier than it is turning out to be. I was trying to fix one error at a time until it all worked out but have hit a roadblock with the current "Object Required" error that is popping up...

[shariq1989]

I figured out what the problem is! I just dont know how to fix it. I took out the ws definition because I figured I didnt need to refer to ThisWorkbook if the sub was on the sheet itself. I just need to figure out how to refer to the sheet itself. Using Set List = Sheet3.Sheets("Sheet3") and then List.Cells(varRow, 1).Value isn't working for me...

[Robotacha2010]

Mojo, you are a legend.

The code you posted is something I have been looking for!! thankyou very much.

[shariq1989]

Hey Mojo, everything worked out really well! Thanks so much. Just for future reference, what would the code look like if i wanted just one email with all the engines with hours over 100 listed on there?

[mojo249]

You're welcome.

Shariq: to send one email remove the email preparation from the loop and instead record the engine numbers to a string (strEngines).

Dion

Please Login or Register  to view this content.