Reading Outlook Mailbox

[Chris.Tanti]

Hi,

Have created a little application that reads emailed orders from Amazon and converts them into a database record (so they can be reported etc). I do it by copying and pasting the email into a working window and processing it. This has to be done one at a time.

I have managed to read Eudora MBX files directly with Alpha5 (there basically text files with a seperator for each message), but was wondering if any body had a experience with doing the same with outlook.

Would like to be able to read the messages in a particular mailbox directly so they can be stripped out rather than having to cut and paste each message individually.

Outlook stores all the mailboxes in a .pst file, Eudora, rather helpfully stores each mailbox in a seperate .mbx file (much easier to interpret).

[Marcel Kollenaar]

[...] Outlook ... Would like to be able to read the messages in a particular mailbox directly so they can be stripped out rather than having to cut and paste each message individually.

Chris, did you found a solution?

[jkletrovets]

I have not used this yet. I will be soon though. Thought I would pass it along in case you had not seen it.

http://www.geniusatwork.nl/


Regards,

Jeff

[Marcel Kollenaar]

I have not used this yet. I will be soon though. Thought I would pass it along in case you had not seen it.

http://www.geniusatwork.nl/

Regards,

Jeff

Thanks Jeff,

I've seen your contribution already in another thread. I'm more interested in a direct way from A5 with Xbasic. There must be a way to do that.

[jkletrovets]

Hi Marcel

I just ran across this:

"A more flexible and recommended method for connecting Outlook to a database is ActiveX Data Objects (ADO), the programming interface for OLE DB database technology, which allows you to connect to virtually any kind of data source. ADO 2.0 is included with Office 2000 and downloadable for earlier versions of Outlook. Later versions of Office come with later versions of ADO.
If you want to synchronize data between Outlook items and database records, you might link the Outlook item and the database record through a custom ID field on the Outlook item that has a counterpart in the database, both holding the same values. See Developer information about the calendar changes in Outlook 2003 Service Pack 2 and later versions about changes related to the EntryID in appointments that may make it a poor choice for this kind of linking.

My book, Microsoft Outlook Programming - Jumpstart for Administrators, Developers, and Power Users, includes practical examples for connecting to Access and SQL Server databases with both DSN and DSN-less connections, populating a combo box from a database table, and using a database to generate a sequential unique ID. You can download the sample code from the get Sue's code link below."

It came from here:

http://www.outlookcode.com/d/database.htm

Sounds like it is indeed possible. I will be digging into this fairly soon myself so I hope there is an elegant solution out there.

Regards,

Jeff

[Marcel Kollenaar]

How about this:

'Date Created: 14-Apr-2006 05:57:19 PM
'Last Updated: 14-Apr-2006 05:57:19 PM
'Created By  : Marcel Kollenaar
'Updated By  : Marcel Kollenaar

dim myOlApp as P
dim myNameSpace as P
dim myFolder as P
constant olFolderInbox = 6
myOlApp = ole.GetObject("","Outlook.Application")
myNameSpace = myOlApp.GetNameSpace("MAPI")
myFolder= myNameSpace.GetDefaultFolder(olFolderInbox)
myFolder.Display()
end

This starts Outlook and shows your FolderIn. This is the first step [...]

The next step is to cycle through the emails and get the text. :)

[jkletrovets]

Well now,

How cool is that!

I ran across this too.

http://support.microsoft.com/?kbid=275262

Thanks Marcel - that will definitely get me where I want to go quicker.

Regards,

Jeff

[Marcel Kollenaar]

Well, accessing the Outlook Calendar with the Jet Engine is also cool. Can't wait to see your Xbasic steps on this.

The problem with Outlook is that it has no Macro Recorder as Word and Excel has. In outlook you have to conquer every line of code. If you once know how the objects want to be handled from within A5 it's easy.

BTW, thanks for the links, nice information and a lot of it can be rewritten for Xbasic. :D

[Marcel Kollenaar]

Ok, now with a loop!

'Last Updated: 14-Apr-2006 09:05:43 PM
'Created By  : Marcel Kollenaar
'Updated By  : Marcel Kollenaar

'// Purpose: Xbasic example how to save the message body text from the FolderInbox
'//          to C:\MailItems\Message_n.txt (Where n = 1 to items count)

dim myOlApp as P
dim myNameSpace as P
dim myFolder as P
dim ItemCount as N
dim i as N
dim Path as C

constant olFolderInbox = 6
constant olTXT = 0

myOlApp = ole.GetObject("","Outlook.Application")
myNameSpace = myOlApp.GetNameSpace("MAPI")
myFolder= myNameSpace.GetDefaultFolder(olFolderInbox)

ItemCount = myFolder.items.count

'// Create folder C:\MailItems\

Path  = "C:\MailItems\\"
file.dir_create_recurse(Path)

'// Loop through the messages in the FolderInbox
'// and save the body text to a text file.

for i = 1 to ItemCount
	myFolder.items(i).SaveAs(Path + "Message_" + alltrim(str(i)) + ".txt", olTXT)
next i

delete myOlApp
delete myNameSpace
delete myFolder

end 'of script

[jkletrovets]

That is great! I can't wait to play with it more.

I definitely will be digging deeper into this in about a week (have to go out of town next week). I am fairly new to Alpha but I am continually amazed at how great it is and the power available from the basic user interface tools, the genies and from xbasic. Since I am a newbie, I have not been able to help a lot yet on this board. I look forward to giving back as my knowledge increases. I certainly have gained a bunch of knoweledge by just reading this board (and Alpha help files/tutorials) since I purchased Alpha. I don't get the folks that criticize it. It is one nice piece of software! And the help here is awesome.

You have definitely helped me Marcel. I might have been able to get there -but you sure paved a pretty smooth on ramp.

I'll be sure to post some examples of my trials when I get back. Thanks again.

Regards,

Jeff

[jkletrovets]

ADO Connection

Greetings Marcel

Below is an ADO connection string that works with Outlook for directly reading the data. I am interested in the contact folder primarily so that is what I worked with here. If you have a "standard" installation - You should just need to change the path to your Outlook pst folder in the connection string and hopefully everything will work.

Please note this is just my first step too. The next for me is to read the data to a temp table or array that can be used for a look up. I "borrowed" almost all of the code from the A5 help file.

I am open to any and all suggestions about enhancing the data retrieval from Outlook. For my needs, the ability to talk to Outlook is going to be fantastic.

Best regards,

Jeff

'Date Created: 28-Apr-2006 01:02:58 AM
'Last Updated: 28-Apr-2006 01:05:03 AM
'Created By  : Jeff Kletrovets
'Updated By  : Jeff Kletrovets
' Code to connect to Outlook via ADO and return last names


dim connString as C
dim conn as ole::adodb.connection
connString="Provider=Microsoft.JET.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Outlook Address Book;PROFILE=Outlook;TABLETYPE=1;DATABASE=C:\Documents and Settings\Jeff Kletrovets\Local Settings\Application Data\Microsoft\Outlook;;Persist Security Info=False"
conn.open(connString)

' Create record set
rs = conn.Execute("select * from contacts order by last")

' Read the last names into a list and display
dim LastnameList as c = ""
while .not. rs.Eof()
    LastnameList = LastnameList + rs.fields.item(1).value + crlf()
    rs.MoveNext()
end while
ui_msg_box("Lastnames",LastnameList)
 
' Close the record set
rs.close()
' Close the ADO connection
conn.close()

[jkletrovets]

Hello again...

Well, it looks as if you might not need to enter the directory info for your pst. It seems you can create a temp folder in something like c:\temp and just use that.

Regards,

Jeff

[jkletrovets]

Here's one more example that reads the inbox. Notice the differences in the connection string. The MAPILEVEL is different and the TABLETYPE also.


There is some more info on these settings here:

http://msdn.microsoft.com/library/de...olexcoutlk.asp


*****************************

'Date Created: 28-Apr-2006 02:55:43 AM
'Last Updated: 28-Apr-2006 02:55:43 AM
'Created By  : Jeff Kletrovets
'Updated By  : Jeff Kletrovets
dim connString as C
dim conn as ole::adodb.connection
connString="Provider=Microsoft.JET.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Personal Folders|;PROFILE=Outlook;TABLETYPE=0;DATABASE=C:\temp;;Persist Security Info=False"

conn.open(connString)

rs=conn.Execute("select * from Inbox")

' display who message is from and subject line
dim MessageList as c = ""
while .not. rs.Eof()
    MessageList = MessageList + rs.fields.item(4).value +"   "+rs.fields.item(3).value + crlf()
    rs.MoveNext()
end while

ui_msg_box("Email Subject",MessageList)

rs.close()

conn.close()

[Marcel Kollenaar]

Hi Jeff,

This is very nice code. I've seen this code at Microsoft but I didn't related it to Alpha Five in the way you did. I even saw examples how to use it from VBA macros in Word.

You are the man. I'll keep this precious code in a safe place.

[jkletrovets]

Thanks Marcel.

I am excited to use it as well!

Regards,

Jeff