Outlook Automation: Linking to Contacts and Calendar 4

[Docpro777]

MS Outlook is the future of PIM and sheduling I strongly speculate. To integrate it with my FoxPro apps is a dream I've been trying hard to realize.

Thus, Importing and Exporting Contacts and Schedules is a challenging on many levels. Please answer a question or 2 below. Later, I will refine the questions again after experimenting based on your suggestions.

1) How might one obtain better examples of importing/exporting Calendar (appointments) or Contacts using COM automation (or other import/export) tools?

2) I'm getting ODBC errors [ODBC Driver Manager] with import and export in Outlook. Outlook states: "the MS FoxPro translator can't build a field map ... invalid string or buffer length."

3) I have visual basic frustrations in using Outlook's Macros, too.

Any general or specific suggestions are welcome.

Philip M. Traynor, DPM

http://www.doctorprofessional.com

 

[Mike Gagnon]

DocPro777

1) How might one obtain better examples of importing/exporting Calendar (appointments) or Contacts using COM automation (or other import/export) tools?

To access your contacts or your calendar appointment in Outlook, the first few lines of code are the same.
Display Outlook's calendar

LOCAL oOutlook,oNameSpace,oDefaultFolder
oOutlook = CREATEOBJECT("outlook.application") 
oNameSpace = oOutlook.getnamespace("MAPI")
oDefaultFolder=oNameSpace.GetDefaultFolder(9) &&Calendar
oDefaultFolder.display()

Display Outlook's contact folder[/code]
LOCAL oOutlook,oNameSpace,oDefaultFolder
oOutlook = CREATEOBJECT("outlook.application&quot
oNameSpace = oOutlook.getnamespace("MAPI&quot
oDefaultFolder=oNameSpace.GetDefaultFolder(10) &&Contact
oDefaultFolder.display()
[/code]
Retrieve Outlook's contact, name and email address

CREATE CURSOR myCursor (Name c(40),email c(50))
LOCAL oOutlook,oNameSpace,oDefaultFolder
oOutlook = CREATEOBJECT("outlook.application") 
oNameSpace = oOutlook.getnamespace("MAPI")
oDefaultFolder=oNameSpace.GetDefaultFolder(10) 
oItems = oDefaultFolder.items
FOR EACH oItem IN oItems
 INSERT INTO myCursor (name,email) VALUES (oItem.fullname,oItem.email1address)
ENDFOR
SELECT myCursor 
BROWSE

Retrieve appointements in Outlook's calendar

CREATE CURSOR myCursor (start T,end T,body c(250))
LOCAL oOutlook,oNameSpace,oDefaultFolder
oOutlook = CREATEOBJECT("outlook.application") 
oNameSpace = oOutlook.getnamespace("MAPI")
oDefaultFolder=oNameSpace.GetDefaultFolder(9) 
oItems = oDefaultFolder.items
FOR EACH oItem IN oItems
 INSERT INTO myCursor (start,end,body) VALUES (oItem.start,oItem.end,oItem.body)
ENDFOR
SELECT myCursor 
BROWSE

Off course there are most likely other pieces of information you may want to retrieve for the above, but you can see how it is done.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[craigsboyd]

Mike,

Good stuff and star quality. If you haven't put this stuff in any of your FAQs regarding Outlook you should.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[Mike Gagnon]

SlightHaze

Thanks for the star. I started an faq (faq184-3894), which I'll keep updating as the different functions are requested.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[monikai]

Star from me too (from summer sky)

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[Nifrabar]

Hello Mike,
Well that' is great stuff.
Could you also inform how to retrieve other fields from the contact-list ?
Or.. where to find a source for that ?
Is there a two-way traffic possible, so uploading from my vfp-app to outlook is possible as well ?
-Bart

 

[Nifrabar]

Mike,
Almost forgot..
Of course my star too.
-Bart

 

[Docpro777]

Mike,

Thanks for these awesome importing tools.

Exporting into Outlook via VFP (as per Nifrabar and (Question #2)) is the next vital link. Any ideas at all?

Imagine being able to synchronize vital VFP Calendar and Contact fields into your Outlook (and handheld pc, even)...

With VFP we can directly code/export into the cladistically similar Excel, but not so directly with Outlook. Macros in Outlook 2002 require V.Basic scripts to be manually written in.

If an Outlook macro is coded by and then called by VFP, this may be part of the solution.

In sum: How might we code to EXPORT into Outlook, via COM automation and/or other work-arounds?


Philip M. Traynor, DPM

http://www.doctorprofessional.com

 

[Mike Gagnon]

Nifrabar

Or.. where to find a source for that?

Go to this link, which is one of the properties of the contacts and in the index (on the left of your browser you will find all the others ones listed alphabetically).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol10/html/olproEmail2Address.asp

Is there a two-way traffic possible, so uploading from my vfp-app to outlook is possible as well ?

yes, if you look at faq184-1772, it show you how to create a task in Outlook, the same principle would apply for Contacts and Calendar appointments. Here is the code in the faq:

myOlApp = CreateObject("Outlook.Application")
myItem = myOlApp.CreateItemFromTemplate("C:\Task.oft")
WITH myItem
    .DueDate = "9/20/2002"
    .Body ="Template Test"
    .SubJect = "Hello Template test"
    .Status = 1  && In Progress
    .Assign
    .Recipients.Add("me@somewhere.com")
    .Send  && This will send the task to the recipient(s)
endwith

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

Docpro777

With VFP we can directly code/export into the cladistically similar Excel, but not so directly with Outlook. Macros in Outlook 2002 require V.Basic scripts to be manually written in.

All "folders" in Outlook have the createitem() function, so yes you can write to Outlook and you can retrieve from Outlook and email from it, if fact you can strickly use Outlook as a vehicle to hold you information, an never really need the interface.





Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Docpro777]

Very well. I'll experiment with these excellent importing and exporting functions.

While I have your grace (or anyone elses); life might not be complete without a wrapper class (methinks) to bind events in Outlook, as we can do with Word.

I looked in the object browser for object skeletons and FOUND NOTHING for Outlook but found this code under HELP: Visual FoxPro and Advanced COM:

CLEAR
CLEAR all
PUBLIC ox as Excel.Application, ;
ow as word.application, ;
oOutlook as Outlook.Application

oOutlookEvents= NEWOBJECT('OutlookEvents')

oOutlook = NEWOBJECT("Outlook.Application&quot
oOutlookEvents.oo = oOutlook
? "Outlook",EVENTHANDLER( oOutlook, oOutlookEvents)

oWordEvents = NEWOBJECT("WordEvents&quot
ow = NEWOBJECT("word.application&quot
oWordEvents.ow = ow
?"Word",EVENTHANDLER(ow,oWordEvents)
ow.visible = .t.
ow.Activate
ow.Documents.Add

oExcelEvents = NEWOBJECT("ExcelEvents&quot
oex = NEWOBJECT("excel.application&quot
oex.Workbooks.Add
?"Excel",EVENTHANDLER(oex, oExcelEvents)
oex.visible = .t.

_screen.WindowState= 1

DEFINE CLASS OutlookEvents AS SESSION OLEPUBLIC
IMPLEMENTS ApplicationEvents IN Outlook.Application
oo = .null.
PROCEDURE ApplicationEvents_ItemSend(ITEM AS VARIANT, ;
CANCEL AS LOGICAL) AS VOID
?PROGRAM()
m.item.Body=STRTRAN(m.item.Body,"good","bad&quot + ;
CHR(13)+CHR(10)+TRANSFORM(DATETIME())+" Fox was here!"
* if Recipients fails, it could be outlook security
* m.item.Recipients.Add("anyone@anywhere.com&quot
PROCEDURE ApplicationEvents_NewMail() AS VOID
?PROGRAM()
PROCEDURE ApplicationEvents_Reminder(ITEM AS VARIANT) AS VOID
?PROGRAM()
PROCEDURE ApplicationEvents_OptionsPagesAdd(PAGES AS VARIANT) AS VOID
?PROGRAM()
PROCEDURE ApplicationEvents_Startup() AS VOID
?PROGRAM()
PROCEDURE ApplicationEvents_Quit() AS VOID
?PROGRAM()
PROCEDURE destroy
?PROGRAM()
IF !ISNULL(this.oo)
?EVENTHANDLER(this.oo,this,.t.)
ENDIF
ENDDEFINE

In addition, Marcia Akins has some excellent info, too, that I need to study (

http://tightlinecomputers.com).

Any further input is appreciated (by anyone).

Philip M. Traynor, DPM

http://www.doctorprofessional.com

 

[ramani]

Hi

Have a look into this also for whatever is worth in it.

A sample form for outlook email handling
faq184-3808



ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Nifrabar]

Mike,

"myItem = myOlApp.CreateItemFromTemplate("C:\Task.oft&quot"

Would you please explain about ("C:\Task.oft&quot

Do I have to create 'C:\Task.Oft'?

Once I run your program example it ends up with error:
OLE IDispatch exception code 4096 from Microsoft Outlook,
Cannot open file ("C:\Task.oft&quotetc.
Any suggestion ?

Regarding the link you gave to MSDN:
I found e.g. property "Companies Property". Do I understand that this holds the company name from Contacts ?
So I have to figure out which properties belong to the fields in my contacts. (Have to translate these from Dutch (=my XP-version) into English)

-Bart

 

[Docpro777]

Thanks Ramani,

Very nice!

It also portrays Calendar and Contacts (Address Book), too; and with Outlook 2002 ... much of it safely contained within the VFP screen even.

Admiring your PIM/Scheduler component I changed the INBOX folder of the olecontrol1.Init (also the default folder) to CALENDAR:
Procedure olecontrol1.Init
This.Folder = "Calendar" &&vs "InBox"
Endproc

Now, to juxtapose a form like yours, then import/export all dynamic data to tables, and bind events.

Thus, Contacts and Address Book would blow any PIM components of Tastrade into oblivion.



Philip M. Traynor, DPM

http://www.doctorprofessional.com

 

[Mike Gagnon]

Nifrabar

Would you please explain about ("C:\Task.oft&quot

Do I have to create 'C:\Task.Oft'?

Once I run your program example it ends up with error:
OLE IDispatch exception code 4096 from Microsoft Outlook,
Cannot open file ("C:\Task.oft&quotetc.
Any suggestion ?

No, that faq assumes you are using a Task tempplate, you you don't want to use a template use the suggestions in faq184-1771.

I found e.g. property "Companies Property". Do I understand that this holds the company name from Contacts ?
So I have to figure out which properties belong to the fields in my contacts. (Have to translate these from Dutch (=my XP-version) into English)

Yes, you would have to figure out the names of the fields you need to see. Do some testing with "? "
For example in the code I used above, just change the line :

LOCAL oOutlook,oNameSpace,oDefaultFolder
oOutlook = CREATEOBJECT("outlook.application") 
oNameSpace = oOutlook.getnamespace("MAPI")
oDefaultFolder=oNameSpace.GetDefaultFolder(10) 
oItems = oDefaultFolder.items
FOR EACH oItem IN oItems
 ?oItem.fullname
 ?oItem.email1address)
 ?oitem.Account
 ?oitem.AssistantName 
 ?oitem.Attachments.Count 
 ?oItem.Business2TelephoneNumber 
 ?oItem.BusinessAddress 
 ?oItem.BusinessAddressCity 
 ?oItem.BusinessAddressCountry
 ?oItem.BusinessAddressPostalCode 
 ?oItem.BusinessAddressStreet 
 ?oItem.BusinessFaxNumber 
 etc....
ENDFOR

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Nifrabar]

Mike,
Retrieving of the data from the contacts of Outlook can be done now, but adding or altering an item is still not clear to me.
Maybe caused by the '("C:\Task.oft&quot' you are talking about.
Do I understand right that xx.oft measns office-template ?
On my system I could only find a 'mail.oft'.
Is there a link which might help to understand about these ?
For now this seems to block me to make progress on this subject.
Any way thanks again so far for giving us a view inside the 'outlook black-box'.
-Bart

 

[Mike Gagnon]

Nifrabar

As stated above, if you do not want to use a Task Template use the code in FAQ184-1771 instead, it does not require the use of a template. Otherwise go to outlook, create a task and save it as a template and use it.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Nifrabar]

Mike,
Tnx,
I am going to take some more time to study your samples now.
As I am just a few days away from a 3-4 week vacation time I printed out the samples and will have plenty of time to study them.
-Bart