Hello,

What is the code for getting Excel to send an email once I close a line item? I got some code from another post but can't get it to work for me. My current spreadsheet is a list of outstanding problems. Once the issue is "Closed" (Dropdown in column U), I would like Excel to email a recipient who's address is in Column K. The issue is in the code - If c = "Closed" Then - but I'm not sure how to fix. Can anyone help? Thanks

Sub Closed()
'You need to Reference Outlook
'Goto Tools>References and select Microsoft Outlook Libary 9.0 (or your version)
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim c As Range

For Each c In Range("U2:U" & Range("U65536").End(xlUp).Row)

If c = "Closed" Then

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
'Change email address here **********************************
.To = Range("K" & c.Row).Value
.Subject = "Notification: Item#" & Range("A" & c.Row).Value & " " & "on " & C54 & " has been closed."
'Change name ****************
.Body = "Hello" & vbNewLine & vbNewLine & _
"Notification: Item#" & Range("A" & c.Row).Value & " " & "on " & C54 & " has been closed." & vbNewLine & vbNewLine & _
"\\silica\vol3\Groups\NPI FPA & Problem Logs\MX25\Problem List"

.Send 'Or use Display
Set olMail = Nothing
End With
Set olApp = Nothing
Application.ScreenUpdating = True
End If
Next c
End Sub