从Excel参考Outlook文件夹 [英] Reference Outlook folder from Excel

问题描述

我创建了一个宏来读取Outlook电子邮件，并将值和标头(发送时间等)提取到Excel.

I have created a macro to read Outlook emails and extract values and headers (subject time sent etc.) to Excel.

这以前曾经起作用，但现在提示错误尝试的操作失败.找不到对象".

This was working before but now prompts an error "The attempted operation failed. An object could not be found".

文件夹的位置是收件箱/TIBCO报告文件夹".错误提示在行

The location of the folder is "Inbox/TIBCO Reports Folder". The error is prompting at line

Set olFolder = olFolder.Folders(1).Folders("Inbox").Folders("TIBCO Reports Folder")

这是代码的一部分:

Dim olApp As Object
Dim olFolder As Object
Dim olMailItem As Object

Dim strTo As String
Dim strFrom As String
Dim dateSent As Variant
Dim dateReceived As Variant
Dim strSubject As String
Dim strBody As String

Dim date1 As Date
Dim date2 As Date

Dim loopControl As Variant
Dim mailCount As Long
Dim totalItems As Long
 '-------------------------------------------------------------

 '//Turn off screen updating
Application.ScreenUpdating = False

 '//Clearing worksheet content
 'Sheets("OutlookEmail").Cells.Clear

 '//Setup headers for information
Sheets("OutlookEmail").Select
Range("A1:F1").Value = Array("Subject", "From", "Date/Time Sent", "Date/Time Received", "To", "Attachment")

 '//Format columns E and F to
Columns("C:D").EntireColumn.NumberFormat = "MM/DD/YYYY HH:MM:SS"

 '//Create instance of Outlook
Set olApp = CreateObject("Outlook.Application")

 '//Select folder to extract mail from
Set olFolder = olApp.GetNamespace("MAPI")
Set olFolder = olFolder.Folders(1).Folders("Inbox").Folders("TIBCO Reports Folder")

推荐答案

从Excel中使用Outlook时，请像这样设置Outlook收件箱引用.

When you are working with Outlook from Excel set your Outlook Inbox references like this.

Option Explicit
Public Sub Example()
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim Inbox As Outlook.MAPIFolder
    Dim Items As Outlook.Items
    Dim i As Long

    '// Ref to Outlook Inbox
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set Inbox = olNS.GetDefaultFolder(olFolderInbox).Folders("Folder Name")

    Set Items = Inbox.Items

    For i = Items.count To 1 Step -1
        Debug.Print Items(i) '
'       do something with Items
    Next
End Sub

确保在VBE的工具">引用"菜单中设置Microsoft Outlook对象XX.X

Make sure to set Microsoft Outlook Object XX.X in the Tools>Reference menu of VBE

在此处查看另一个示例 https://stackoverflow.com/a/40356349/4539709

See another example here https://stackoverflow.com/a/40356349/4539709

这篇关于从Excel参考Outlook文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！