Inserting names from address list into user form

[PCMonitor]

Hi there

I would like be able to insert names from our exchange global address list or maybe usernames from active directory in to a texbox on my user form and then seperate them with ; and space

easy enough huh?

[matthewspatrick]

Set a reference to the Microsoft Outlook library, then use this code. Presently it populates A1 on the ActiveSheet, but you can easily use it to populate a TextBox on a UseForm:

[VBA]
Sub foo()

Dim olApp As Outlook.Application
Dim olGCL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntry
Dim olNS As Outlook.Namespace
Dim Counter As Long
Dim Result As String

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olGCL = olNS.AddressLists("Global Address List")

For Each olEntry In olGCL.AddressEntries
Result = Result & ";" & olEntry.Name
Next

[a1] = Mid(Result, 2)

Set olEntry = Nothing
Set olGCL = Nothing
Set olNS = Nothing
Set olApp = Nothing

End Sub
[/VBA]

[PCMonitor]

Thanks for reply,

Sorry I was quite vague I'm still at a bit of a loss, I can set a reference to the outlook libarary easy enough however,

I want to (would like to) end up with a button next to the text box that launches the Outlook address book GUI or maybe MS Address Book so I can go through and select a bunch of people and then click ok to that, thus inserting them into the text box with ; and a space between each name is that similar ?

[piercedgeek]

So basically, you want to use the outlook address book like normal, but rather than have it put the names in the "To:" area of a new email, populate a cell(s) in excel?

[PCMonitor]

Almost, I have a userform containing several text boxes (different categories) I want the end user of the form to put peoples names in the different textboxes which send the entrys of for me to look at. so basically I want to use the outlook address book like normal, but rather than have it put the names in the "To:" area of a new email, populate a text box on my user form in the following format:

jim; larry; tim; richard; bob;

[piercedgeek]

I'm pretty sure you won't be able to do it exactly like that (I could be wrong, I have not done much work involving outlook in excel VBA), but I'm pretty sure you could use the code above to pull all of the names from outlook into excel (dump them to a hidden worksheet or something), then make some sort of GUI in excel that will read the names that were just pulled out of outlook, allowing you to pick them from a list. Also, maybe use Workbook_Open() to pull in new outlook data so the list is always up to date?
Sadly, I've not done much work with making custom GUI's in VBA either, so I won't be able to whip up some quick code for this in 2 min, but I'm 100% sure it *can* be done.