What is the best way to use Outlook address book to select customer and then open Excel

Status
Not open for further replies.

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Hello,

Looking for some advice here. We are needing to select a customer from our Outlook address book and pass the entire contact information to Excel to use there.

Would it be best to use a custom form in Outlook and have it trigger opening Excel?

Or is it better to reach over from Execl to Outlook and somehow select the desired customer?

I see it is possible to open an Outlook form from Excel, such as selecting email recipients to pull that information back. That is where we are looking at going, but we need the full contact information instead of just the email address.

Any suggestions out there? What pieces of Outlook should we be looking to call on?

Any links to similar examples would be appreciated as well.

Thank you,
Brian
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Which product are you working in when this need comes up? If you are in Excel, you can use VBA to grab the Outlook info. If you are in Outlook, you can send it to excel.

This example shows outlook to excel: http://www.slipstick.com/developer/code-samples/macro-export-outlook-fields-excel/

this is an excel macro that sends data to outlook: http://www.slipstick.com/developer/create-appointments-spreadsheet-data/ - it uses early binding so you need to set a Reference to outlook object model in Excel.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Hello Diane,

Been reading and ordering books, so making some more sense of this.

Here is more on our set up:
Outlook is shared with a dozen employees in different parts of the country.
In addition, Outlook is also synced with Google for some of the features they have available, especially phone apps.
Which is to say we use the shared Outlook information quite a bit now.
Need: We need an expanded Contact form showing more of the existing Contact fields from the standard Outlook Address Book.
Need: We want to make use of "User Field 1" setting it to "Customer Owner", "Customer Rep", "Vendor", "Contractor", etc. We often use lat - long as our customers are building in new sites, so User Field 2 is going to be the lat-long and User Field 3 a web address link to a map to the new site.
(The "Customer Owner" info would be used on the generated contacts for signature blocks, ect.)

We are using Excel to build estimates, purchase orders, etc.
Need: When we start a new estimate, we need to initially select a contact(s) from the Outlook Address Book, preferably by making a call from Excel to open the Outlook Contact form above (at least that looks like it is doable) for the particular group selected matching User Field 1.

I'm in hopes that it is possible to use Outlook's Contact Select form and Outlook's Contact Edit form so as to not have to recreate and maintain those in Excel. For example, start Excel, open the template there and select option to create a new estimate. This automatically saves a copy of the template as a workbook. Then Excel would open the correct Outlook form to select the "Customer(s)" to create the estimate for. If the user could add/edit the contact information via these Outlook forms at this time that would be great! Once selected the contact(s) information would be passed back to Excel and integrated into the estimate. Which after a great deal of input will generate an estimate proposal, perferably with Word, perferably in PDF format for emailing to the customer.

Is all of this going to be possible? What suggestions might you have?

Thanks much,
Brian
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Some more ideas. Diane already suggested to use Excel for your needs. Another benefit of it is that it's easier to deploy VBA with a Workbook than it would be for Outlook.

Userproperties aren't available for the address book. Also, with the Outlook object modell there isn't much you can do to find items in the adress book.

Userproperties are available for contact items in a contact folder. This requires that your contacts are stored in a public, or a shared contact folder all the users have access to. You can use the Restrict function to search for items matching User Field 1. Pass the email addresses of the result to the SelectNamesDialog object if there are multiple matches. Once a contact is selected, you can display and modifiy it by using Outlook's default contact form. See the ContactItem object in the object browser (f2) for the entire list of the names of the available contact properties.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Userproperties are available for contact items in a contact folder. This requires that your contacts are stored in a public, or a shared contact folder all the users have access to. You can use the Restrict function to search for items matching User Field 1. Pass the email addresses of the result to the SelectNamesDialog object if there are multiple matches. Once a contact is selected, you can display and modifiy it by using Outlook's default contact form. See the ContactItem object in the object browser (f2) for the entire list of the names of the available contact properties.
Bingo! Putting this together now. Thank you
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Brian, let's stay in this forum.

ShowOnlyInitialAddressList should do it. The same suggestion given for the ContactItem object applies to every other object: You can explore its methods and properties in the object browser. There you'd also get a detailed explanation and often code examples for each one.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Thanks for the help, but after 7 hours of searches and attempts I just don't see how "Items.Restrict" can work with the Outlook.AddressList. The addresslist is read only and I am not seeing anyway to simply create a temp address book to pull an address list from either. With the working contacts.items.restrict I can build my own window and go from there.

But, if I am missing something please let me know.

Code:
Option Explicit

Sub Contact_User1()
  
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
  
    Dim objNamespace As Object
    Set objNamespace = objOutlook.GetNamespace("MAPI")
  
'   Check for the default address list in the Contacts folder
    On Error GoTo ErrorHandler
    Dim objAddressList As Outlook.AddressList
    For Each objAddressList In Outlook.Application.Session.AddressLists
        If objAddressList.AddressListType = olOutlookAddressList Then
            If objAddressList.GetContactsFolder.EntryID = objNamespace.GetDefaultFolder(olFolderContacts).EntryID Then
                Exit For
            End If
        End If
    Next
    On Error GoTo 0
  
'   Find the desired Customer group
    Dim objContacts As Outlook.Folder
    Set objContacts = objNamespace.Session.GetDefaultFolder(olFolderContacts)
    Dim objItems As Outlook.Items
    Set objItems = objContacts.Items.Restrict("[User1] = 'Customer'")
    Dim objItem As Outlook.ContactItem
  
'   Debug.Print objContacts.Items.Count 'value = 26 <- full default address list
'   Debug.Print objItems.Count 'value = 4 <- restricted address list
  
    Dim objRestrictedList As Outlook.AddressList
'   Set objRestrictedList = objNamespace.Session.GetDefaultFolder(olFolderContacts) 'error - missmatched type - not address list
    Set objRestrictedList = objAddressList 'full default address list pulled in from check above
    Dim objRestrictedListEntries As Outlook.AddressEntries
'   Set objRestrictedListEntries = objRestrictedList.AddressEntries.Restrict("[User1] = 'Customer'") 'error - this object not supported - not an "items"
    Set objRestrictedListEntries = objRestrictedList.AddressEntries
    Dim objRestrictedListEntry As Outlook.AddressEntry
 
'   Display the desired Customer group
    Dim objDialog As Outlook.SelectNamesDialog
    Set objDialog = objNamespace.Session.GetSelectNamesDialog
    With objDialog
      
        .ShowOnlyInitialAddressList = True
        .InitialAddressList = objRestrictedList 'only Outlook.AddressList object accepted here
      
        .AllowMultipleSelection = False
        .Caption = "Select Customer Contact"
        .NumberOfRecipientSelectors = olShowNone
      
        .Display
      
    End With


    Set objAddressList = Nothing
    Set objContacts = Nothing
    Set objItems = Nothing
    Set objItem = Nothing
    Set objRestrictedListEntry = Nothing
    Set objRestrictedListEntries = Nothing
    Set objRestrictedList = Nothing
    Set objDialog = Nothing

ErrorHandler:
    Exit Sub
End Sub
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Ok ,see the Recipients object, fill it with the addresses returned by Restrict, and pass that one to the SelectNamesDialog.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Nope, that does not work either.

added this to With objDialog
Code:
For Each objItem In objItems
           .recipients.Add (objItem.Email1Address)
Next
I get back the one name I clicked on and the four names added above even though there is a limit of one.

Unless I can somehow create a temporary AddressList used by the InitalAddressList (and fill it with only the desired contacts) I see no way to do this within GetSelectNames

I'll just build my only selection window for this.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Not finding any way to create that temporary address list either. I can add to an exiting list, but not create a new one.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
I'm sorry, so I misunderstood the VBA help.

I didn't try it, however, acccording to the object browser, creating a new contact folder, and setting ShowAsOutlookAB should create a new address list. This one could be deleted.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Yep, that fails too because of type mismatch; not an addresslist object required by InitialAddressList.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
I think now it's your misunderstanding. If you create the contacts folder as mentioned, Outlook will create a new address list. Find it in the AddressLists collection, and pass that object to the InitialAddressList property, not the folder object.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Again I'm not finding any way to create a "new" folder (without Exchange?).

Here is what I can get close with, but not finding anything that will fix the objContacts2 errors:

Code:
Option Explicit

Sub Contact_User1()
  
    Dim idx As Integer
  
    Dim objOutlook As Outlook.Application 'Object
    Set objOutlook = New Outlook.Application '("Outlook.Application")
  
    Dim objNamespace As Outlook.Namespace
    Set objNamespace = objOutlook.GetNamespace("MAPI")
  
    Dim objAddressList1 As Outlook.AddressList
    Dim objAddressList2 As Outlook.AddressList
  
'   Check for the default address list in the Contacts folder
    On Error GoTo ErrorHandler
        For Each objAddressList1 In Outlook.Application.Session.AddressLists
            If objAddressList1.AddressListType = olOutlookAddressList Then
                If objAddressList1.GetContactsFolder.EntryID = objNamespace.GetDefaultFolder(olFolderContacts).EntryID Then
                    Debug.Print objAddressList1.Name
                    Exit For
                End If
            End If
        Next
    On Error GoTo 0
  
'   Find the desired Customer group
    Dim objContacts1 As Outlook.MAPIFolder
    Dim objContacts2 As Outlook.MAPIFolder
  
    Set objContacts1 = objNamespace.GetDefaultFolder(olFolderContacts)
    Set objContacts2 = objNamespace.GetDefaultFolder(olFolderContacts) ' needs to be new folder but not finding anything that works
  
  
    Dim objDialog As Outlook.SelectNamesDialog
    Set objDialog = objNamespace.Session.GetSelectNamesDialog
  
    With objContacts1
        .AddressBookName = "AddressList1"
        .Items.Restrict ("[User1] = 'Customer'")
        ' COPY TO objContacts2 ???
    End With
  
    With objContacts2
        .ShowAsOutlookAB = True
        .AddressBookName = "AddressList2"
    End With

'   Check for the new address list in the Contacts folder
    On Error GoTo ErrorHandler
        For Each objAddressList2 In Outlook.Application.Session.AddressLists
            If objAddressList2.AddressListType = olOutlookAddressList Then
                If objAddressList2.Name = "AddressList2" Then
                    Debug.Print objAddressList2.Name
                    Exit For
                End If
            End If
        Next
    On Error GoTo 0

    With objDialog 'SELECT NAMES DIALOG
       .ShowOnlyInitialAddressList = True
       .InitialAddressList = objAddressList2
       .AllowMultipleSelection = False
       .Caption = "Select Customer Contact"
       .NumberOfRecipientSelectors = olShowNone
       .Display
    End With

    For idx = 1 To objDialog.recipients.Count
        Debug.Print idx & ": " & objDialog.recipients(idx)
    Next idx

    Set objContacts2 = Nothing
    Set objContacts1 = Nothing
    Set objAddressList2 = Nothing
    Set objAddressList1 = Nothing
    Set objNamespace = Nothing
    Set objOutlook = Nothing

ErrorHandler:
    Exit Sub
End Sub

Nesting did not work either:
Code:
    Set objContacts1 = objNamespace.GetDefaultFolder(olFolderContacts)
    With objContacts1
        .AddressBookName = "AddressList1"
        .Items.Restrict ("[User1] = 'Customer'")
        With objDialog 'SELECT NAMES DIALOG
           .ShowOnlyInitialAddressList = True
           .InitialAddressList = objAddressList1
           .AllowMultipleSelection = False
           .Caption = "Select Customer Contact"
           .NumberOfRecipientSelectors = olShowNone
           .Display
        End With
    End With

Other ideas?
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
A Folder object has a Folders collection for its subfolders, and the Folders collection has an Add method for adding a folder. It returns a folder object, which cannot be passed directly to an AddressList, but you should be able to find its name in the AddressLists collection, get the corresponding AddressList object and pass that one to the dialog.
 

bnj1776

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Right. Here is an example of how: http://www.jpsoftwaretech.com/look-for-and-create-folders-programmatically-in-outlook/ that I followed.

Code:
Dim objContacts1 As Outlook.MAPIFolder
    Set objContacts1 = objNamespace.GetDefaultFolder(olFolderContacts)
'   Set objContacts1 = objNamespace.GetDefaultFolder(olFolderInbox) 'just to test it
 
    Dim objContacts2 As Outlook.MAPIFolder
'   Set objContacts2 = objContacts1.Folders.Add("TempContacts", olFolderInbox) 'just to test WORKS
    Set objContacts2 = objContacts1.Folders.Add("TempContacts", olFolderContacts) ' FAILS
Create an Inbox, no problem. However creating a Contacts folder fails every time.
Run-time error '-2147352567 (80020009)':
Cannot create the folder
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Are you sure the folder "TempContacts" doesn't exist already? Call just
Code:
Set objContacts2 = objContacts1.Folders.Add("TempContacts")
If that fails for a non-existing folder name, something's wrong with your account.
 
Status
Not open for further replies.
Top