retrieving email address from outlook through userform

I would like to ask if there is a way to retrieve email address from outlook address book from the values enter in user form textbox and retrieve it.

For example, my textbox1 have user entering the full names of people they want to search and with a search button, textbox2 will have all the email address retrieve from outlook address book based on textbox1.

Currently what I have is, a module call retrieve email

Option Explicit
Sub GetAddresses()
Dim o, AddressList, AddressEntry
Dim c As Range, r As Range, AddressName As String
Set o = CreateObject("Outlook.Application")
Set AddressList = o.Session.AddressLists("Contacts")
'Chage this range to include the first names only. AddressName assignment line handles concatenating the last name.
Set r = Add.Emailname
For Each c In r
AddressName = c.Value & " " & c.Offset(0, 1).Value
For Each AddressEntry In AddressList.AddressEntries
If AddressEntry.name = AddressName Then
c.Offset(0, 2).Value = AddressEntry.Address
Exit For
End If
Next AddressEntry
Next c
End Sub
And in my user form, the search button

Private Sub Searchbutton_Click()
Call GetAddresses
End Sub
The code is what I have seen from online. Can anyone help me edit and guide me?

Here's a function that will retrieve the email address of a user given their name. It uses the Outlook application to create a new mail item, add the user name as the recipient and then resolves the names before retrieving the email address of the user.

Please Login or Register  to view this content.

WBD

hi what if my textbox have more than 1 names , and i want to retrieve all the email address?

How are you separating the names in the textbox? You could separate them with commas like this:

Alice Brooks, Charlie Davies, Erica Fletcher, Gordon Haynes

Then you could use something like this (in conjunction with the previous code):

Please Login or Register  to view this content.

WBD

Hi , so after i have tried. It works for only one box of names.May i know is there improve solution to existing code for search of multiple columns. For example. Textbox1 (names) --- Textbox2( textbox1's name email) And i have textbox3(names)-----Textbox 4(textbox3's name email). This is because each box is inserted into different columns in excel.

Private Sub Searchbutton_Click()

Dim nameList As Variant
Dim cclist As Variant
Dim outputList As String
Dim outputcc As String
Dim i As Long

nameList = Split(Attention.Text, ",")
For i = 0 To UBound(nameList)
outputList = outputList & "," & GetUserEmailAddress(Trim(nameList(i)))
Next i
Emailadd.Text = Mid(outputList, 2)

cclist = Split(emailcc.Text, ",")
For i = 0 To UBound(cclist)
outputcc = outputList & "," & GetUserEmailAddress(Trim(cclist(i)))
Next i
ccadd.Text = Mid(outputcc, 2)
Private outlookApp As Object
Public Function GetUserEmailAddress(userName As String) As String

Dim mailItem As Object
Dim recipient As Object

If outlookApp Is Nothing Then Set outlookApp = CreateObject("Outlook.Application")

GetUserEmailAddress = "[Not Found]"

Set mailItem = outlookApp.CreateItem(0)
mailItem.Recipients.Add userName
If Not mailItem.Recipients.ResolveAll Then Exit Function
Set recipient = mailItem.Recipients(1)
Select Case recipient.AddressEntry.Type
Case "SMTP"
GetUserEmailAddress = recipient.AddressEntry.Address
Case "EX"
GetUserEmailAddress = recipient.AddressEntry.GetExchangeUser.PrimarySmtpAddress
End Select

End Function

Your code is hard to read without the code tags around it; please use them to make the code more legible. I can see you have a mistake in this line:

Please Login or Register  to view this content.

Which should be:

Please Login or Register  to view this content.

Apart from that, it should be working OK?

WBD