Excel VBA - 分析共享邮箱 Outlook - 运行时错误 1004:应用程序定义或对象定义错误 [英] Excel VBA - Analysing Shared Mailbox Outlook - Run time error 1004: Application-defined or object-defined error

问题描述

我在 Excel 中使用以下代码尝试将我们共享邮箱中的详细信息获取到电子表格中以供进一步分析.

I am using the following code in Excel to try to get the details from our shared mailbox into a spreadsheet for further analysis.

代码在尝试获取 Sender、SenderEmailAddress & 时产生运行时错误 1004:应用程序定义或对象定义的错误.发件人姓名.

The code produces a run-time error 1004: application-defined or object-defined error at the point it reaches trying to get the Sender, SenderEmailAddress & SenderName.

当这些部分处于非活动状态并且获取主题、接收时间等没有任何问题时，这很好.

It is fine when these parts are made inactive and it gets the Subject, ReceivedTime, etc without any issue.

有谁知道需要更改什么才能使其正常工作?

Does anyone know what needs to be changed to get that working?

此外，是否有人对如何遍历共享邮箱中的所有文件夹有任何建议，而不必为邮箱层次结构中的每个文件夹设置案例选择?或者甚至是添加所需文件夹的更短方法(即每个文件夹一行代码 vs 2/3/4 行)?

Also, does anyone have any suggestions on how to loop through all of the folders in the shared mailbox instead of having to set-up a Case selection for each folder in the mailbox hierarchy? Or even a shorter way of adding the folders required (i.e. one line of code for each folder vs 2/3/4 lines)?

提前致谢

Sub getEmails()

Dim olApp       As Outlook.Application
Dim olNS        As Outlook.Namespace
Dim olFldr      As Outlook.MAPIFolder
Dim olItem      As Object
Dim olMailItem  As Outlook.MailItem
Dim ws          As Worksheet
Dim iRow        As Long
Dim hdr         As Variant
Dim iFldr       As Long
Dim lstAtt      As String
Dim olAtt       As Outlook.Attachment
Dim dlm         As String

Set ws = ThisWorkbook.Worksheets("Sheet1")

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")

With ws
    iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False

For iFldr = 1 To 2
    Select Case iFldr
        Case 1
            Set olFldr = olNS.Folders(1)
            Set olFldr = olFldr.Folders("Inbox")
            'Set olFldr = olFldr.Folders("Access Requests")
            'Set olFldr = olFldr.Folders("Ad-hoc Requests")
        Case 2
            Set olFldr = olNS.Folders(1)
            Set olFldr = olFldr.Folders("Inbox")
            Set olFldr = olFldr.Folders("Folders")
        Case Else
    End Select

    For Each olItem In olFldr.Items
        If olItem.Class = olMail Then
            Set olMailItem = olItem
                iRow = iRow + 1
            With olMailItem
                If Not .Sender Is Nothing Then ws.Cells(iRow, "D") = .Subject
                ws.Cells(iRow, "A") = .Sender
                ws.Cells(iRow, "B") = .SenderEmailAddress
                ws.Cells(iRow, "C") = .SenderName

                ws.Cells(iRow, "E") = .ReceivedTime
                ws.Cells(iRow, "F") = .Categories
                ws.Cells(iRow, "G") = .TaskCompletedDate
                ws.Cells(iRow, "H") = olFldr.Name
                lstAtt = ""
                dlm = ""
                For Each olAtt In .attachments
                    lstAtt = lstAtt & dlm & olAtt.DisplayName
                    dlm = ";" 'Chr(10)
                Next
                ws.Cells(iRow, "I") = lstAtt
            End With
        End If
    Next olItem
Next iFldr


With ws
    hdr = Array("Sender", "SenderEmailAddress", "SenderName", "Subject", "ReceivedTime", "Categories", "TaskCompletedDate", "Folder", "Attachments")
    .Range("A1").Resize(, UBound(hdr)) = hdr
    .Columns.AutoFit
End With

Application.ScreenUpdating = False

MsgBox "Complete!"

结束子

推荐答案

MailItem.Sender 返回对象 (AddressEntry)，而不是标量值(字符串或整数)).您已经在访问 SenderEmailAddress 和 SenderName，为什么还需要 Sender?

MailItem.Sender returns an object (AddressEntry), not a scalar value (string or an int). You are already accessing SenderEmailAddress and SenderName, why do you need Sender?

此外，您假设第一个存储始终是默认邮箱.情况并非总是如此.请改用 Namespace.GetDefaultFolder(olFolderInbox).

Also, you are assuming that the first store is always the default mailbox. That is not always the case. Use Namespace.GetDefaultFolder(olFolderInbox) instead.

这篇关于Excel VBA - 分析共享邮箱 Outlook - 运行时错误 1004:应用程序定义或对象定义错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！