Macro to send an email when a cell value changes to an email

[Closed]
Report
-
Hello,

I use Office 2013 for everything.

I am trying to write a macro that will send an email from outlook to an email address in a given cell when the cell changes to the target email. The cell in question is a VLOOKUP Function (in column H) that returns an email, so it is not in hyperlink form. Additionally, the VLOOKUP is nested inside an IFERROR statement that provide "No email available" in the case of an error. The goal is that when column H auto-populates with an email address, an email with be automatically sent to the address provided. This is currently the macro that I am using:

Sub SendEmail()
Dim OutlookApp As Object
Dim OutlookEmail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutlookApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
ThisWorkbook.Save
For Each cell In Columns("H").Cells.SpecialCells(xlCellTypeFormulas)
If cell.Value Like "?*@?*.?*" Then
Set OutlookEmail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookEmail
.To = cell.Value
.CC = "email address"
.Subject = "Test email, Please ignore"
.Body = "This email has been automatically generated. Have a wonderful day."
.Send
End With
On Error GoTo 0
Set OutlookEmail = Nothing
End If
Next cell
cleanup:
Set OutlookApp = Nothing
Application.ScreenUpdating = True
End Sub


but when I run it, nothing seems to happen. Any ideas how to fix this? And for the record, I am from no profound coding background so the simpler the explanation the better.

Thank you so much,

Colin