从Outlook到python都可以使您的工作减少一半

Abstract: This article covers how to schedule your regular python scripts. Assuming that you

摘要： 本文介绍如何安排常规python脚本。 假设你

1. Get updated excel reports every day/week/month into a shared Network or email

   每天/每周/每月将更新的Excel报告获取到共享的网络或电子邮件中

2. Have a python script that is up and running to processes your reports and deliver insights in the form of images, worksheets, or other forms of data.

   有一个已启动并正在运行的python脚本，用于处理报告并以图像，工作表或其他形式的数据形式提供见解。

3. Want to run the process over an extended period at regular intervals of time but heavily dread having to run it manually.

   想要以固定的时间间隔长时间运行该过程，但是非常麻烦，必须手动运行。

There are fun parts of Data Engineering — like programming and analyzing data. Then there are boring parts, like using the already written programs on a regular basis to get the most recent results. Clearly, nobody wants to do the latter. Here is a quick fix I use to avoid running those scripts which need to be executed daily/weekly/monthly so that this time can be put to use better.

数据工程中有趣的部分-例如编程和分析数据。 然后是无聊的部分，例如定期使用已编写的程序来获取最新结果。 显然，没有人愿意做后者。 这是我用来避免运行那些每天/每周/每月执行一次的脚本的快速修补程序，以便可以更好地利用此时间。

Three words — Macro, Batch, and schedule.

三个词-宏，批处理和计划。

1. I use a macro to save my incoming excel reports to a location as and when they come. (I get it from a place which performs the analysis and sends a report to me manually. No, there is no way around this).

   我使用宏将传入的excel报告保存到位置。 (我从执行分析并手动发送报告的地方获得它。没有，这没有办法)。

2. I set up an outlook rule to run the macro only on desired folders along with few other conditions (sender, subject etc).

   我设置了一个Outlook规则，以仅在所需的文件夹以及其他一些条件(发送者，主题等)上运行宏。

3. I write a batch file (.bat), which on clicking executes the script on command prompt.

   我编写了一个批处理文件 (.bat)，单击该文件将在命令提示符下执行脚本。

4. I schedule the bat file using the Windows Task scheduler to execute the script with the same frequency as the arrival of new excel reports

   我使用Windows Task Scheduler计划 bat文件，以与新excel报告到来相同的频率执行脚本

5. I write a PowerShell script to email the newest results to all the necessary recipients and then I schedule it to run soon after the bat file executes.

   我编写了一个PowerShell脚本 ，将最新结果通过电子邮件发送给所有必要的收件人，然后安排它在bat文件执行后立即运行。

步骤1：Outlook宏 (Step 1: Outlook Macros)

1. You can activate VBA scripting in Outlook by activating it in the options pane of outlook.
   您可以通过在Outlook的选项窗格中激活VBA脚本来在Outlook中激活它。
2. You will then see that a button for developer tools appears at the top of the window in your outlook homepage.
   然后，您将在Outlook主页的窗口顶部看到用于开发人员工具的按钮。

3. Go to the Developer Tools tab and start-up Visual Basic scripting.

3.转到“开发人员工具”选项卡，并启动Visual Basic脚本。

4. Here create a new module and script your code to save incoming attachments.

4.在这里创建一个新模块并编写代码脚本以保存传入的附件。

Easy Peasy.

十分简单。

In the example below, the report name is always the subject of the automated email I get. So I used this criterion to decide the save name of my file and save it to a folder.

在下面的示例中，报告名称始终是我收到的自动电子邮件的主题。 因此，我使用此标准来确定文件的保存名称并将其保存到文件夹。

(I also make sure to save it with the date so that I know that the destination folder always has the most recent file before executing my script.)

(我还确保将其与日期一起保存，以便在执行脚本之前知道目标文件夹始终具有最新文件。)

VBA script to save attachments on incoming email:

VBA脚本可在收到的电子邮件中保存附件：

Public Sub SaveRegularReports(MItem As Outlook.MailItem)
Dim oAttachment As Outlook.Attachment
Dim sSaveFolder As String
sSaveFolder = "/You/want/to/save/here/"
If MItem.Subject = "Quarterly_rep" Then
 savename = "QRep"
ElseIf MItem.Subject = "JustSomeRegRep" Then
 savename = "RegRep"
ElseIf MItem.Subject = "WeeklyReport" Then
 savename = "WeeklyUpdate"
End If
For Each oAttachment In MItem.Attachments
oAttachment.SaveAsFile sSaveFolder & savename & Format(MItem.ReceivedTime, "mmddyyyy") & ".xlsx"
Next
End Sub

Pardon my German, But I’m sure you will recognize these settings in English too!

请原谅我的德语，但我敢肯定，您也会用英语识别这些设置！

Now, all I have to do is decide whom to apply these rules to. So I go to rules and set up the conditions I want to apply the script to.

现在，我要做的就是决定将这些规则应用于谁。 因此，我将遵循规则并设置要应用脚本的条件。

In my example, I have the conditions that the email always comes from one specific address with a subject containing the name of the report. (I moved all such regular reports to a separate folder in the inbox for safety)

在我的示例中，我的条件是电子邮件始终来自一个特定的地址，主题包含报告名称。 (出于安全考虑，我将所有此类常规报告移至收件箱中的单独文件夹中)

In the next step, As an action, I execute the above script SaveRegularReports whenever this folder gets a newbie.

下一步，作为一个动作，只要此文件夹有新手，我就会执行上面的脚本SaveRegularReports。

Voila! Autosaved everything I need without lifting a finger!!

瞧！ 自动保存我需要的所有东西而无需费力！

第2步。编写一个bat文件以在保存的Excel工作表上执行python脚本 (Step 2. Write a bat file to execute your python script on saved excel sheets)

This is also a piece of cake. A bat (or batch) file is a set of commands that get executed by the command prompt when you click it. If you ever wrote bash commands or used the windows command prompt shell scripts to “pip install” and the like, you know what I’m talking about.

这也是小菜一碟。 蝙蝠(或批处理)文件是一组命令，单击它们时它们会由命令提示符执行。 如果您曾经编写过bash命令或使用Windows命令提示符shell脚本来进行“ pip install”之类的操作，您就会知道我在说什么。

1. Open up a text file and write commands similar to those below to execute your python script, as you would on the command prompt. (I am using Anaconda, So I first activate the anaconda Prompt before executing my python script).
   像在命令提示符下一样，打开一个文本文件并编写类似于以下命令的命令以执行python脚本。 (我正在使用Anaconda，因此在执行我的python脚本之前，我首先激活anaconda Prompt)。
2. Save this file as filename.bat
   将此文件另存为filename.bat

@echo offcall C:\Users\myUSer\AppData\Local\anaconda\Scripts\activate.batC:\This\is\Where\Your\Script\Lives\Scriptchen.pypause

When I click on the above bat file (scriptchens_bat.bat), my python script gets executed. It uses the saved excel reports from step 1.

当我单击上面的bat文件(scriptchens_bat.bat)时，我的python脚本被执行。 它使用步骤1中保存的Excel报告。

步骤3.任务计划您的脚本执行 (Step 3. Task-Schedule your script execution)

Note: I know I receive my reports every week, say on Friday at 10 AM. I set this script to run every Friday at 10:30.

注意：我知道我每周都会收到报告，例如星期五的上午10点。 我将此脚本设置为在每个星期五的10:30运行。

1. Open up the Task scheduler from the search bar in Windows. Go to Create Basic Task.
   在Windows的搜索栏中打开“任务计划程序”。 转到创建基本任务。
2. After naming and describing it in the appearing window, go to trigger pane and set up your schedule. In this example to 10:30, Fridays.
   在出现的窗口中命名并描述它之后，转到“触发”窗格并设置您的时间表。 在此示例中，星期五为10:30。

Now comes the meaty part. Go to the Actions pane. Here is where you set your bat file up for execution. Select “Start Program” from actions, and in the program pane, give the path to your .bat file you wrote in step 2.

现在是肉的部分。 转到“动作”窗格。 在这里设置蝙蝠文件以执行。 从操作中选择“启动程序”，然后在程序窗格中，提供在步骤2中编写的.bat文件的路径。

click ok and confirm.

单击确定并确认。

Voila! you will now have output of your Python script every week without lifting a finger!

瞧！ 现在，您每周都可以轻松输出Python脚本！

步骤4.通过电子邮件发送结果 (Step 4. Emailing the Results)

If you are happy with just having the most updated results, you can stop here. But if you want to go the extra mile and also automate the distribution of your processing results, grab a coffee and read through to the last step.

如果您对最新的结果感到满意，可以在这里停止。 但是，如果您想加倍努力并自动分配处理结果，请喝杯咖啡，通读最后一步。

I decided to use powershell to email my results with outlook to other coworkers. Here is how:

我决定使用Powershell通过电子邮件将我的搜索结果和Outlook信息发送给其他同事。 方法如下：

1. Open up PowerShell ISE from windows search.
   通过Windows搜索打开PowerShell ISE。
2. The following script is an example for my case, which is to mail the results separately to all recipients with a body and a subject line. You can modify your script to suit your needs.
   以下脚本是我的情况的一个示例，该示例将结果连同正文和主题行分别邮寄给所有收件人。 您可以修改脚本以适合您的需求。

$OL = New-Object -ComObject outlook.applicationStart-Sleep 5$recipients = @(“recipient_one@company.de”,”recipient_two@company.de”,”recipient_three@company.de”)foreach ($recipient in $recipients){$mItem = $OL.CreateItem(“olMailItem”)$mItem.To = $recipient$mItem.Subject = “Automatic Email: Weekly Report”$mItem.Body = “This is an automated email sent using powershell. In case of any questions please contact Me. Dieses Dokument enthält… “$mItem.Attachments.Add(“\This\is\where\my\python\output\lives\file_to_email.xlsx”)$mItem.Send()}

Powershell script to automate sending an email

Powershell脚本自动发送电子邮件

步骤5.设置任务计划程序，将您的结果通过电子邮件发送给收件人 (Step 5. Set up Task Scheduler to email your results to the recipients)

Similar to step 3, you will now schedule your powershell script to execute every week soon after your python script is executed.

与第3步类似，现在您将计划将Powershell脚本安排为在执行python脚本后每周执行一次。

1. Go to New Task in your task scheduler
   在任务计划程序中转到“新任务”
2. Name and describe your task
   命名并描述您的任务
3. Go To trigger and set up your schedule
   转到触发并设置您的时间表
4. Go to action. In the program/script bar, enter the address to your PowerShell application. It is usually here:
   采取行动。 在程序/脚本栏中，输入您的PowerShell应用程序的地址。 通常在这里：

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

In the argument bar, enter the path to your .ps1 script written earlier.

在参数栏中，输入先前编写的.ps1脚本的路径。

Voila! You sent an email without lifting a finger!

瞧！ 您发送电子邮件时没有松手！

So to summarize, you automated an entire chain of events starting from receiving an excel file, saving it to a location, processing it with python and sending the results in an email. This is a pretty neat hack that saves me a bunch of time at work. Hopefully, it helps you all too!

综上所述，您可以自动执行整个事件链，从接收excel文件，将其保存到某个位置，使用python处理并将结果发送到电子邮件中开始。 这是一个非常简洁的技巧，为我节省了很多时间。 希望它也对您有帮助！

翻译自: https://towardsdatascience.com/from-outlook-to-python-results-a-sweet-hack-to-cut-your-work-in-half-c2741a403f40