title: vba not working with vlookup
body: <p>I am trying to write a document which automatically fires an email to certain people based on certain conditions. I have the code working, and am happy with what I have.</p>
  
  <p>But....</p>
  
  <p>When I change the email addresses on the worksheet into a vlookup, the coding I have no longer picks up the email addresses.</p>
  
  <p>Is there any way that this can be fixed? Thanks in advance. Code as is, below.</p>
  
  <pre><code>Sub Button1_Click()
  
  Dim OutApp As Object
  Dim OutMail As Object
  Dim cell As Range
  Dim StrBody As String
  
  Application.ScreenUpdating = False
  Set OutApp = CreateObject("Outlook.Application")
  
  On Error GoTo cleanup
  For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants) 
      If cell.Value Like "?*@?*.?*" And _
         LCase(Cells(cell.Row, "C").Value) = "true" Then 
  
          Set OutMail = OutApp.CreateItem(0)
          On Error Resume Next
          With OutMail
              .To = cell.Value
              .Subject = "Urgent Training Notification  - Action Required"
              .Body = "Dear " &amp; Cells(cell.Row, "A").Value _
                    &amp; vbNewLine &amp; vbNewLine &amp; _
                      "Please be aware, " &amp; Cells(cell.Row, "D") &amp; " is required to update the following training:" &amp; vbNewLine &amp; _
                      " " &amp; vbNewLine &amp; _
                      Cells(cell.Row, "E") &amp; vbNewLine &amp; _
                      " " &amp; vbNewLine &amp; _
                      "This must be rectified within 14 Days." &amp; vbNewLine &amp; _
                      " " &amp; vbNewLine &amp; _
                      " " &amp; vbNewLine &amp; _
                      "Any issues, please escalate as required." &amp; vbNewLine &amp; _
                      "Many thanks."
  
              .Send  'Or use Display
          End With
          On Error GoTo 0
          Set OutMail = Nothing
      End If
  Next cell
  
  cleanup:
  Set OutApp = Nothing
  Application.ScreenUpdating = True
  End Sub
  </code></pre>