MS Outlook Contact Import from Excel with VBA-User Defined Fields

I have tested the following code and it will work fine, however I don't see how I can include mapping to the user-defined fields I created.

All the standard fields are available to me i.e. Company, address, Email, ect.... but I can't seem to locate how Outlook stores the User-Defined fields. As an Example I created a field called "2014 Sales"  I can directly enter data into this field, but how would I do it in VBA?

Sub ExcelWorksheetDataAddToOutlookContacts2()
'Automating Outlook from Excel: This example uses the Items.Add Method to export data from an Excel Worksheet to the specified (not necessarily default) Contacts folder.
'Automate using Early Binding: Add a reference to the Outlook Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook's predefined constants. Once this reference is added, a new instance of Outlook application can be created by using the New keyword.


'Ensure that the worksheet data to be posted to Outlook, starts from row number 2:

'Ensure corresponding columns of data in the Worksheet, as they will be posted in the Outlook Contacts Folder:
'Column A: First Name
'Column B: Last Name
'Column C: Email Address
'Column D: Company Name
'Column E: Mobile Telephone Number

Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.folder
Dim subFolder1 As Outlook.folder
Dim delFolder As Outlook.folder
Dim cItem As Outlook.ContactItem
Dim delItems As Outlook.Items
Dim lLastRow As Long, i As Long, n As Long, c As Long

On Error Resume Next


'determine last data row in the worksheet:
lLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

'Create a new instance of the Outlook application. Set the Application object as follows:
Set applOutlook = New Outlook.Application

'use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set nsOutlook = applOutlook.GetNamespace("MAPI")

'----------------------------

'Empty the Deleted Items folder in Outlook so that when you quit the Outlook application you bypass the prompt: Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?

'set the default Deleted Items folder:
Set delFolder = nsOutlook.GetDefaultFolder(olFolderDeletedItems)
'set the items collection:
Set delItems = delFolder.Items

'determine number of items in the collection:
c = delItems.Count
'start deleting from the last item:
For n = c To 1 Step -1

delItems(n).Delete

Next n

'----------------------------

'set reference to the folder (named "cont") where a new item is to be created:
Set cFolder = nsOutlook.Folders("mcaliebe@brennaninc.com")
Set subFolder1 = cFolder.Folders("TDF Accounts")

'post each row's data on a separate contact item form:
For i = 2 To lLastRow

'Use the Items.Add Method to create a new Outlook contact item in a specific Contacts folder (Export Excel Worksheet Data To Outlook Contacts).
'Note that if the item type is not specified after Add [viz. Add(olContactItem)], it defaults to its parent folder's type:
Set cItem = subFolder1.Items.Add
'display the new contact item form:
cItem.Display
'set properties of the new contact item:
With cItem

.firstName = Sheets("Sheet1").Cells(i, 1)
.LastName = Sheets("Sheet1").Cells(i, 2)
.Email1Address = Sheets("Sheet1").Cells(i, 3)
.CompanyName = Sheets("Sheet1").Cells(i, 4)
.MobileTelephoneNumber = Sheets("Sheet1").Cells(i, 5)

End With
'close the new contact item form after saving:
cItem.Close olSave

Next i

'quit the Oulook application:
applOutlook.Quit

'clear the variables:
Set applOutlook = Nothing
Set nsOutlook = Nothing
Set cFolder = Nothing
Set subFolder1 = Nothing
Set delFolder = Nothing
Set cItem = Nothing
Set delItems = Nothing



End Sub

Open in new window

MCaliebeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexei KuznetsovMicrosoft Outlook MVPCommented:
You either need to use ContactItem.UserProperties.Add or use ContactItem.PropertyAccessor.SetProperty
MCaliebeAuthor Commented:
Would you have an example of how I might include this in the code?
Alexei KuznetsovMicrosoft Outlook MVPCommented:
Sure. Here is the example for the property of string type:
...
Set prop = cItem.UserProperties.Add("Sales2014", olText)
prop.Value = "Test Value"
...

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.