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!!
Bookmarks