VBA代码以验证在Outlook全局地址列表中找到的电子邮件地址 [英] VBA CODE to Verify Email Address Found in Outlook Global Address List

问题描述

问题描述

要删除在全局地址列表中找不到的非活动（不存在）电子邮件帐户，然后再将电子邮件发送到excel中可用的Outlook电子邮件帐户列表中

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

解决方案

运行sql查询以从数据库中获取用户名或用户电子邮件ID

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

步骤1：

查询1：

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

或

查询2：

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

第2步：

调用要复制的模块以将检索结果集复制到Excel工作表

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

步骤3：

解析显示名称

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

错误1：如果我使用查询1：结果集将是[e-mail]，其中所有电子邮件ID均有效-WRONG_RESULT。

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

错误2：我f我使用查询2：结果集将是UserName
的组合，例如Rajan jha（rjhan），合同雇员将是Rajan jha（rjhan-Compnay1位于Compnay2）

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)

在此结果中，使用Rajanjha（rjahan）进行输出，如果在GAL中找到了该电子邮件帐户，它将有效，如果未找到，则为无效电子邮件。对于像Rajan jha（rjhan-Compnay1位于Compnay2）这样的结果集，即使电子邮件帐户在GAL中存在，导致结果无效。

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.

请引导我解决该问题

推荐答案

我通过在中间输出的条件检查中进行小的更改解决了问题。

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

此处 oRecip.Resolve 正在解析$的有效和无效电子邮件ID的电子邮件ID b $ b同一个公司和 InStr（oRecipName， @）= 0 起着关键作用，以删除无效的电子邮件ID。

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.

无效的电子邮件 oRecip.Resolve 会将输出解析为有效。但是输出将是

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

[e-mail]

[e-mail]

此处 InStr（oRecipName， @）= 0 在字符串和标志中检查 @ 因为无效的电子邮件ID

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

活动电子邮件 oRecip.Resolve 会将输出解析为有效。但是输出将是

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

用户电子邮件ID为
的Rajan Kumar Jha（姓氏）其中 @ 不会在中间字符串中，并且它是有效的电子邮件ID。

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

但是我的电子邮件有问题像这样的ID

But I have problem Email ID like

[e-mail]

[e-mail]

未将有效电子邮件ID的公司解析为需要解决的用户名。

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

这篇关于VBA代码以验证在Outlook全局地址列表中找到的电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！