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!


0
A
3/11/2010 4:24:54 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1152 Views

Similar Articles

[PageSpeed] 58

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" <joran6@sbcglobal.net> wrote in message 
news: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!
>
> 


0
Peter
3/11/2010 6:04:51 PM
On Mar 11, 12:04=A0pm, "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 =3D GetObject(, "Outlook.Application")
> On Error GoTo 0
> If Not objOL Is Nothing Then
> Set objOL =3D CreateObject("Outlook.Application")
> End If
>
> Type a vraiable and you should start to see intellisence after the dot. H=
elp
> 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-563=
5-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 =3D CreateObject("Outlook.Application")
> > Set olNS =3D objOL.GetNamespace("MAPI")
> > Set myFolder =3D olNS.GetDefaultFolder(10)
> > Set myItems =3D myFolder.Items
>
> > myItems.Sort "FullName"
>
> > x =3D 0
> > For Each myContact In myItems
> > =A0 =A0If TypeName(myContact) =3D "ContactItem" Then
> > =A0 =A0 =A0 =A0If Len(myContact.Email1DisplayName) > 0 Then
> > =A0 =A0 =A0 =A0ListBox1.AddItem
> > =A0 =A0 =A0 =A0ListBox1.Column(0, x) =3D myContact.Email1DisplayName
> > =A0 =A0 =A0 =A0ListBox1.Column(1, x) =3D myContact.Email1Address
> > =A0 =A0 =A0 =A0x =3D x + 1
> > =A0 =A0 =A0 =A0End If
> > =A0 =A0End If
> > Next myContact
>
> > Set olNS =3D Nothing
> > Set objOL =3D 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.
0
A
3/11/2010 9:52:39 PM
Reply:

Similar Artilces:

Trouble loading Office for Mac
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I'm having trouble installing Microsoft Office for Mac. I put the DVD in the drive, and for about 10 or 15 seconds, it tries to read it (I hear noises), but than the DVD is ejected from the drive. I've tried 3 or 4 times, with no success. Any suggestions on how to fix this? Do you think it's a faulty DVD? Or something not set up correctly on my MacBook? Thank you, On 6/5/09 5:19 PM, in article 59b76053.-1@webcrossing.caR9absDaxw, "klco60@officeformac.com" <klco60@officeformac.com> wrote: > I'm having ...

Using Excel, how do I replace cells containing blanks with nulls?
I plan to use an Excel spreadsheetand cut and paste data into a database. I need to replace blank cells in the spreadsheet with nulls. How can I accomplish this? Select the range, press F5, special and Blanks, press enter, now type whatever you want and press ctrl + enter Regards, Peo Sjoblom "Nither1" wrote: > I plan to use an Excel spreadsheetand cut and paste data into a database. I > need to replace blank cells in the spreadsheet with nulls. How can I > accomplish this? If I understand you correctly, Excel doesn't support the concept of the NULL data ...

Excel 07
Having difficulties creating a line chart using daily balances, but I would like the x-axis to show the month-end date. My start date is 12/31/2007 and my end date is 6/30/2008. I have daily information that I would like plotted, however only the month-end date (1/31/2008, 2/28/2008, etc) to show. I have experimented with the axis options (day, month, year), however it seems to default to the 1st day of the month. If using every 30 days, it also becomes off. Please help. I'm using an existing chart from Excel 03, and it will not convert properly...I've also tried creat...

Excel and Access Linking
I am interested in linking the two together for a project at work. I work for a construction company and I need to keep track our employees and the work they perform. I also need to do this for our subcontracted work also. We have about 95 clients and about 15 different crews in-house. I am looking for some good reference books or web site linkings on Access and Excel. I enjoy this forum nad have found many useful topics. --- Message posted from http://www.ExcelForum.com/ ...

"Cleaning" an Excel workbook
We are going to be entering data in an Excel spreadsheet, and need to guarantee accuracy. Our plan is to create two identical spreadsheets, have two people enter data, then check that they are the same - but how??? By merging them? Or is there a better way to do this check? Please help - thanks! If you mean cell by cell comparison (a1 with a1, x99 with x99), you can use a program written by Myrna Larson and Bill Manville. You can find a copy on Chip Pearson's site: http://www.cpearson.com/excel/whatsnew.htm look for compare.xla Pizza wrote: > > We are going to be enteri...

Excel Rep Builder Not Displaying in SharePoint 2010 Excel Services
I am having difficulty viewing an Excel Report Builder report in Excel Services on SharePoint 2010. The error states that Excel Services does not support “Query Tables (also known as “External Data Ranges”). I know that SP 2010 can access external data sources (there is even a place to install the ODC file, separately)… so I don’t know why a simple Exel Reports Builder won’t work. Any suggestions? ...

Can you plot a graph with 3 axes e.g. xyz values on Excel?
Can you plot a graph with 3 axes e.g. xyz values? Excel doesn't have a native 3D XYZ chart format, but Andy Pope shows how to fake it on his web site http://andypope.info - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Khan wrote: > Can you plot a graph with 3 axes e.g. xyz values? In article <8CD24A5A-1697-4A3A-A3B7-DBD78C310482@microsoft.com>, Khan@discussions.microsoft.com says... > Can you plot a graph with 3 axes e.g. xyz values? > For an understanding of what you can do...

excel vba
Here is what happens: 1. user opens program 2. userform pops up asking retail, trade, or account 3. depending on which is chosen, certain cells/formats get reformatte specific ways 4. user clicks on command button for clearing the page, causes certain cells to become unprotected and then protects the sheet 5. user goes through and inputs info into non protected cells and the clicks on save/print command button This is where the problem is arising, I don't know where and when should put codes to disable the protection in order for certaing cell to change. For example, I have a code set u...

Excel charts and Word docs
In Office 2003, how do I import an excel table into a word doc? In Ms Word go to Insert - Object - Create from File and select the xls file that contains your table. "Frustrated" wrote: > In Office 2003, how do I import an excel table into a word doc? Copy the range in Excel, switch to Word, and paste. Word puts the Excel data into a Word table. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Frustrated" <Frustrated@discussions.microsoft.com> wrote in message news:90F...

In EXCEL, access denied when deleting read only
When I try to remove the read only designation on a spreadsheet, I keep getting Access Denied message. Any help appreciated. ...

how do i increase the rows in a excel balance sheet?
i've prepare a balance sheet for my company.In the excel balance sheet i can't enter more then 12 rows. what can i do/ "dass" <dass@discussions.microsoft.com> wrote in message news:33689BBB-8071-4E51-8D31-17579EB2BFF5@microsoft.com... > i've prepare a balance sheet for my company.In the excel balance sheet i > can't enter more then 12 rows. what can i do/ Could you be a bit more specific? Excel (at least in 2003 and prior) has over 60 THOUSAND rows. What EXACTLY do you mean by "can't enter more than 12 rows"? Excel 95 and earlier h...

Making PDFs in Office 2008
I wonder if the PDF saving capablities have improved in Office 2008. In 2004 the PDF Maker buttons (and commands) on the Mac version lacked support for Bookmarks. Only links in a TOC came through. The Windows version offered much more of these options, like Bookmark derived from a document's structure (headings). The Save as PDF dialogue in 2008 only produces a PDF with visual content, still no navigational features. I haven't tried reinstalling my Acrobat Pro (8) yet. I'm not sure how Acrobat integrates with Word, but I would assume that it is through VBA, and in that case, it is...

Opening Office Files
I typically open my documents/excel files/power point by clicking on the file directly. Everytime I do I get the following message: "Please wait while Windows configures Microsoft Office Professional Hybrid 2007". This causes a long delay in opening the file and my previous system(XP) did not perform this task. My new system is Vista. I also have a problem every time I open Outlook, it starts installing Visio and PowerPoint. Any reason why it should do any of these? How do I alleviate these from happening? Thanks, -- Gevan "Gevan" <Gevan@discus...

Clip Art Gallery Issues (Office 2004)
Sorry if this has been discussed before, could not find anything in the newsgroup. I have attempted to download clip art from the Microsoft website and once downloaded to the desktop I cannot import it into the gallery. I open Word and go to insert -> Picture -> clip art and then import the file, and I get an error message stating " Attempt to open database file clipart failed" When I try to import by running the Clip Gallery application, it refuses to import because "it can only be run within office". Is there a workaround for this? Thanks in advance. R Burgos ...

Facing problem in Back Office 4.3
I could not start service in Back Office 4.3 Ver If I start remote access service, It is giving error 1066 what is the resion for this, please let me know, immediately. Thanking you Jagadish ...

Excel links
I am generating a spreadsheet in excel 2007 and sending it to one of my subs. They then take info from my sheet and from another sheet and link it to a third sheet that is in excel 2003. The problem arises when she sends the spreadsheet with the new links back. We open the sheet in 2007 and we the pound sign and the word value instead of the new linked number. We have tried to open the sheet in the 2003 compatibality mode but that will not work. Any Ideas. "Any Ideas" Yes: stop switching between the versions; you are asking for trouble. "Perplexed" ...

Search in Excel
If I search in columns which cover over one screen window, after first Find in column A, do other operations, then Find column A again, Find will start from two different locations 1. Find from beginning of column 2. Find from beginning of current windows of column Obviouly, 2 is better than 1. You can start Find from location near your last Find It seems that Find isn't stead in these two state, is it Shall I control the state of Find Thanks. Victor, I don't know what other operations you do, but as far as I know :whenever you restart Find after doing some operations, it starts f...

Excel validation using multiple conditions
Hi I am faced with a situation where i need some help. I a having a worksheet which calculates the number of leave accumulated fo an employee. when we choose a particular option for the employee, say "xx" whe he/she works overtime, right now i have set up a formula to calculat the number of times this occurs. I need help to further enhance this functionality. Based on the date o which the "xx" is entered into a particular employee's row, i need t create another column which validates the particular date and check whether more than a month has elapsed from the date...

Multiple excel users can open the same file and edit at the same .
We are running Excel 2002 SP2. We recently moved to a Windows 2000 server and now we are running into the issue where Multiple users can open the same excel file and edit it at the same time without getting the file the 2nd time in Read Only mode. Go to Tools>Share Workbook & choose the setting to Allow changes by more than one user. The file must also be stored in a Shared network/workgroup folder. Save the fil. It is also strongly recommended that you research the feature in Excel Help to become more familiar with it before you start sharing the file. HTH |:>) "Jef...

CRM on Office 2003
Hi everyone, Currently MS CRM is not supported on Outlook 2003. After installing the software and starting Outlook you get an error "Microsoft CRM folders cannot be installed". I found that every time I close and open Outlook (after saying no to not disable the add-in), 1 more folder is added to the Microsoft CRM Folders. I did this about 13 times and it added all the folders required. After that I could see the toolbar and it looks like everything is working fine. Regards Shane Coetser lol thats persistence "Shane Coetser" <shanec@oakwell.co.za> wrote in m...

Import from Excel to Windows live outlook
How to import contacts from an Excel Spreadsheet to Windows live Outlook No such animal as "Windows live Outlook". There is Microsoft Outlook, and you will find the option under File, Import and Export. And there is Windows Live Mail, and there you could probably look in Help or post your question to the Live Mail group. Either way, "Excel" isn't actually involved and isn't needed. Import from Excel wrote: > How to import contacts from an Excel Spreadsheet to Windows live Outlook If you mean Office Outlook............... See Ron's site ...

Where do I get the brochure options in the program?
I can't finds the options for color schemes. "Help" <Help@discussions.microsoft.com> wrote in message news:1559D466-0162-46B5-931A-21C2E13BFD51@microsoft.com... >I can't finds the options for color schemes. What version? ...

In Excel the Y axis title box cuts off the last letter.
The Y axis title box always cuts off the last letter no matter what type or size of font. It doesn't matter how many letters either 5 or 15, the last one is aways missing. A known bug (only some users experience it). Workaround: add some dots (.....) to end of the title and format them with font of the same colour as the title background. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "maggie" <maggie@discussions.microsoft.com> wrote in message news:0B698A36-4475-4DA3-A2B1-6DDCC9185E75@microsoft.com... > The Y axis title box al...

Re: XP style controls in non-unicode app
I found that if I add the following lines near the bottom of my stdafx.h file I get the new XP style controls in my MFC app: #if defined _M_IX86 #pragma comment(linker,"/manifestdependency:\"type='win32' name='Microsoft.Windows.Common-Controls' version='6.0.0.0' processorArchitecture='x86' publicKeyToken='6595b64144ccf1df' language='*'\"") #elif defined _M_IA64 #pragma comment(linker,"/manifestdependency:\"type='win32' name='Microsoft.Windows.Common-Controls' version='6.0.0.0' processorArc...

Decision Trees in excel
Help! How do I make decision trees in Excel? I know it's possible but can't find it anywhere. Thanks... That's not much info to work with, so you get a very generic answer: =IF(condition,result if true, result if false) condition can be something like A1<50 result can be a value or a formula (including another IF() function) I hope this helps. Post specifics with your questions, and you will get better answers. "Dani" <anonymous@discussions.microsoft.com> wrote in message news:077b01c3b6eb$979aa4d0$a001280a@phx.gbl... > Help! How do I make decisio...