PaulRKrueger
asked on
VBS Contact Import to Public Folder
I am working on an import script based on several scripts I've found on here so far, but I need some help. The script already deletes all contact items in a public folder and I have the csv file with the information that needs to be loaded. Unfortunately I haven't been able to work out the import routine. I've removed my attempts so that they don't confuse the situation.
I need the information in the csv file loaded as outlook contacts. I would like the script to ignore contacts without an entry in the sn column. Lastly, I need this to be a standalone script (not an Outlook macro) as this will be an automated process.
I need the information in the csv file loaded as outlook contacts. I would like the script to ignore contacts without an entry in the sn column. Lastly, I need this to be a standalone script (not an Outlook macro) as this will be an automated process.
Const olFolderContacts = 10
Const olPublicFoldersAllPublicFolders = 18
Dim olkApp, olkSes, olkContacts, olkPublic, olkContact, intIndex
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon "Outlook"
Set olkContacts = olkSes.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("TBC Directory").Folders("TBC Directory")
'Delete all contacts
For intIndex = olkContacts.Items.count To 1 Step -1
olkContacts.Items(intIndex).Delete
Next
Set olkContacts = Nothing
Set olkPublic = Nothing
Set olkContact = Nothing
olkSes.Logoff
Set olkSes = Nothing
Set olkApp = Nothing
ASKER
That's all interesting, but I'm afraid I don't have the skills to handle this one.
You have a csv with contact information and you want to upload the data as new contacts in the public folder "olkContacts " as you have instantiated above?
If yes can you produce a subset of the csv with dummy data for test purposes and to assist in understand datums required.
Chris
If yes can you produce a subset of the csv with dummy data for test purposes and to assist in understand datums required.
Chris
ASKER
Yes, that is correct. You can see a dummy file attached to the question (contacts.csv). The field mapping is as follows (csv column headings on left, Outlook fields on right):
DisplayName -> Email Display Name
department -> Department
facsimiletelephonenumber -> Business Fax
givenname -> First Name
mail -> Email Address
mobile -> Mobile Phone
pager -> Pager
sn -> LAst Name
telephonenumber -> Business Phone
title -> Job Title
DisplayName -> Email Display Name
department -> Department
facsimiletelephonenumber -> Business Fax
givenname -> First Name
mail -> Email Address
mobile -> Mobile Phone
pager -> Pager
sn -> LAst Name
telephonenumber -> Business Phone
title -> Job Title
Dummy file ... ah! sorry about that.
I will work on a solution in the morning if nothing happens in the meantime ... though I expect it will.
Chris
I will work on a solution in the morning if nothing happens in the meantime ... though I expect it will.
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll play with it today.
ASKER
OK, so I made one change (Title -> Job Title) and everything seems to work just fine.
Case "title"
con.JobTitle = itemArray(itemCount)
Unfortunately I'm having a problem with my larger data set. Some of our titles contain commas in them (e.g. "Vice President, CFO").
The CSV file has the title in quotation marks as seen above. Right now the script does one of two things:
1. It puts the part of the title before the comma in there ("Vice President)
2. The script fails because it thinks there are too many values for the array (e.g. "DIR OF MKTG-ACC, WINE, REV MGT")
Is there anything that can be done to accommodate these titles? Ideally the full title without the quotes is what would end up in the Job Title field.
Case "title"
con.JobTitle = itemArray(itemCount)
Unfortunately I'm having a problem with my larger data set. Some of our titles contain commas in them (e.g. "Vice President, CFO").
The CSV file has the title in quotation marks as seen above. Right now the script does one of two things:
1. It puts the part of the title before the comma in there ("Vice President)
2. The script fails because it thinks there are too many values for the array (e.g. "DIR OF MKTG-ACC, WINE, REV MGT")
Is there anything that can be done to accommodate these titles? Ideally the full title without the quotes is what would end up in the Job Title field.
MIght be able to bodge it a little ... can you supply a csv file with some representative albeit false data?
My theory is on examination that perhaps I can substitute the data for commas within quotes but before trying I want to know the data I am trying with has the potential to capture the issue.
Chris
My theory is on examination that perhaps I can substitute the data for commas within quotes but before trying I want to know the data I am trying with has the potential to capture the issue.
Chris
ASKER
ASKER
Never mind! I changed the export to delineate using semicolons instead of commas and that did the trick.
That's good ... a simple solution is much better ... I was in fact testing a solution when I had a PC crash so I am just rebooting teh now.
Chris
Chris
https://www.experts-exchange.com/questions/22675705/Outlook-VBA-Import-CSV-into-Calendar.html