vba 自动执行此涉及 Outlook 和 Excel 的 Windows 任务的最佳语言是什么?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2169879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best language to automate this Windows task involving Outlook and Excel?
提问by sundar - Reinstate Monica
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.
根据一组标准从我的邮箱中获取邮件——来自特定用户、具有特定主题行模式等。
解析每封邮件的邮件正文并提取一些内容(邮件具有几乎固定的模式)
使用特定模板创建一个 Excel 工作表(前两行是不变的,第三行是本周的星期一和星期五等)并将上面提取的内容以连续的行粘贴到此工作表中。
创建内容相同但格式不同的 Outlook 电子邮件
- 随此邮件附上上述 Excel 表格。
(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.
现在,当我想到自动化这项任务时,我立刻想到了 Perl 的 Win32::OLE,因为我最近了解了它,并且对 Perl 非常熟悉。但是,我认为 Visual Basic for Automation 可能更适合该任务,因为它是为此类任务创建的语言。
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.
我的问题是,VBA 是否足以抵消我在 Perl 和 VB 之间的技能差异?几年前我还是个孩子的时候就玩过 VB6,但这与我的 VB 经验有关。
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.
我也愿意接受任何其他语言/平台建议,前提是它们具有一定的灵活性并且不太难学。我也知道一些 Python。
I looked at thisand also this, this, thisand this, but they either are not relevant, or do not have any good answers.
采纳答案by Eugene Yokota
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.
我问过 Windows 程序员的最佳“官方”脚本语言,答案大多是PowerShell。这是我找到的自动化 Outlook 的链接:using PowerShell for Outlook Automation。
$outlook = new-object -com Outlook.Application
Look at the subjects of the first 5 emails you sent
查看您发送的前 5 封电子邮件的主题
$sentMail = $outlook.Session.GetDefaultFolder(5) # == olFolderSentMail
$sentMail.Items | select -first 5 TaskSubject
And Excel: PowerShell and Excel.
和 Excel:PowerShell 和 Excel。
$a = new-object -comobject excel.application
How do I change the value of a specified cell?
如何更改指定单元格的值?
$a.ActiveSheet.Range("B1").Value2 = "y"
回答by BillW
Disclaimer : I have never worked with Perl or Python.
免责声明:我从未使用过 Perl 或 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 :
如果您愿意从宏和 VBA“升级”到使用 Visual Studio Office 工具 (VSTO),它已经在 .NET 中提供了一段时间,那么您有几个强大的新选项来处理 Office 互操作:
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#.
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.
使用可通过 C# 和其他 .NET 语言使用的 PIA(主要互操作程序集)公开的 COM API:SO 或 CodeProject 上提供了大量代码示例,用于完成您描述的所有事情。建议您搜索“C# Office Interop”“C# Outlook Interop”“C# Excel Interop”“C# Office Automation”。或者搜索从 VB.NET 到 F# 的其他 .NET 语言。
使用最新的 .NET 工具,您拥有 UI 设计器,可让您为 Excel 或 Outlook 等创建加载项。我现在正在 2010 年开发 Excel 加载项,很高兴能够将 WinForms 控件拖放到 WorkSheet 上,并在设计时以与在 WinForms 中工作的方式相同的方式创建事件处理程序和编辑属性。当然,正如您所料,2010 测试版Office 有一些“粗糙的边缘”。
VSTO forum on Microsoft : VSTO Forum for Office 2003, 2007
Microsoft 上的VSTO 论坛:Office 2003、2007 的 VSTO 论坛
Good "portal" for Office Automation via VSTO : Getting Started (Visual Studio Tools for Office)
通过 VSTO 实现办公自动化的良好“门户”:入门(Visual Studio Tools for Office)
VSTO forum on Microsoft for Visual Studio 2010 beta 2 : 2010 betaBe sure and open the drop-down panel under "Announcements" titled "Office Development with Visual Studio 2010 Beta 2 Resources"
Microsoft for Visual Studio 2010 beta 2 上的 VSTO 论坛:2010 beta确保打开“Announcements”下标题为“Office Development with Visual Studio 2010 Beta 2 Resources”的下拉面板
Microsoft Office 2010 beta forum : Office 2010
Microsoft Office 2010 测试版论坛: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 :
在 Visual Studio 2010 测试版 2 中,使用 Office 2010 测试版,您有不少于六种与 Excel 互操作相关的项目类型:
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
Excel 2007 加载项、Excel 2010 加载项、Excel 2007 模板、Excel 2010 模板、Excel 2007 工作簿、Excel 2010 工作簿:对于 Outlook:Outlook 2007 加载项、Outlook 2010 加载项
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"
这个 SO 线程开始 VSTO ?可能对您有一些价值,我建议您在“VSTO Excel”和“VSTO Outlook”上的 SO 上进行搜索
回答by t0mm13b
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.
使用AutoIt怎么样?它的语法类似于 VB,可以做任何事情,而且文档非常全面。此外,您可以将其编译为独立的可执行文件,以便在另一台没有 AutoIt 的机器上自动执行任务。它有一个很好的 IDE 环境来测试和调试自动化脚本。
Hope this helps, Best regards, Tom.
希望这会有所帮助,最好的问候,汤姆。