Referencing other Office programs in a Excel VBA App

[A Mad Doberman]

I din't like the way my previous post sounded, so i'm reposting...

All,

My Excel VBA programming is decent enough when I'm only working with
Excel.
However, I'm trying to expand my knowledge base and frequently I wish
I could better interact with other Office programs, such as Outlook,
in my Excel VBA apps.
To better illustrate my question, here's a piece of code that works
great (it populates a user form list with Outlook addresses) but I
don't fully understand.


Private Sub UserForm_Activate()


Dim x As Integer


Set objOL = CreateObject("Outlook.Application")
Set olNS = objOL.GetNamespace("MAPI")
Set myFolder = olNS.GetDefaultFolder(10)
Set myItems = myFolder.Items


myItems.Sort "FullName"


x = 0
For Each myContact In myItems
If TypeName(myContact) = "ContactItem" Then
If Len(myContact.Email1DisplayName) > 0 Then
ListBox1.AddItem
ListBox1.Column(0, x) = myContact.Email1DisplayName
ListBox1.Column(1, x) = myContact.Email1Address
x = x + 1
End If
End If
Next myContact


Set olNS = Nothing
Set objOL = Nothing


End Sub


My basic question is this: Can anyone suggest a good reference to
learn the uses and codes associated with objects, variables,
constants, etc..., which relate to programs outside Excel for use
within an Excel VBA app? For example, in the above code I do not
understand the precise usage of
GetNamespace("MAPI"), GetDefaultFolder(10), myFolder.Items,
Email1DisplayName, Email1Address, etc..., etc...


I don't like using code I don't understand for many obvious reasons.
Clearly, Email1DisplayName is a particular field in the OL Address
Book, but how do I learn what all of these fields are called? Where
do
I obtain syntax info for Outlook Object references? Etc.., Etc....I
think you'll all get what I'm asking.


Thanks, everyone!

 

[Peter T]

Add a reference to the Outlook object model to your project

Tools, References, scroll down and tick "Microsoft Outlook x.0"

In your code declare your object variables as you would with Excel, eg

Dim objOL As Outlook.Application
Dim myItems As Outlook.Items
Dim myFolder As MAPIFolder
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
On Error GoTo 0
If Not objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
End If

Type a vraiable and you should start to see intellisence after the dot. Help
should become avilable too in the same was as it does in Excel.

Once done and tested you can convert back to 'Late Binding', uncheck the
reference, declare those objects 'As Object' and change any named Outlook
constants to their intrinsic values.

In passing, with Outlook I think generally better to start by trying to
reference the existing running Outlook, if any. Hence use of GetObject

Regards,
Peter T

 

[A Mad Doberman]

Add a reference to the Outlook object model to your project

Tools, References, scroll down and tick "Microsoft Outlook x.0"

In your code declare your object variables as you would with Excel, eg

Dim objOL As Outlook.Application
Dim myItems As Outlook.Items
Dim myFolder As MAPIFolder
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
On Error GoTo 0
If Not objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
End If

Type a vraiable and you should start to see intellisence after the dot. Help
should become avilable too in the same was as it does in Excel.

Once done and tested you can convert back to 'Late Binding', uncheck the
reference, declare those objects 'As Object' and change any named Outlook
constants to their intrinsic values.

In passing, with Outlook I think generally better to start by trying to
reference the existing running Outlook, if any. Hence use of GetObject

Regards,
Peter T















- Show quoted text -

Thank you, Pete. That's very helpful.