从 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的Tools>Reference菜单中设置Microsoft Outlook Object XX.X
Make sure to set Microsoft Outlook Object XX.X in the Tools>Reference menu of VBE
在此处查看另一个示例https://stackoverflow.com/a/40356349/4539709
这篇关于从 Excel 引用 Outlook 文件夹的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!