Add cell value to email subject line

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

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.

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

Sub Send_Email_Using_VBA()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim ws As Object

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set OutApp = CreateObject("Outlook.Application")
    
    strto = "fred@foss.com"
    strcc = "shariq1989@gmail.com"
    strbcc = ""
    strbody = "Attn." & vbNewLine & vbNewLine & _
              "There is an engine overhaul due!" & _
              vbNewLine & vbNewLine & "Automated Msg from the America"

'Loop through rows from 1 to last row
For varRow = 1 To ws.Range("A60000").End(xlUp).Row
    'Check if hours exceed 50
    If ws.Cells(varRow, 2).Value > 50 Then
        strsub = "Maintenance Alert: " & ws.Cells(varRow, 1).Value

        Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .To = strto
            .CC = strcc
            .Subject = strsub
            .Body = strbody
            .Send
        End With
    End If
Next varRow
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set ws = Nothing

End Sub

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

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...

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...

Mojo, you are a legend.

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

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?