时间戳

首页 > 解决方案 > 使用 Outlook VBA 将邮件信息导入 Excel

vba - 使用 Outlook VBA 将邮件信息导入 Excel

问题描述

我正在尝试从特定文件夹中的未读电子邮件中提取信息并将其粘贴到结构化的 Excel 文件中。

我需要主题、正文、发件人、接收时间、对话 ID 和附件名称。

重要提示：它必须是 Outlook 宏而不是 Excel 宏。

我有以下代码，但它给了我

“运行时错误'9'：下标超出范围。

Sub WriteTextFile()

    Dim wkb As Workbook
    Set wkb = Workbooks.Open("C:\Users\bebxadvypat\Desktop\Test VBA Macros.xlsx")
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set Workbook = objExcel.Workbooks.Open("C:\Users\bebxadvypat\Desktop\Test VBA Macros.xlsx")
    
    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder
    Dim row As Integer
    
    row = 2

    objExcel.Worksheets("Sheet1").Cells(1, 1).Value = "Sender"
    objExcel.Worksheets("Sheet1").Cells(1, 2).Value = "Subject"
    objExcel.Worksheets("Sheet1").Cells(1, 3).Value = "Date"
    objExcel.Worksheets("Sheet1").Cells(1, 4).Value = "ID"
    objExcel.Worksheets("Sheet1").Cells(1, 5).Value = "Body"

    Set objNS = GetNamespace("MAPI")
    Set objFolder = objNS.Folders.GetLast
    Set objFolder = objFolder.Folders("Deleted Items")
    
    For Each item In objFolder.Items
        objExcel.Worksheets("Sheet1").Cells(row, 1).Value = item.sender
        objExcel.Worksheets("Sheet1").Cells(row, 2).Value = item.Subject
        objExcel.Worksheets("Sheet1").Cells(row, 3).Value = item.ReceivedTime
        objExcel.Worksheets("Sheet1").Cells(row, 4).Value = item.ConversationID
        objExcel.Worksheets("Sheet1").Cells(row, 5).Value = item.Body
    Next

    Workbook.Save
    Workbook.Saved = True
    Workbook.Close
    objExcel.Quit
      
    Set Workbook = Nothing
    Set objExcel = Nothing
    
End Sub

标签： vbaoutlook

解决方案

根据测试您的代码，您可以尝试添加row = row+1并使用此方法来获取删除项：

Set olNs = Application.GetNamespace("MAPI")
Set DeletedFolder = olNs.GetDefaultFolder(olFolderDeletedItems)
    For Each Item In objFolder.Items
        objExcel.Worksheets("Sheet1").Cells(row, 1).Value = Item.Sender
        objExcel.Worksheets("Sheet1").Cells(row, 2).Value = Item.Subject
        objExcel.Worksheets("Sheet1").Cells(row, 3).Value = Item.ReceivedTime
        objExcel.Worksheets("Sheet1").Cells(row, 4).Value = Item.ConversationID
        objExcel.Worksheets("Sheet1").Cells(row, 5).Value = Item.Body
        row = row + 1
    Next

推荐阅读