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

Colin - Jun 16, 2016 at 10:10 AM

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
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."
End With
On Error GoTo 0
Set OutlookEmail = Nothing
End If
Next cell
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,