vba - 将 Outlook 电子邮件回复作为任务收集并填充到 Excel 电子表格中

Question

将不胜感激任何帮助/建议

我对 VBA 和宏很陌生。我从事行政工作，我有很多相互关联的任务，我想加快这个过程。

我收到一封来自经理的批准电子邮件，告诉我新员工在开始工作时需要/批准哪些项目。然后，我需要为我需要记住的每个项目创建一个任务/提醒，以便为该员工组织并将所有响应传输到共享驱动器上的跟踪电子表格，以便我们可以跟踪新员工的应用程序/项目。

如果我可以设置一个规则，那么当我收到其中一封电子邮件时，会自动为每个带有"is"的项目创建一个任务，而忽略“否”和所有“是/否”响应填充到 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

This site确实有助于处理传入邮件的自动化，我强烈建议您阅读有关让 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