Controlling Email on Windows with Perl

punkish has asked for the wisdom of the Perl Monks concerning the following question:

Dunno how to title this better. This is a rather strange situation for which I first considered Perl, ran into a few roadblocks, a colleague started messing with VBA, ran into even more roadblocks, and I am now reconsidering Perl.

If the incoming email has a particular subject line (I can use email filter), certain information has to be extracted from the email message (I can use Perl if I can access the message contents), a record with some of the info from the message has to be inserted into a SQL-Server table (use DBI with DBD-ODBC), the message is to be possibly reformatted (of trivial difficulty), and forwarded to one or more pre-determined addresses. All this has to be accomplished unattended and with no human intervention.

The email server is Exchange, and the most likely email client is Outlook.

Initially I "assumed" we were going to use Outlook, tried to run a Perl script from within Outlook, but was not able to... we tried doing it all in VBA using ADO, but ran into a similar problem. Seems like after being burned by too many viruses accessing Outlook innards, MS has made even legitimate access to Outlook very difficult.

I am seeking any advice from fellow monks on a possible approach to solving this problem. If the technique is successful, I think I can even justify ditching Outlook completely and use some other email client. But I am not sure if I can use Perl to directly access an email received by Exchange server.

I found a few discouraging Google hits on MAPI and Perl, and a number of hits on accessing Excel spreadsheets and whatnot. Nothing of the sort I am describing above.

Thanks in advance for any guidance.

Comment on Controlling Email on Windows with Perl

Replies are listed 'Best First'.
Re: Controlling Email on Windows with Perl by saintbrie (Scribe) on Apr 16, 2004 at 03:42 UTC
Forewarned: I'm a linux guy without a windows box to look at. Seems like the problem is accessing from within outlook. If you can filter on the subject line, even Outlook can do that. Use its filters to forward to another email address that can be POPped by perl (Net::POP3). From there, you shouldn't have any trouble. Can you use the rules wizard to create a couple of rules: one that saves the messages to a folder, and another that triggers a program that then processes it. I've heard that you can use Win32::OLE to send messages via a MAPI object, but I don't know if it can be used from "within" outlook.	[reply]
Re: Re: Controlling Email on Windows with Perl by punkish (Priest) on Apr 16, 2004 at 04:45 UTC
Aaaah, the beauty. You, "linux guy," I direct my warmest regards to you. Your suggestion of using Net::POP3 works like a charm against Exchange. To hell with Outlook and such nonsense. Perl, once again, saves the day. Much gratitude. However, this is a "pull" from my perl script... I would have to set up a cron task and run the script periodically. A better solution would be if I can have the email server "kick" the script into action when a specific email is received? For that I will need access to the script from within the email server! Any ideas for that? If I can't solve this then I will set up a periodic task, however, the latter would be more elegant.	[reply]
Re: Re: Re: Controlling Email on Windows with Perl by saintbrie (Scribe) on Apr 16, 2004 at 11:10 UTC
Yes that would be more elegant. If I were setting up such a system on a linux server, something like bugzilla's bugmail script (for instance), I'd use procmail to trigger the script when I received a message from the mail server. Does Exchange have such a concept?	[reply]
Re: Controlling Email on Windows with Perl by Errto (Vicar) on Apr 16, 2004 at 04:45 UTC
Caveat: I've done some Perl and I've done some Windows but never Perl on Windows, so this is a bit hypothetical. Under Win32, Perl can access any COM object through the Win32::OLE class. IIRC the correct class for Outlook is "Outlook.Application" However, you'll run into the problem you mentioned, which is that Outlook will not let you send email programmatically, regardless of language, without popping up a rather unpleasant confirmation message. For receiving email, I don't recommend MAPI as it's ugly and complicated. You can use the CDO library through Win32::OLE, or, assuming your servers are Exchange 2000 or higher, you can access them with IMAP. Get the Mail::IMAPClient module from CPAN for this. For sending email, I believe Net::SMTP is considered the canonical choice.	[reply]
Re: Controlling Email on Windows with Perl by Koosemose (Pilgrim) on Apr 16, 2004 at 04:53 UTC
Probably one of the (relatively) easier methods to do this would be to set up a Mail Proxy, and in the spirit of not reinventing wheels, I'll point you to the same node I based my own Email Proxy/Filter on, McD's A SpamAssassin-Enabled POP3 Proxy, Just look for the sub scan_mail, and replace it with one of your own liking. As an example of another plugin filter for McD's proxy, allow me to direct you to my own code to Convert Multipart Email to plain text ((shameless plug)). Oh yes, and as further food for thought, a related column (for SysAdmin magazine) by merlyn Just Another Perl Alchemist	[reply]
Re: Controlling Email on Windows with Perl by Zero_Flop (Pilgrim) on Apr 16, 2004 at 06:15 UTC
You can do what saintbrie suggests. Just compleatly get rid of the email client and have your script dl the email and store it in mbox format. But one more reference for you. You may want to look at PopFile. Popfile acts as a proxy between your email client and your server. It can classify your emails for you and you can probably add some plug-ins to do the manipulation you are looking for. They are currently developing plug-ins that adds newsgroup classifications; it is designed to be modular. Worst case it will give you some ideas. Ever since my email was harvested I have used Popfile very successfully to classify my email. I promote it whenever I can. It turned a bad situation into something very tolerable. PS. Yes you could do something with Win32::OLE but why... Don't get me wrong if you look at my other posts I am a proponent of it. The problem is you would be locked into Outlook. By using a proxy or a perl script that does it all you would be able to use any client you wanted without recoding.	[reply]
Re: Re: Controlling Email on Windows with Perl by chanio (Priest) on Apr 16, 2004 at 15:52 UTC
Benefits of using PopFile: OpenSource, multiplatform, well object oriented, easy to add new modules (just put them in the modules folder), well documented, easy win32 interfase, easy to install with a .exe. Perhaps you could even present your filter as an add-on to the popfile comunity and help others to improve this great spam filter with new add-ons. {\('v')/} .`(___)' ____A_A_S_________________	[reply]


go ahead... be a heretic
	PerlMonks