Geek Answers Handbook

What is the best language to automate this Windows task using Outlook and Excel?

I need to automate a specific task, which includes:

  • Receiving letters from my mailbox based on a set of criteria - from a specific user, has a specific subject line template, etc.

  • Parse the mail body of each mail and extract some content (letters have an almost fixed pattern)

  • Create an Excel worksheet with a specific template (the first two rows are constant, the third row is Monday and Friday this week, etc.) and paste the contents extracted above into this worksheet in consecutive lines.

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

  • Attaching the above excel sheet with this post.

(Finally, sending it, but I don’t want to automate this part - I like to double-check things before sending emails.)

Now that I thought about automating this task, Perl Win32 :: OLE came to mind right after I learned about it recently, and I find it very convenient in Perl. But then I thought that Visual Basic for automation might be more suitable for the task, since it is a language created for such tasks.

My question is, is VBA better to compensate for the difference in my skill set between Perl and VB? I played with VB6 when I was a kid a few years ago, but this concerns the degree of my experience with VB.

I am open to any other language / platform offers if they allow flexibility and are not too difficult to learn. I also know Python.

I looked over this , as well as this , this , and, but they either are irrelevant or the answers are of no use.

+4
source share
3 answers

I asked for the best "official" scripting language for Windows programmers , and the answer was mostly PowerShell . Here's the link I found with Outlook Automation: using PowerShell to automate Outlook .

$outlook = new-object -com Outlook.Application

Look at the topics of the first 5 letters 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 to change the value of a specified cell?

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

Disclaimer: I have never worked with Perl or Python.

If you are ready to “activate” macros and VBA with Visual Studio Office Tools (VSTO), which have been available in .NET for some time, you have several powerful new features for interacting with Interop:

  • Using the COM API, as shown through the PIA (Primary Interop Assemblies) used in C # and other .NET languages: the many code examples available here in SO or CodeProject to do all the things you describe. We suggest that you search for "C # Office Interop" "C # Outlook Interop" "C # Excel Interop" "C # Office Automation". Or search in other .NET languages ​​from VB.NET to F #.

  • With the latest .NET tools, you have custom 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's pretty nice to be able to drag and drop WinForms controls onto WorkSheet and create event handlers and edit properties during development in the same way as in WinForms. Of course, the 2010 beta has some rough edges, as you might expect.

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

A good "portal" for automating Office through VSTO: Getting Started (Visual Studio Tools for Office)

VSTO Forum at Microsoft Beta for Visual Studio 2010 2: 2010 Beta Be sure to open the drop-down panel under the heading "Ads" "Office Development with Visual Studio 2010 Beta Resources"

Microsoft Office 2010 Beta: Office 2010

In the beta version of Visual Studio 2010 2, using the beta version of Office 2010, you have at least six types of projects related to interacting with Excel:

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

This SO Stream Starts VSTO? may have some meaning for you, and I suggest you do a search here on SO on "VSTO Excel" and "VSTO Outlook"

+6
source

How about using AutoIt ? It has a syntax that is reminiscent of VB and can only do anything plus documentation quite complete. In addition, you can compile it into a standalone executable file to automate the task on another computer that does not have AutoIt. And it has a good IDE for testing and debugging automation scripts.

Hope this helps, Regards, Tom.

0
source

More articles:


All Articles