CODEWITHSUNDEEP
vbaoutlookms-word
ND1
ND1

Reputation: 3

Retrieve email address from Outlook using Word VBA

I am using Microsoft Office 365.

I am trying to search Outlook contacts, for names in the Word document, to retrieve email addresses in below format.

Surname, Name <email address>;

I receive:

/o=ExchangeLabs/ou=Exchange Administrative Group....

How can I get the email address in "[email protected]" format?

Word VBA code.

Option Explicit
Sub SendEmail()
    Dim Names As String
    Dim Doc As Word.Document
    Dim rng As Word.Range
    
    Set Doc = ActiveDocument
    Names = Selection.Text
    Selection.Collapse Direction:=wdCollapseEnd
    Selection.Move Unit:=wdStory, Count:=1
    Selection.Text = vbNewLine
    
    Dim OL As Outlook.Application
    Dim EmailItem As Outlook.MailItem
    Dim Rec As Outlook.Recipient
    
    ' Check if Outlook is already open
    On Error Resume Next
    Set OL = GetObject(, "Outlook.Application")
    On Error GoTo 0
    
    ' If Outlook is not open, create a new instance
    If OL Is Nothing Then
        Set OL = New Outlook.Application
    End If
    
    Set EmailItem = OL.CreateItem(olMailItem)
    
    With EmailItem
        .Display
        .CC = Names
        
        ' Ensure names are properly formatted
        Dim RecipientsResolved As Boolean
        RecipientsResolved = .Recipients.ResolveAll
        
        If Not RecipientsResolved Then
            MsgBox "One or more recipients could not be resolved. Please check the names and try again.", vbExclamation
        End If
        
        For Each Rec In .Recipients
            Selection.Collapse Direction:=wdCollapseEnd
            Selection.Text = Rec.Name & " <" & Rec.Address & ">; "
            Selection.Collapse Direction:=wdCollapseEnd
        Next Rec
     End With
    
    Set OL = Nothing
    Set EmailItem = Nothing
End Sub

Upvotes: 0

Views: 84

Answers (1)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66356

That is a perfectly valid email address of type "EX" while you expect "SMTP". Use Recipient.AddressEntry to retrieve the AddressEntry object and then check AddressEntry.Type. If it is "SMTP", just use AddressEntry.Address. Otherwise call AddressEntry.GetExchangeUser() (check for null) and use ExchangeUser.PrimarySmtpAddress.

Upvotes: 0

Related Questions