Help with Code

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
Here's my spreadsheet. All outlook data comes back OK EXCEPT Company (Name),, WHY?

See my Code at bottom
Access_Outlook_Data_from_Excel.xls
ABCD
2WhattoCominginfrom
3NameinAddressBookRetrieveOutlookSTATUS
4BruceCodye-mailbcody@arcwoodva.comO/K
5JobTitlePresidentO/K
6Business(540)989-1040O/K
7Home(540)989-0257O/K
8Cell(540)819-6622O/K
9Companybcody@arcwoodva.comNOT
10
11
12StanleyG.Boatwrighte-mailstan@sgb-cpa.comO/K
13JobTitleOwnerO/K
14Business(540)342-6411O/K
15Home(540)774-6732O/K
16Cell(540)354-6732O/K
17Companystan@sgb-cpa.comNOT
18
19
20JimMaye-mailjmay@jmmay.comO/K
21JobTitleOwnerO/K
22Business(540)342-2687O/K
23Home(540)342-2687O/K
24Cell(540)314-3242O/K
25Companyjmay@jmmay.comNOT
Sheet1



Code:
Function GetContactInfoFromOutlook(strFullName As String, strReturnItem As String) As String
Dim OLF As Object, olContactItem As Object
Dim OK As Boolean, i As Long, strResult As String
    On Error Resume Next
    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(10)
    If OLF Is Nothing Then
        Set OLF = CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(10)
    End If
    On Error GoTo 0
    If Not OLF Is Nothing Then
        With OLF
            OK = False
            i = 0
            Do While i< .Items.Count And Not OK
                i = i + 1
                On Error Resume Next
                Set olContactItem = .Items(i)
                On Error GoTo 0
                If Not olContactItem Is Nothing Then
                    With olContactItem
                        If .FullName = strFullName Then
                            OK = True
                            Select Case LCase(strReturnItem)
                                Case "mail", "e-mail"
                                    strResult = .Email1Address               'OK
                                Case "phone", "home phone", "home"
                                    strResult = .HomeTelephoneNumber         'OK
                                Case "mobile", "cell", "cellphone", "carphone"
                                    strResult = .MobileTelephoneNumber    'OK
                                Case "business"
                                    strResult = .BusinessTelephoneNumber   'OK
                                Case "Job Title"
                                    strResult = .JobTitle                 'OK
                                Case "Company"
                                    strResult = .CompanyName             'NOT WORKING
                                Case "Fax"
                                    strResult = .BusinessFaxNumber      'NOT WORKING - REMOVED
                                Case "job title"
                                    strResult = .JobTitle                 'OK
                                                                ' add more if necessary
                                Case Else ' default result
                                    strResult = .Email1Address
                            End Select
                        End If
                    End With
                    Set olContactItem = Nothing
                End If
            Loop
        End With
        Set OLF = Nothing
    End If
    GetContactInfoFromOutlook = strResult
End Function
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
Never mind - I was not using the "lowercase" in my Case select for Company, as company,,,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top