title: VBA CODE to Verify Email Address Found in Outlook Global Address List
body: <p>Problem Description</p>
  
  <p>To remove Inactive (non existing )email accounts not found in global address list  before send email to list of available outlook email accounts in excel</p>
  
  <p>Solution </p>
  
  <p>Run sql Query to fetch Username or User Email id from Database</p>
  
  <p>Step 1 : </p>
  
  <p>Query 1 :</p>
  
  <pre><code>strSQL = "select distinct [User Email ID]  from dbo.vw_EmailRecipients_AT where Report_Catalog_ID in (" &amp; rptid &amp; ")"
  </code></pre>
  
  <p>or</p>
  
  <p>Query 2 :</p>
  
  <pre><code>strSQL = "select distinct [User Name]  from dbo.vw_EmailRecipients_AT where Report_Catalog_ID in (" &amp; rptid &amp; ")"
  </code></pre>
  
  <p>Step 2 :</p>
  
  <p>Call the Module to Copy retrieve Result Set to Excel Sheet</p>
  
  <pre><code>Sub Testemail()
      Dim rEmails As Range
      Dim rEmail As Range
      Dim oOL As Object
  
      Set oOL = CreateObject("Outlook.Application")
      Set rEmails = ThisWorkbook.Sheets("Report_Users").Range("A2:A" &amp; Range("A65000").End(xlUp).Row)
  
      For Each rEmail In rEmails
          rEmail.Offset(, 1) = ResolveDisplayNameToSMTP(rEmail.Value, oOL)
      Next rEmail
  
  End Sub
  </code></pre>
  
  <p>Step 3 :</p>
  
  <p>Resolve Display Name </p>
  
  <pre><code>Public Function ResolveDisplayNameToSMTP(sFromName, OLApp As Object) As String
  
      Dim oRecip As Object 'Outlook.Recipient
      Dim oEU As Object 'Outlook.ExchangeUser
      Dim oEDL As Object 'Outlook.ExchangeDistributionList
  
      Set oRecip = OLApp.Session.CreateRecipient(sFromName)
      oRecip.Resolve
      If oRecip.Resolved Then
          ResolveDisplayNameToSMTP = "Valid"
      Else
          ResolveDisplayNameToSMTP = "Not Valid"
      End If
  End Function
  </code></pre>
  
  <p>Bug 1: If I Use Query 1 : The resultset will be abcdef@company.com where all the email id will be valid - WRONG_RESULT.</p>
  
  <p>Bug 2: If I Use Query 2 : The resultset will be combination of UserName
  like Rajan jha(rjhan) and contract employees will be Rajan jha (rjhan - Compnay1 is at Compnay2)</p>
  
  <p>In this result the output with Rajanjha(rjahan), if the email account is found in GAL it will valid and if not found it will be Invalid email.For resultset like Rajan jha (rjhan - Compnay1 is at Compnay2) where even email account exist in GAL it result as invalid.</p>
  
  <p>please guide me through to solve this problem</p>