Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing other Office programs in a Excel VBA App
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing other Office programs in a Excel VBA App
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" wrote in ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the size of the font in office programs | Excel Programming | |||
Universal VBA code for opening MS (Office ) programs from excel | Excel Programming | |||
Missing programs after downloading open office | Setting up and Configuration of Excel | |||
Macros across office programs... | Excel Programming | |||
Excel Programs developed in Office 2000 on Windows 2000 | Excel Discussion (Misc queries) |