HOME | SIGN UP | ACCOUNT | SUBSCRIBE | TROUBLESHOOTING | LOGIN
News · Meetings · Jobs · Downloads · Universal Thread articles · Web articles · Conference coverages · Consultants · Blogs · FAQ · User groups · Photos · Videos

VFP and Office OLE Automation

José Cavalcanti, Global Connection
José Augusto Cavalcanti is director of Global Connection Consultoria em Informática Ltda. and TI Manager of Daniel Advogados. He's a member of FoxBrasil User Group and Universal Thread and develop using Visual Foxpro since version 2.5 for DOS. He's specilized in development using "frameworks" and OLE Automation. He can be contacted at jose.cavalcanti@globalconnection.com.br and jose.cavalcanti@daniel-advogados.com.br.

Introduction

I was eager to write this article some time ago (actually, a LONG time ago), after going through hundreds (or thousands) of messages posted at FoxBrasil User Group, with the subject "How can I send messages within VFP?".

Whoever knows me, knows that I love the "OLE Automation" subject. What does OLE Automation mean? OLE is the acronym for Object Linking and Embedding. Great, you say, but what does it actually mean? OLE Automation could be defined (in my own terms) as the capability of an application to expose its functionality (PEM – Properties, Events and Methods) to another application. It would be like if we could pack Microsoft Word into an object and simply insert it into a VFP form (or VB, VBA, JavaScript etc.).

Just an example, let's pretend that for a payment control system (for a magazine) it is necessary to remind someone in billing that at a certain day he/she has to verify if the bank vouchers sent to the subscribers have been paid. The date is not fixed for all subscribers and it is based on the day the subscription started. Based on this scenario, it seems that you would have to built in a calendar within the system, so the reminder would work properly, correct? Not necessarily, because the user uses Microsoft Outlook 2000 and it is an OLE server, i.e., you could (within the VFP) instruct Microsoft Outlook 2000 to execute a specific task as, for example, add an entry to the Calendar, fill out the necessary data and save it.

And is it difficult? No, not really, as far as you have the OLE Server (Object Model) documentation handy. Most of the time, to obtain the documentation is the toughest job.

I intend to (depending on my free time and family patience) start writing articles on Microsoft Outlook Object Model, Microsoft Internet Explorer and Lotus Notes. As I just started studying the latter lately, I'm not sure if I will get to it. To make it simple, I will start these series of articles with Microsoft Outlook, and would like to mention that these series of articles were based on another series of articles written by Andrew Ross MacNeill, for FoxPro Advisor magazine.

Microsoft Outlook

Outlook is a productivity tool that includes Calendar, Messaging, Contacts and To-Do list. The Outlo oks 2000's Object Model (version I'm currently using) can be found in the file VBAOUTL9.CHM. As you can see in figure 1, the Application object is the top level object. This object is (by itself) worthless, but it is the main entrance to access Outlook. Although built with other functions, the Outlook is primarily an e-mail package, it uses MAPI (Messaging Application Programming Interface).

When Outlook is started, it creates a reference to an MAPI Namespace object. This object holds references to the location of Folders, items and configurations used by Outlook. Ready to have some fun? So check out the code below:

LOCAL	loApplication, loNameSpace, loContacts, loInbox, lcMessage

loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")

loContacts    = loNameSpace.GetDefaultFolder(10)
loInbox       = loNameSpace.GetDefaultFolder(6)

lcMessage     = "'Contacts' Folder´s Name: " + CHR(9) + loContacts.Name + CHR(13) + CHR(10)+;
                "'Inbox' Folder´s Name "    + CHR(9) + loInbox.Name

MSGSVC(lcMessage)

RELEASE ALL

RETURN

Figure 1 - Microsoft Outlook 2000 Object Model (I´m sorry, but I don´t have the English version).

First of all, we call Outlook and create an object Namespace (we always start this way) and then we use the GetDefaultFolder method to obtain the reference (another object) to some of the most commonl y used Folders. Finally we display those Folders names. Table 1 shows a list of possible parameters for the GetDefaultFolder method.

ParameterFolder
3Deleted Itens
4OutBox
5Sent Itens
6Inbox
9Calendar
10Contacts
11Journal
12Notes
13Tasks
16Drafts
This way of action works pretty well with standard Folders, but what if we want to know the Folders contained in your Inbox? For that we would have to use the Folders property that all Folder object have, see the sample below:
LOCAL	loApplication, loNameSpace, loInbox, lnCounter, loFolder
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loInbox       = loNameSpace.GetDefaultFolder(6)
FOR lnCounter = 1 TO loInbox.Folders.Count
    loFolder = loInbox.Folders(lnCounter)
    MSGSVC("Folder: " + loFolder.Name)
ENDFOR
RELEASE ALL
RETURN

First, we select Inbox, verify the number of Folders in it and display their names. It is good to remember that we can always reference a Folder by its number (Folders(4)) or by its name (Folders("Inbox")).

Each Folder has a series of Items. We use the Count property to identify those items number. This way, we can slightly modify our program, so it will show the number of Items within each Folder:

LOCAL	loApplication, loNameSpace, loInbox, lnCounter, loFolder, lcMessage
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loInbox       = loNameSpace.GetDefaultFolder(6)
FOR lnCounter = 1 TO loInbox.Folders.Count
    loFolder  = loInbox.Folders(lnCounter)
    lcMessage = "Folder: " + CHR(9) + loFolder.Name + CHR(13) + CHR(10) +;
      "Items: "  + CHR(9) + STR(loFolder.Items.Count)
    MSGSVC(lcMessage)
ENDFOR
RELEASE ALL
RETURN

Working with Contacts

The Contacts item (ContactItem in our Object Model) stores information about a specific Contact. For each item it is possible to store 3 addresses, 3 e-mail addresses, 19 fax/phone numbers and many other data. Even though, there might be cases when these fields are not sufficient. In this situation we can create other items defined by the user (UserProperties property). Fields defined by the user are stored in each Item individually, i.e., a given Contact Item can have a Field that the others doesn´t have.

So, to recover the information about our Contacts, we would use:

LOCAL	loApplication, loNameSpace, loContacts, lnCounter, loContact, lcMessage
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loContacts    = loNameSpace.GetDefaultFolder(10)
FOR lnCounter = 1 TO loContacts.Items.Count
    loContact = loContacts.Items(lnCounter)
    lcMessage = "Contact: "       + CHR(9) + STR(lnCounter)        + CHR(13) + CHR(10) +;
     "First Name: "   + CHR(9) + loContact.FirstName   + CHR(13) + CHR(10) +;
     "Last Name: "    + CHR(9) + loContact.LastName    + CHR(13) + CHR(10) +;
     "Company Name: " + CHR(9) + loContact.CompanyName
    MSGSVC(lcMessage)
ENDFOR
RELEASE ALL
RETURN

Including and Editing Contacts

To add a new contact we use the Add method and to save the changes made we use the Save method (pretty logical, right?). Then we would have:

LOCAL	loApplication, loNameSpace, loContacts, loNewContact
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loContacts    = loNameSpace.GetDefaultFolder(10)
loNewContact  = loContacts.Items.Add()
loNewContact.FirstName   = "Filippo"
loNewContact.LastName    = "Cavalcanti"
loNewContact.FullName    = "Filippo Cavalcanti"
loNewContact.CompanyName = "Global Connection"
loNewContact.Save()   
RELEASE ALL
RETURN

You can check it out, now my son is part of your Contact file. To remove him, use the Delete method.

Navigating, Sorting and Searching Data

The Outlook Object Model provides methods to make navigating within a Folder easier. In the first method, we counted the number of items within a specific Folder and then we went through (one by one) until the last one.

LOCAL	loApplication, loNameSpace, loContacts, loItens, lnContador, lcMessage
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loContacts    = loNameSpace.GetDefaultFolder(10)
loItens       = loContacts.Items
FOR lnCounter = 1 TO loItens.Count
    lcMessage = "First Name: "   + CHR(9) + loItens.Item(lnCounter).FirstName + CHR(13) + CHR(10) +;
     "Last Name: "    + CHR(9) + loItens.Item(lnCounter).LastName + CHR(13) + CHR(10) +;
     "Company NAme: " + CHR(9) + loItens.Item(lnCounter).CompanyName
    MSGSVC(lcMessage)
ENDFOR
RELEASE ALL
RETURN

We can include the Sort method to classify the items by the field specified in the first parameter. The second parameter specifies if ascending (.F.) or descending (.T) order. There we have it:

LOCAL	loApplication, loNameSpace, loContacts, loItens, lnContador, lcMessage
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loContacts    = loNameSpace.GetDefaultFolder(10)
loItens       = loContacts.Items
loItens.Sort("[CompanyName]", .T.)
FOR lnCounter = 1 TO loItens.Count
    lcMessage = "First Name: "     + CHR(9) + loItens.Item(lnCounter).FirstName + CHR(13) + CHR(10) +;
     "Last Name: "     + CHR(9) + loItens.Item(lnCounter).LastName + CHR(13) + CHR(10) +;
     " Company Name: " + CHR(9) + loItens.Item(lnCounter).CompanyName
    MSGSVC(lcMessage)
ENDFOR
RELEASE ALL
RETURN

In the second method we used GetFirst and GetLast methods, which return references to the first and last items of a Folder. After in place, we use the GetNext and GetPrevious methods to move backward and forward within a Folder.

To locate a specific Item, we use the Find method. The selection criteria is passed through just one parameter. Then to list all contacts where the first names (FirstName) start with the letter "J" would be like this:

LOCAL	loApplication, loNameSpace, loContacts, loItens, loItem, lcMessage
loApplication = CREATEOBJECT("Outlook.Application")
loNameSpace   = loApplication.GetNameSpace("MAPI")
loContacts    = loNameSpace.GetDefaultFolder(10)
loItens       = loContacts.Items
loItens.Sort("[FirstName]", .F.)
loItem        = loItens.Find("[FirstName]>='J'")
DO WHILE .T.
    IF LEFT(loItem.FirstName, 1) <> "J"
        EXIT
    ENDIF
    lcMessage = "First Name: "   + CHR(9) + loItem.FirstName   + CHR(13) + CHR(10) +;
     "Last Name: "    + CHR(9) + loItem.LastName    + CHR(13) + CHR(10) +;
     "Company Name: " + CHR(9) + loItem.CompanyName
    MSGSVC(lcMessage)
    loItem    = loItens.FindNext()
ENDDO
RELEASE ALL
RETURN

Conclusion

This article is the first of a series on OLE Automation, and more interesting subjects are still to come. It is possible to save data entry programs and eliminate duplicated data using Outlook to store Contacts information. In the next article we will look into 2 other areas of Microsoft Outlook: Tasks and Calendar.

Copyright © 1993 - 2014 Level Extreme Inc., All Rights Reserved · Telephone: 506-783-9007 Email: info@universalthread.com · Privacy & Security · Copyright · Terms & Conditions