Okay, try this. Email addresses need to be placed in column Q (although you can easily change that in the code):
Code:
Sub SendEmail()
'Macro Purpose: To send an email through Outlook
Dim objOL As Object
Dim objMail As Object
Dim sEmail As String
Dim sEmailColumn As String
Dim sSubject As String
Dim sBody As String
Dim lDataRow As Long
Dim cl As Range
'Set column with email address
sEmailColumn = "Q"
For Each cl In Selection.Resize(, 1)
'Generate required info
lDataRow = cl.Row
'Check if remediation required
If cl.Parent.Range("L" & lDataRow).Value = "Urgent" Then
With cl.Parent
sEmail = .Range(sEmailColumn & lDataRow)
sSubject = "Agreement " & .Range("B" & lDataRow) & " requires urgent remediation!"
sBody = "Remediation Required:" & vbNewLine & .Range("H" & lDataRow) & _
vbNewLine & vbNewLine & "Advisors Comments:" & vbNewLine & .Range("N" & lDataRow) & _
vbNewLine & vbNewLine & "Management Comments:" & vbNewLine & .Range("O" & lDataRow)
End With
'Turn on error handling
On Error GoTo Cleanup
'Bind to Outlook
Set objOL = CreateObject("Outlook.Application")
'Create a new email and send it
Set objMail = objOL.CreateItem(0) '0=olmailitem
With objMail
.To = sEmail
.Subject = sSubject
.Body = sBody
.Display
End With
End If
Next cl
Cleanup:
'Release all objects
Set objMail = Nothing
Set objOL = Nothing
On Error GoTo 0
End Sub
Once that code is pasted into a standard module, you can select any cell, press Alt+F8 and run the macro. It should create the email for you. If you select a group of cells, it will create an email for each line where remediation is Urgent.
If you'd like it to send automatically, without creating the preview first, change ".Display" to ".Send"
Bookmarks