收集Outlook电子邮件响应作为任务，并在Excel电子表格中填充

Question

感谢任何帮助/建议

我是VBA＆amp;宏。我从事管理工作，并且有很多相互关联的任务，我想加快该过程。

我会收到经理的批准电子邮件，告诉我新员工启动时将需要/批准哪些项目。然后，我需要为我需要记住的每个项目创建一个任务/提醒，并将所有响应转移到共享驱动器上的跟踪电子表格中，以便我们可以跟踪新员工的应用程序/项目。

伟大的是，如果我可以设置一条规则，以便自动收到以下一封电子邮件为每个单独的项目创建一个"是"的任务，而对"否"而忽略了所有"是"和所有"是//在Excel表中的下一个可用单行中没有"响应"。最好是，如果我将Outlook中的任务标记为"完成"时，它将此信息传输到Excel表格 - 这可能是一厢情愿的想法。

例如，电子邮件可能看起来像这样：

Employee Name: John Doe
Line Manager: Jane Smith
Start Date: 1/1/2012 
Item 1: Yes
Item 2: No
Item 3: Yes
Item 4: Yes

和Excel将为上述每个列提供一个列。

再次 - 任何帮助/建议都非常感谢

Answer 1

此网站确实有助于处理传入邮件的自动化，我强烈建议您阅读有关在此处工作的Outlook VBA的提示。

此代码从该页面稍微改编。从托多（Todo）开始的评论行仍需要填写，但这应该使您在正确的轨道上开始。

Option Explicit
'
' Place this code in the "ThisOutlookSession" class module
'
' The code will:
'
' Monitor the Inbox
' Check for the existence of a specific kind of e-mail
' Move the processed e-mail to a "processed" folder
'
Private WithEvents olInboxItems As Items
'
' Application_Startup() is a reserved function that will automatically
' be called when Outlook starts.
'
Private Sub Application_Startup()
    Set olInboxItems = Session.GetDefaultFolder(olFolderInbox).Items
End Sub
'
' This event is fired when the Inbox receives a new message
' (it can also be fired when manually moving a message from
'  another folder back to the inbox)
'
Private Sub olInboxItems_ItemAdd(ByVal Item As Object)
'    On Error Resume Next (commented out for ease of debugging)
    Dim olMailItem As MailItem
    Dim strAttachmentName As String
    Dim Employee() As Variant
    Dim v() As String
    Dim i As Long
    Dim NumItems As Long
    Dim line As Variant
    '
    ' Only inspect mail items
    ' Ignore appointments, meetings, tasks, etc.
    '
    If TypeOf Item Is MailItem Then
        Set olMailItem = Item
        '
        ' Test for specific subject line
        '
        If InStr(olMailItem.Subject, "My Subject Line") > 0 Then
            ' Get an array of lines in the body of the email
            v = Split(olMailItem.Body, vbCrLf) 
            ' TODO Parse the array for the data required to populate your Excel file
            ' TODO Open (or activate) the Excel file
            ' TODO Add the data to the Excel file
            ' Once complete, move mail item to OK/Errors folder
            ' This code assumes the folders already exist
            ' and are subfolders of the Inbox folder
            '
            ' In older versions of Outlook, olDestFolder
            ' should be declared as type MAPIFolder
            ' instead of Folder
            '
            Dim olDestFolder As Folder, strFolderName As String
            If Err.Number Then
                strFolderName = "Processed_Errors"
            Else
                strFolderName = "Processed_OK"
            End If
            '
            ' Display Message
            '
            Set olDestFolder = _
                Session.GetDefaultFolder(olFolderInbox).Folders(strFolderName)
            If Err.Number Then
                olMailItem.Move olDestFolder
                MsgBox Err.Description + strFolderName + vbCrLf + _
                       "Check the error folder", _
                       vbCritical, "Automated e-Mail processing unsuccessful"
            Else
                olMailItem.Move olDestFolder
                MsgBox "Message has been processed and placed in " + strFolderName, _
                        vbInformation, "Automated e-Mail processing successful"
            End If
        End If
    End If
    Set Item = Nothing
    Set olMailItem = Nothing
End Sub