Order Items in Row Source

[Curry]

I have written this code, below, which populates a list box with outlook contact items. This is working very well. I have the List box row source type set as Value List. The code simply builds the Row source string and inserts.

The issue I have though is that the list is not in Alphabetical order. Is there anyway to get the For Each , Next code to run through the folder, selecting the Items alphabetically so that the string has each item placed in the string in the correct order.

code:
Dim Outlook As New Outlook.Application
Dim NameSpace As NameSpace
Set NameSpace = Outlook.GetNamespace("MAPI")
Dim ContactFolder As MAPIFolder
Set ContactFolder = NameSpace.GetDefaultFolder(olFolderContacts)

Dim ContactItem As Variant

Dim List As String

For Each ContactItem In ContactFolder.Items

List = List & "'" & ContactItem.FullName & "';'" & ContactItem.Email1DisplayName & "';'" & ContactItem.Email1Address & "';"

Next ContactItem

Me.Contact_Select_List.RowSource = "Full Name;Display Name;E-Mail;" & List



Regards
IC

 

[Dennisk]

one way would be to load up an access table with the data, then use a query to retreive and sort.

 

[Curry]

THanks for that. I had thought about that however I am not to sure how to send the data to a table.I would want it to be a temp table which would be overwritten each time this COntact data is collected.

ANy clues would be great.

Regards
IC

 

[Dennisk]

create a table tblTemp and add 3 fields Name, Displayname,EmailAddress
and use the code snippet

dim rst as DAO.recordset
Currentdb.Execute "Delete * from tblTemp"

For Each ContactItem In ContactFolder.Items
     rst.addnew
     rst("Name")=ContactItem.FullName 
     rst("Displayname")=ContactItem.Email1DisplayName 
     rst("EmailAddress")=ContactItem.Email1Address 
     rst.update
Next ContactItem

 

[Curry]

Thanks for that..

I had got there before I saw your amended post....I enjoy a challenge.

Much appreciated....

Code
Dim Outlook As New Outlook.Application
Dim NameSpace As NameSpace
Set NameSpace = Outlook.GetNamespace("MAPI")
Dim ContactFolder As MAPIFolder
Set ContactFolder = NameSpace.GetDefaultFolder(olFolderContacts)

Dim ContactItem As Variant

Dim List As String


Dim DB As Database
Dim rst As DAO.Recordset
Set DB = CurrentDb()

CurrentDb.Execute "Delete * from tblContTemp"

For Each ContactItem In ContactFolder.Items
Set rst = DB.OpenRecordset("tblContTemp")
rst.AddNew

rst("Full Name").Value = ContactItem.FullName
rst("Display Name").Value = ContactItem.Email1DisplayName
rst("E-Mail").Value = ContactItem.Email1Address

rst.Update
rst.Close

Next ContactItem

Me.Contact_Select_List.RowSource = "qryContTemp"