can I convert excel data into outlook contacts ?

  pharte 22:34 01 Jul 2004

I use a pda and have all my contacts and appointments on it and also syncronised on outlook on my desktop, I have now got a full customer listing in excel format and could do with a way of converting it into my contacts database.

  stalion 22:35 01 Jul 2004

one for you VoG™ regards.

  VoG II 22:38 01 Jul 2004

Which version of Office?

  pharte 22:41 01 Jul 2004

office 2003, xp home,

  pharte 22:41 01 Jul 2004

oops professional edition

  VoG II 22:51 01 Jul 2004

Assuming that you are running Excel 97 or later:

Start Excel. Alt+F11 to open the Visual Basic Editor. Insert/Module. Paste in the following

Sub Export_Contacts()

Dim olApp As Outlook.Application

Dim olNamespace As Outlook.NameSpace

Dim olFolder As Outlook.MAPIFolder

Dim olColItems As Outlook.Items

Dim olItem As Outlook.ContactItem

Dim i As Long

Dim j As Long


Set olApp = New Outlook.Application

Set olNamespace = olApp.GetNamespace("MAPI")

Set olFolder = olNamespace.GetDefaultFolder(10)

Set olColItems = olFolder.Items

Application.ScreenUpdating = False
j = 0

j = Range("A2", Cells(2, 1).End(xlDown)).Count + 1

For i = 2 To j

If olColItems.Find("[CompanyName]= " & CStr(Cells(i, 1).Value)) Is Nothing Then

Set olItem = olColItems.Add

With olItem

.CompanyName = Cells(i, 1).Value

.BusinessAddressStreet = Cells(i, 2).Value

.BusinessAddressPostalCode = Cells(i, 3).Value

.BusinessAddressCity = Cells(i, 4).Value

.FullName = Cells(i, 5).Value

.Email1Address = Cells(i, 6).Value

.BusinessTelephoneNumber = Cells(i, 7).Value

.MobileTelephoneNumber = Cells(i, 8).Value


End With

End If

Next i

Set olItem = Nothing

Set olColItems = Nothing

Set olFolder = Nothing

Set olNamespace = Nothing

Set olApp = Nothing

Application.ScreenUpdating = True

MsgBox "The Contacts have successfully been updated!", vbInformation

End Sub


Obviously you will need to modify this to suit the way you have laid out your worksheet. I'm assuming it is called "dir", we start on row 2, company name is in Column A, Business Address is in B etc.

  VoG II 23:05 01 Jul 2004

Set olItem = Nothing

Set olColItems = Nothing

should be on two separate lines, sorry.

  VoG II 23:09 01 Jul 2004

Oh yes and you now need to go back into your worksheet, Tools/Macro/Macro, highlight Export_Contacts and click the Run button.

  pharte 23:44 01 Jul 2004

I'm definately gonna wait until tomorrow / sober until I give it a go..........

will be forever gratefull if it works....

  VoG II 23:49 01 Jul 2004

When *you* are sober. LOL!

Tell us where the contact info is stored (Rows, Columns) and we can code it, can't we folks. For me that will be tomorrow at earliest.

  Muckle 23:53 01 Jul 2004

If you open Outlook and go to 'File', 'Import and Export...', Import from another program or file, next, scroll down and select Microsoft Excel, Next, Browse to the required spreadsheet and finish the task. The next time you synchronise with the pda everything should be updated.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?