localmagic
asked on
What's the best way to Import CSV data to Outlook Contacts weekly?
Hi Experts!
I have a client with a proprietary system for managing their business that also contains all their contact information. They would like to have this information in a public folder of Outlook contacts also.
The proprietary system has some features that will let me export the data to a CSV file - name, address, phone number, etc.
I would like to automate the process of importing this data to Outlook. I did a dry run with the Outlook import tool, and it's pretty close. But I think to truly automate it (and remove office workers from the equation) I will need to use some script or some such.
I programmed in the (distant...) past, and have read through posts suggesting that this can be done. In my head, I imagine a solution where I write a VBS script to read the CVS file and use the Outlook model to write it out. Is this the way to go? If so, can someone give me a sample script I could modify to match the fields I have?
My CSV today has stuff like this...
First Name Middle Name Last Name Suffix Company Name Phone Number Fax Number Mobile Phone Business Street Business Street 2 Business City Business State Business Postal Code Email Address
Thanks for ideas and help!
I have a client with a proprietary system for managing their business that also contains all their contact information. They would like to have this information in a public folder of Outlook contacts also.
The proprietary system has some features that will let me export the data to a CSV file - name, address, phone number, etc.
I would like to automate the process of importing this data to Outlook. I did a dry run with the Outlook import tool, and it's pretty close. But I think to truly automate it (and remove office workers from the equation) I will need to use some script or some such.
I programmed in the (distant...) past, and have read through posts suggesting that this can be done. In my head, I imagine a solution where I write a VBS script to read the CVS file and use the Outlook model to write it out. Is this the way to go? If so, can someone give me a sample script I could modify to match the fields I have?
My CSV today has stuff like this...
First Name Middle Name Last Name Suffix Company Name Phone Number Fax Number Mobile Phone Business Street Business Street 2 Business City Business State Business Postal Code Email Address
Thanks for ideas and help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not familiar with VBA code, Sorry ..
Try to use that code as it is and see if that would first solve your issue and then work on tweaking..
SR
Try to use that code as it is and see if that would first solve your issue and then work on tweaking..
SR
As far as format required for input, export several contacts from Outlook into a .csv file and look at teh column headings.
ASKER
Thanks SR,
I finally decided that scripts weren't going to do it, so took a look at the other references and began coding a VB app. In case it will help anyone else, here's the 'proof of concept' test I wrote.....
Dim strLine As String, strData() As String
Dim colItems As Items
Dim oOutlook As New Outlook.Application
Set colItems = oOutlook.GetNamespace("MAP
GetDefaultFolder(olFolderC
' Open the text file:
Open "C:\Outlook.csv" For Input As #1
' Discard the first line since that's the header info
Line Input #1, stgrLine
' Grab the data outta the file one line at a time until you hit the EOF mark:
While Not EOF(1)
' Read a line
Line Input #1, strLine
' Split the line into an array containing the data:
strData = Split(strLine, ",")
' Write Contacts to Outlook
With colItems.Add
.FirstName = strData(0)
.MiddleName = strData(1)
.LastName = strData(2)
.Save
End With
I finally decided that scripts weren't going to do it, so took a look at the other references and began coding a VB app. In case it will help anyone else, here's the 'proof of concept' test I wrote.....
Dim strLine As String, strData() As String
Dim colItems As Items
Dim oOutlook As New Outlook.Application
Set colItems = oOutlook.GetNamespace("MAP
GetDefaultFolder(olFolderC
' Open the text file:
Open "C:\Outlook.csv" For Input As #1
' Discard the first line since that's the header info
Line Input #1, stgrLine
' Grab the data outta the file one line at a time until you hit the EOF mark:
While Not EOF(1)
' Read a line
Line Input #1, strLine
' Split the line into an array containing the data:
strData = Split(strLine, ",")
' Write Contacts to Outlook
With colItems.Add
.FirstName = strData(0)
.MiddleName = strData(1)
.LastName = strData(2)
.Save
End With
ASKER
That does look most promising. Can I use this code as subroutines in a "whatever.vbs" script running under WSH? I have gotten a bit confused lately -- some are designed to run as VBA/macro stuff under Outlook. If that's true, then it looks like a little tweaking and some startup routines and I would be there.
LM