Sure, here it is:
Sub Make_Outlook_Mail_With_File_Link()
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.96 And Cells(i, 7) <> "Complete" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Cells(3, 20) 'email to contents of cell T3
.cc = ""
.BCC = ""
.Subject = ActiveWorkbook.Sheets("TM Tracking Chart").Cells(i, 2) & " Proofing & Editing"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0
Cells(i, 7) = "Complete"
Set OutMail = Nothing
Exit For
ElseIf Cells(i, 4) >= 0.95 And Cells(i, 7) <> "Yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "[email protected]"
.cc = ""
.BCC = ""
.Subject = ActiveWorkbook.Sheets("TM Tracking Chart").Cells(i, 2) & " Proofing & Editing"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0
Cells(i, 7) = "Yes"
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
this code will check if cell is 96% or higher and that column G does not have "Complete" on it. If both conditions check then it sends an email to the address listed on Cells T3 (row 3, column 20)
After sending the email, it will place the word "Complete" in Column G
If cell is not 96% or higher then it will check to see if it's 95%. If so the old part of the code will run.
The code will exit after generating the email, weather a "Complete" email or a "Yes" email.
See if that helps.
Best of luck.
Bookmarks