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

Posted on 2007-07-24
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

    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

    LVL 1

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
    Set OWA language and time zone in Exchange for individuals, all users or per database.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now