I am working on a spreadsheet.

I have created a Macro Code that sends an email to myself WHEN the value of a cell in column "D" = 95.

Now that the email is coming to me, the only other issue I am having is the Subject Title of the email. I would like for the email to have a subject titled of the value of the cell that is in column 'B' but it has to be on the same row as the cell I update in column D.

So for example...

Cell 3B = SCIENCE & Cell 3D = 95


Cell 4B = MATH & Cell 4D = 75


Since my macro already sent me an email (in this case) because Cell 3D = 95, I want the title or subject line to read SCIENCE.

When I update 4D to equal 95, I want the title of read MATH.


The Macro I am using now is:

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim i As Integer

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")


strbody = "<font size=""3"" face=""Calibri"">" & _
"Quality Assurance Department:<br><br>" & _
"Please be advised that there is a document ready to be proofed: .Cells(i, 2) <br><br>"

For i = 1 To 140

On Error Resume Next

If Cells(i, 4) = 0.95 Then


Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "[email protected]"
.cc = ""
.BCC = ""
.Subject = ActiveWorkbook.Sheets("ABC Company Chart").Cells(i, 2) & " Proofing & Editing"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Exit For


End If


On Error GoTo 0
Next i

Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub



THANK YOU FOR ANY HELP YOU CAN PROVIDE!!