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
 
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.
 
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
 
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.
 
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
 
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.
 
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
 
Ok ,see the Recipients object, fill it with the addresses returned by Restrict, and pass that one to the SelectNamesDialog.
 
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.
 
Not finding any way to create that temporary address list either. I can add to an exiting list, but not create a new one.
 
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.
 
Yep, that fails too because of type mismatch; not an addresslist object required by InitialAddressList.
 
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.
 
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?
 
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.
 
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
 
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.
Similar threads
Thread starter Title Forum Replies Date
cheapfaremart Which outlook version is best? Using Outlook 0
P Best configuration for Outlook 2016, Gmail and iCloud Using Outlook 8
P Outlook room resource calendars and best practices Exchange Server Administration 0
Diane Poremsky The Best Outlook Store Add-ins Contest Using Outlook 0
Patrick van Berkel Best way to share (and keep up-to-date) Macro's in Outlook 2010 Outlook VBA and Custom Forms 6
S Best method to use Outlook Standalone with iPhone Using Outlook 13
jobudge Reinstalled Outlook 2013 after system crash what is best way to synch with gmail? Using Outlook 1
G what is the best way to organize outlook contacts Using Outlook 1
M Best way to move outlook? Using Outlook 1
R Best way to share outlook calendar with 10 laptops Using Outlook 9
M Outlook 2007 / Vista / Windows XP best combo needed BCM (Business Contact Manager) 2
Horsepower What is best calendar practice for iMac Using Outlook 3
J What is the best EntryID format to set on MS Access table Outlook VBA and Custom Forms 3
B What is best IMAP .OST file to .PST file converter solutions? Using Outlook 1
Rupert Dragwater What is the best way to back up pst Using Outlook 4
oliv- Best practice for catching mailitem.events Outlook VBA and Custom Forms 0
M What is the best way to find all records of an e-mail for our company? Outlook VBA and Custom Forms 2
J Best approach for restarting custom task form development after seemingly corrupt form environment Outlook VBA and Custom Forms 0
X Best utility for repairing PST files? Using Outlook 3
D a suggestion ...sent with the best intentions Using Outlook 1
M How best to track OUTGOING referrals? BCM (Business Contact Manager) 2
M best duplicate contact remover? Using Outlook 6
M Best way to identify/designate contacts in incoming emails? Using Outlook 4
P Best way to locally backup/archive Gmail? Using Outlook 1
M Looking for options and best practices for an Edge Server (Exchange or not) Exchange Server Administration 0
O What are best practices for a re-install Office 13 w/BCM with OS re-install BCM (Business Contact Manager) 1
D What would be the best strategy? Using Outlook 3
crazyboy BCM Installation Best Practices? BCM (Business Contact Manager) 5
J Best way to move form old to new, larger pst? Using Outlook 2
B Best way to get a pick-list on a form? Outlook VBA and Custom Forms 1
M Best way to distribute VBA project Outlook VBA and Custom Forms 4
E Where is the best place to store values for combobox entries Outlook VBA and Custom Forms 5
E Where is the best place to store values for combobox entries Outlook VBA and Custom Forms 5
T Is Explorer.FolderSwitch the best event for hiding commandbarbutton? Outlook VBA and Custom Forms 5
C Form Region, Task Pain, Ribbon Extension - which way is best? Outlook VBA and Custom Forms 1
A Outlook can't remember outlook.com, Exchange password. Using Outlook 0
S Related messages show in main Outlook window vice new Advanced Find windows Using Outlook 1
H Force Outlook 2019 with GMail 2-Step to Require Login? Using Outlook 0
G Retaining Tabs in outlook body Using Outlook 2
V Setting up Outlook 2021 on new computer Using Outlook 2
G Add Map It button to Custom Contacts Form in Outlook Outlook VBA and Custom Forms 1
X Custom icon (not from Office 365) for a macro in Outlook Outlook VBA and Custom Forms 1
Victor_50 Problem - Google Workspace will stop "unsafe" access to Outlook end 2024 Using Outlook 3
C New pc, new outlook, is it possible to import auto-complete emailaddress Using Outlook 4
T Outlook 365 won't take new working password Using Outlook 0
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
P Can't add custom field to custom Outlook form, it always adds to the Folder instead Outlook VBA and Custom Forms 2
B Sync Outlook Public Folders to Contacts Using Outlook 2
D Delete Outlook emails from MS server Using Outlook 12
B Outlook tasks and PDF Using Outlook 4

Similar threads

Back
Top