retrieving email address from outlook through userform

Question

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?

It's not clear what you're trying to do. `the values enter in user form textbox`? Perhaps it would help if you show examples of what you have and what you're trying to do. — ashleedawg, Aug 31 '18 at 05:23
Hi @ashleedawg, for example in userform textbox1, the user enter names, Tom, James and then when i click on search button, textbox2 will have value that is the result of email address from the address box in the outlook. — Lim, Aug 31 '18 at 05:29
I think you're after the `ResolveDisplayNameToSMTP` function by Sue Mosher. I can't find the original site, but here's the link on this site: [Creating a “Check Names” button in Excel](https://stackoverflow.com/questions/31161726/creating-a-check-names-button-in-excel/31161938#31161938) — Darren Bartrup-Cook, Aug 31 '18 at 09:26

JvdV · Accepted Answer · 2018-08-31T07:45:16.770

I see you have copied your code. This code is meant to loop around a range. You could simply remove the loop and inmplement your textbox value and assign it to your searchbutton. However you'll need a .ListBox1 (instead of .TextBox2) since you wanted to have all hits in contact to appear in a list. I've also implemented the .Instr function to see if the searchvalue appears in the contacts name (using LCase to be sure). This would making a search on just a lastname way easier. The code then would look like:

Option Explicit
Private Sub GetAddresses()
Dim o, AddressList, AddressEntry
Dim AddressName As String
Set o = CreateObject("Outlook.Application")
Set AddressList = o.Session.AddressLists("Contacts")
 'Change this range accordingly
AddressName = UserForm1.TextBox1.Value
For Each AddressEntry In AddressList.AddressEntries
    If InStr(1, LCase(AddressEntry.Name), LCase(AddressName)) <> 0 Then
        UserForm1.ListBox1.AddItem AddressEntry.Address
    End If
Next AddressEntry
End Sub

Private Sub Searchbutton_Click()
UserForm1.ListBox1.Clear
Call GetAddresses
End Sub

hi @jvdv , if i have more than one name in textbox1. How should i change the code? — Lim, Aug 31 '18 at 09:24
Why would there be more than one name in one textbox? Consider: A) making more textboxes B) Search multiple times from one textbox (and possibily don't clear your Listbox if you want the result to add to the listbox in a new search) — JvdV, Aug 31 '18 at 09:35
i think the solution B works.Are you able to guide me on that? — Lim, Aug 31 '18 at 10:19

retrieving email address from outlook through userform

1 Answers1