What's the best language to automate this Windows task involving Outlook and Excel?

Question

Hi,

I need to automate a particular task that involves:

• Fetching mails from my mailbox based on a set of criteria - from a particular user, has a particular pattern of subject line, etc.

• Parsing the mail body of each mail and extracting some content (the mails have an almost fixed pattern)

• Creating an Excel sheet with a particular template (the first two lines are constant, the third line is the Monday and Friday of this week, etc.) and paste the content extracted above into this sheet in successive lines.

• Creating an Outlook email message with the same content in a different format

• Attaching the above Excel sheet with this mail.

(Finally sending it, but I don't want to automate that part - I like to double check things before sending out mails.)

Now, when I thought of automating this task, Perl's Win32::OLE came to mind immediately since I'd learnt about it recently, and am very comfortable in Perl. But then, I thought Visual Basic for Automation might be more suited for the task, since it is a language created for such tasks.

My question is, is VBA better enough to offset the difference in my skillset between Perl and VB? I've played with VB6 when I was a kid years ago, but that's about the extent of my VB experience.

I'm open to any other language/platform suggestions as well, provided they allow some flexibility and aren't too difficult to learn. I know some Python as well.

I looked at this and also this, this, this and this, but they either are not relevant, or do not have any good answers.

Thanks in advance,
Sundar

Answer 1

I asked Best “official” scripting language for Windows programmers, and the answer was mostly PowerShell. Here's a link I found automating Outlook: using PowerShell for Outlook automation.

$outlook = new-object -com Outlook.Application

Look at the subjects of the first 5 emails you sent

$sentMail = $outlook.Session.GetDefaultFolder(5) # == olFolderSentMail 
$sentMail.Items | select -first 5 TaskSubject    

And Excel: PowerShell and Excel.

$a = new-object -comobject excel.application

How do I change the value of a specified cell?

$a.ActiveSheet.Range("B1").Value2 = "y"

Answer 2

How about using AutoIt? This has a syntax that resembles VB and can do about just anything plus the documentation is quite comprehensive. Furthermore, you can compile it to a standalone executable for automating the task on another machine that does not have AutoIt. And it has a nice IDE environment to test and debug the automation script.

Hope this helps, Best regards, Tom.

Answer 3

Disclaimer : I have never worked with Perl or Python.

If you are willing to "step up" from Macros and VBA to using the Visual Studio Office Tools (VSTO) which have been available in .NET for some time now, you have several powerful new options to handle Office interop :

1. Using the COM API's as exposed via PIA's (Primary Interop Assemblies) usable from C# and other .NET languages : lots of code examples available here on SO or on CodeProject for doing all the things you describe. Suggest you search on "C# Office Interop" "C# Outlook Interop" "C# Excel Interop" "C# Office Automation." Or search on other .NET languages from VB.NET to F#.

2. With the latest .NET tools you have UI-designers that let you create Add-Ins for Excel or Outlook, etc. I'm working on an Excel add-in in 2010 right now, and it is quite a pleasure to be able to drag and drop WinForms controls onto a WorkSheet and create event handlers, and edit properties, at design-time in the same way I would work in WinForms. Of course, 2010 beta Office has some "rough edges," as you might expect.

VSTO forum on Microsoft : VSTO Forum for Office 2003, 2007

Good "portal" for Office Automation via VSTO : Getting Started (Visual Studio Tools for Office)

VSTO forum on Microsoft for Visual Studio 2010 beta 2 : 2010 beta Be sure and open the drop-down panel under "Announcements" titled "Office Development with Visual Studio 2010 Beta 2 Resources"

Microsoft Office 2010 beta forum : Office 2010

In Visual Studio 2010 beta 2, using the Office 2010 beta, you have no less than six project types related to interop with Excel alone :

Excel 2007 Add-In, Excel 2010 Add-In, Excel 2007 Template, Excel 2010 Template, Excel 2007 Workbook, Excel 2010 Workbook : For Outlook : Outlook 2007 Add-In, Outlook 2010 Add-In

This SO thread Beginning VSTO ? may have some value to you, and I suggest you do searches here on SO on "VSTO Excel" and "VSTO Outlook"