asked on

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

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)

Thanks

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrLogin

ASKER

Thanks Chris.

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

Cheers

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)
MyMail.Recipients.ResolveAll
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, "")
Else
For Each MyListMember In MyDistList.Members
MsgBox MyListMember.Name & ", " & IIf(Not MyListMember.Manager Is Nothing, MyListMember.Manager, "")
Next
End If

MyMail.Subject = "Subject line"
MyMail.Body = "Multiple line " & vbCrLf & "body"
MyMail.Display

Set MyListMember = Nothing
Set MyDistList = Nothing
Set MyRecipient = Nothing
Set MyMail = Nothing
Set MyAddrList = Nothing
Set MyNameSpace = Nothing
Set MyOutlook = Nothing

End Sub

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

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