We help IT Professionals succeed at work.

import contacts from csv file into outlook using vba

Medium Priority
2,266 Views
Last Modified: 2012-05-06
Dear Experts,


I need your urgent help about vba coding. I want to import csv file into outlook using vba. I found code, but it doesn' work. It creates only subfolder in outlook "contacts" and then DL's, but it doesn't make any contacts or populate the DL's.
Can someone help me to run this code. The code has to create contacts and next popluate the DL.

regards,

Karel
code.txt
Comment
Watch Question

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
There is a line which starts  - On Error Resume Next near the beginning of the code.

I suggest that you comment that line out so that errors are reported instead of being ignored.

Author

Commented:
Hi Grahamskan,

I did it gives error on the next lines (see below):

'On Error Resume Next 'this will stop users receiving errors, if you would like to debug before distributing, remove this

    Set objFolder = myNameSpace.GetDefaultFolder(olFolderContacts).Folders(Name) 'sets the place the contacts will go to

    objFolder.ShowAsOutlookAB = False 'takes the old version of the folder out of the active contacts list

    objFolder.Delete 'deletes the old folder

 

    If Not objFolder Is Nothing Then

        objFolder.ShowAsOutlookAB = False

        objFolder.Delete

        F.Folders.Add Name

    End If

 

    F.Folders.Add Name 'creates new folder in desired location

Author

Commented:
error:run-time error -2147221233 (8004010f)
the operation failed. an object could not be found
-------------------------

regards,

Karel
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Does the error tell you what was wrong?

What is the message?
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Sorry. Cross-posted.

The code does not check for the existence of the folder before trying to set it, so a quick solution is to restore the On error, but to turn off Error handling handling after the delete attempt.

On Error Resume Next 'this will stop users receiving errors, if you would like to debug before distributing, remove this
    Set objFolder = myNameSpace.GetDefaultFolder(olFolderContacts).Folders(Name) 'sets the place the contacts will go to
    objFolder.ShowAsOutlookAB = False 'takes the old version of the folder out of the active contacts list
    objFolder.Delete 'deletes the old folder
On Error GoTo 0

Open in new window

Author

Commented:
Hi GrahamSkan,

it gives still an error :(.

error.JPG

Author

Commented:
When I delete the contact folder manaully. it gives this error msg:
line: Set Wkb = ObjExcel.Workbooks.Open(FileName:=Path & " \ " & FName) 'opens the csv file as the active workbook
error.-2JPG.JPG
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Are you the administrator?

Author

Commented:
I have full admin rights

Author

Commented:
Hi,

I modified a bit and got now this error: Runtime Error 9: Subscript out of range


objFolder.ShowAsOutlookAB = True ' ticks box to see folder content items as contacts

Path = "C:\import" ' path where csv file is saved to

FName = "contacts.csv" 'name of csv file

 

Set ObjExcel = New Excel.Application ' opens excel (will not be visible to the user)

Set Wkb = ObjExcel.Workbooks.Open(FileName:=Path & "\" & FName) 'opens the csv file as the active workbook

'line below gives error
Set WS = Wkb.Sheets("sheet1") ' name of the sheet in the workbook the contacts are stored in (default = "sheet1")
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
That means that you there isn't a sheet called "sheet1" in the workbook.
If you're not sure of the name, you can use the numerical index instead.

Set WS = Wkb.Sheets(1)

Otherwise, give it the actual name that you are using, e.g. :

Set WS = Wkb.Sheets("MySheet")


Author

Commented:
Hi GrahamSkan,

I'm lost now :(. It adds now, not all of contacts, few in address book in wrong way.
this are my headers: "First Name","Last Name","Business Phone","Mobile Phone","E-mail Address"
And in the new created contats it gives like this: full name= first name + business phone nr

Author

Commented:
I found error:)
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
That's great. Well done.

Author

Commented:
Only problems is now to populate DL. That doesn't work.

Author

Commented:
I have only one question. How can i change the code so that all contacts will be created directly in root folder in stead of in subfolder please?
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
I think that you just need to omit the new folder creation.
'...
Set olApp = CreateObject("Outlook.Application")
Set myNameSpace = olApp.GetNamespace("MAPI")
lastline = False
i = 1
 
'code deleted from here
 
If Name = "Contacts" Or IsMissing(Name) Or Name = "" Then
Set objFolder = myNameSpace.GetDefaultFolder(olFolderContacts)
Else
Set objFolder = myNameSpace.GetDefaultFolder(olFolderContacts).Folders(Name)
End If
'...

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Sorry for late reply. Thanks to you the script works to import. Thanks really lot :D. But can you only help to avoid duplicates please during the update. I saw when I run the script twice it creates all the contacts twice in Folder contacts :(.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.