Access VBA code for Outlook Object Library - look up name & manager

Posted on 2007-07-24
Medium Priority
Last Modified: 2013-11-27
Using Microsoft Acess 2003, VBA coding on Windows XP Professional with Microsoft Outlook 11.0 Object Library.

Trying to find the VBA code to (1) look up a person's name in the address book (2) look up the person's manager as I wish to CC the manager.    

Started with:

  Dim MyOutlook As Outlook.Application
  Dim MyMail As Outlook.MailItem
  Dim MyRecipient As Outlook.Recipient
  Dim MyAddressList As Outlook.AddressLists
  Dim MyAddressEntry As Outlook.AddressEntry
  Set MyOutlook = New Outlook.Application
  Set MyMail = MyOutlook.CreateItem(olMailItem)

Question by:MrLogin
LVL 59

Accepted Solution

Chris Bottomley earned 750 total points
ID: 19563193
Assuming you are happy that the entries in the contacts folder are consistent, i.e. that manager names against a contact represent the string used for the manager name ... or that you have a field for each contact with the manager email, (let me know if it's unclear how to change) then the following works during a trial with dummy data so should give you what you want:

Sub read_contact()
Dim MyOutlook  As Outlook.Application
Dim outlook_namespace As NameSpace
Dim outlook_contacts As MAPIFolder
Dim con As Outlook.ContactItem
Dim manager_name As String
Dim manager_email As String

    Set MyOutlook  = GetObject(, "outlook.application")
    If MyOutlook  Is Nothing Then Set MyOutlook  = CreateObject("outlook.application")
    Set outlook_namespace = outlook_app.GetNamespace("MAPI")
    Set outlook_contacts = outlook_namespace.GetDefaultFolder(olFolderContacts)
    For Each con In outlook_contacts.Items
        If con.FullName = recipient_name Or con.Email1Address = recipient_email Then
            manager_name = con.ManagerName
        End If
    For Each con In outlook_contacts.Items
        If con.FullName = manager_name Then
            manager_email = con.Email1Address
        End If
End Sub


Author Comment

ID: 19570857
Thanks Chris.

Your response provided sufficient insight to solve the problem, have included the test harness I used for completeness - points to you.


Sub Test_mail()

  Dim MyOutlook As outlook.Application
  Dim MyNameSpace As NameSpace
  Dim MyAddrList As AddressList
  Dim MyDistList As AddressEntry
  Dim MyListMember As AddressEntry
  Dim MyMail As outlook.MailItem
  Dim MyRecipient As outlook.Recipient
  Dim sUserName As String
  ' ---- Connect to Outlook
  Set MyOutlook = New outlook.Application
  Set MyNameSpace = MyOutlook.GetNamespace("MAPI")
  Set MyAddrList = MyNameSpace.AddressLists("Global Address List")
  ' ---- User name parameter
  sUserName = GetUserNameFromForm()
  ' ---- Set up mail item & resolve supplied name
  Set MyMail = MyOutlook.CreateItem(olMailItem)
  MyMail.Recipients.Add (sUserName)
  Set MyRecipient = MyMail.Recipients.Item(1)
  If Not MyRecipient.Resolved Then
    MsgBox "Please choose a valid name"
    Exit Sub
  End If
  ' ---- What is available in the distribution list
  Set MyDistList = MyAddrList.AddressEntries(MyRecipient.Name)
  ' ---- CC the manager if present
  If Not MyDistList.Manager Is Nothing Then
    MyMail.CC = MyDistList.Manager
  End If
  ' ---- Resolve a single entry or a distribution list
  If MyDistList.Members Is Nothing Then
    MsgBox MyDistList.Name & ", " & IIf(Not MyDistList.Manager Is Nothing, MyDistList.Manager, "")
     For Each MyListMember In MyDistList.Members
       MsgBox MyListMember.Name & ", " & IIf(Not MyListMember.Manager Is Nothing, MyListMember.Manager, "")
  End If
  MyMail.Subject = "Subject line"
  MyMail.Body = "Multiple line " & vbCrLf & "body"
  Set MyListMember = Nothing
  Set MyDistList = Nothing
  Set MyRecipient = Nothing
  Set MyMail = Nothing
  Set MyAddrList = Nothing
  Set MyNameSpace = Nothing
  Set MyOutlook = Nothing

End Sub

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
I came across an unsolved Outlook issue and here is my solution.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question