Thread: Referencing other Office programs in a Excel VBA App

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!

On Mar 11, 12:04*pm, "Peter T" <peter_t@discussions> wrote:
> 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" <jor...@sbcglobal.net> wrote in messagenews:3dc93c48-5635-4028-a82d-a9f89ee5fa04@33g2000yqj.googlegroups.com...
>
>
>
> >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!- Hide quoted text -
>
> - Show quoted text -

Thank you, Pete. That's very helpful.