CODEWITHSUNDEEP
excelvbaemail
Mallikarjuna Shivappa
Mallikarjuna Shivappa

Reputation: 41

VBA CODE to Verify Email Address Found in Outlook Global Address List

Problem Description

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

Solution

Run sql Query to fetch Username or User Email id from Database

Step 1 :

Query 1 :

strSQL = "select distinct [User Email ID]  from dbo.vw_EmailRecipients_AT where Report_Catalog_ID in (" & rptid & ")"

or

Query 2 :

strSQL = "select distinct [User Name]  from dbo.vw_EmailRecipients_AT where Report_Catalog_ID in (" & rptid & ")"

Step 2 :

Call the Module to Copy retrieve Result Set to Excel Sheet

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" & Range("A65000").End(xlUp).Row)

    For Each rEmail In rEmails
        rEmail.Offset(, 1) = ResolveDisplayNameToSMTP(rEmail.Value, oOL)
    Next rEmail

End Sub

Step 3 :

Resolve Display Name 

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

Bug 1: If I Use Query 1 : The resultset will be [email protected] where all the email id will be valid - WRONG_RESULT.

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)

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.

please guide me through to solve this problem

Upvotes: 1

Views: 9520

Answers (2)

ASH
ASH

Reputation: 20302

I concur with niton. Instead of using VBA for this, and I am a huge fan of VBA, I would say grab the GAL using the methodology described in the URL below.

https://www.extendoffice.com/documents/outlook/3590-outlook-export-gal-to-csv.html

I've tried VBA to download all data pertaining to all contacts in Outlook, with horrible consequences. If you use the built-in controls, and follow the steps described above, you will get everything you need quickly and accurately. If you try to develop your own custom VBA solution, you are totally on your own there...

Upvotes: 0

Mallikarjuna Shivappa
Mallikarjuna Shivappa

Reputation: 41

I have solved Problem by making small changes in Condition Checking of Intermediate Output.

Public Function ResolveDisplayNameToSMTP(sFromName, OLApp As Object) As String

    Dim oRecip As Object  'Outlook.Recipient

    Set oRecip = OLApp.Session.CreateRecipient(sFromName)
    oRecip.Resolve
    oRecipName = oRecip.Name

    If oRecip.Resolved And InStr(oRecipName, "@") = 0 Then
        ResolveDisplayNameToSMTP = "Valid"
    Else
        ResolveDisplayNameToSMTP = "Not Valid"
    End If

End Function

Here oRecip.Resolve is resolving Email Id of Active and Inactive Email ID Of Same Company and InStr(oRecipName, "@") = 0 plays key role to remove invalid email id.

Inactive Email oRecip.Resolve will Resolve output to valid. But the output will be

[email protected]

Here InStr(oRecipName, "@") = 0 checks for @ in the String and flag as Invalid Email Id

Active Email oRecip.Resolve will Resolve output to valid. But the output will be

Rajan Kumar Jha (First Middle Last Name) of User Email Id Where @ will not be in Intermediate String and it is Valid Email Id.

But I have problem Email ID like

[email protected]

Where Active Email ID's Company is not getting Resolved to User name which need to be Solved.

Upvotes: 3

Related Questions