Email Macro using Formula as the email

[Tom90]

Hi All, I am try to loop a column of email address in a file, but the email address appear in the column by using an vlookup, I can do the VBA using one cell at a time and it will pick up the vlookup email and work ok but when I try it with a column of email address using the vlookup it will not work I have to copy and paste special values the email address and it works ok

Thanks Tom90

Sub TestNew()

  Dim OutApp As Object
  Dim OutMail As Object
  Dim cell As Range

  Application.ScreenUpdating = False
  Set OutApp = CreateObject("Outlook.Application")

  On Error GoTo cleanup
  For Each cell In Columns("J").Cells.SpecialCells(xlCellTypeConstants)
  If cell.Value Like "?*@?*.?*" And _
  LCase(Cells(cell.Row, "K").Value) <> "sent" Then

  Set OutMail = OutApp.CreateItem(0)

  On Error Resume Next
  With OutMail
  .To = cell.Value
  .Subject = "Reminder"
  .Body = "Dear " & Cells(cell.Row, "H").Value _
  & vbNewLine & vbNewLine & _
  "Please contact us to discuss bringing " & _
  "your account up to date."
  .Send
  End With
  On Error GoTo 0
  Cells(cell.Row, "K").Value = "sent"
  Set OutMail = Nothing
  End If
  Next cell

cleanup:
  Set OutApp = Nothing
  Application.ScreenUpdating = True
End Sub

----------------------------------------------------------------------------
Mod Edit: Code Tag!

 

[chirayu]

Not sure if this will work but try using the cell value as a variable rather than referring to the actual cell itself e.g.

Dim MyVal as String
MyVal = cell.Value

'Other Code

.To = MyVal

 

[Debaser]

Your code is specifically ignoring formulas with this line:

ForEach cell In Columns("J").Cells.SpecialCells(xlCellTypeConstants)

If you only want to look at formulas, use:

ForEach cell In Columns("J").Cells.SpecialCells(xlCellTypeFormulas)

 

[Tom90]

Hi chirayu & Debaser thanks for you help I got it working using Debaser reply,Tom90