将电子邮件从Outlook子文件夹导出到Excel [英] Exporting emails from an Outlook subfolder to Excel
问题描述
我发现一个VBA宏,它从Outlook接收电子邮件,并将它们放入Excel中的单元格中。
代码可以正常工作,但是我想告诉Excel只能从特定的子文件夹获取电子邮件。在我的收件箱文件夹中,我有一个名为Info的子文件夹。我想要能够从这个子文件夹获取电子邮件。
I've found a VBA macro that takes e-mails from Outlook and put them into cells in Excel. The code works, but I want to tell Excel only to get e-mails from a specific subfolder. In my Inbox-folder I have a subfolder called Info. I want to be able to get e-mails from this subfolder.
这是我现在的代码:
Sub Download_Outlook_Mail_To_Excel()
'Add Tools->References->"Microsoft Outlook nn.n Object Library"
'nn.n varies as per our Outlook Installation
Dim folders As Outlook.folders
Dim folder As Outlook.MAPIFolder
Dim iRow As Integer
Dim Pst_Folder_Name
Dim MailboxName
'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
MailboxName = "My email address"
'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
Pst_Folder_Name = "Inbox"
Set folder = Outlook.Session.folders(MailboxName).folders(Pst_Folder_Name)
If folder = "" Then
MsgBox "Invalid Data in Input"
GoTo end_lbl1:
End If
'Rad Through each Mail and export the details to Excel for Email Archival
Sheets(1).Activate
For iRow = 1 To folder.Items.Count
Sheets(1).Cells(iRow, 1).Select
Sheets(1).Cells(iRow, 1) = folder.Items.Item(iRow).SenderName
Sheets(1).Cells(iRow, 2) = folder.Items.Item(iRow).Subject
Sheets(1).Cells(iRow, 3) = folder.Items.Item(iRow).ReceivedTime
Sheets(1).Cells(iRow, 4) = folder.Items.Item(iRow).Size
Sheets(1).Cells(iRow, 5) = folder.Items.Item(iRow).SenderEmailAddress
'Sheets(1).Cells(iRow, 6) = Folder.Items.Item(iRow).Body
Next iRow
MsgBox "Outlook Mails Extracted to Excel"
end_lbl1:
End Sub
如何确保它从子文件夹获取电子邮件,而不是主文件夹?
How can I make sure that it gets emails from the subfolder, and not the mainfolder?
推荐答案
尝试使用以下代码:
'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
MailboxName = "My email address"
'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
Pst_Folder_Name = "Inbox"
' subfolder name
Dim subFolderName As String
subFolderName = "Info"
Set folder = Outlook.Session.folders(MailboxName).folders(Pst_Folder_Name).Folders(subFoldersName)
If folder = "" Then
MsgBox "Invalid Data in Input"
GoTo end_lbl1:
End If
另外您可以考虑使用 GetDefaultFolder 命名空间类的方法来获取文件夹表示当前配置文件的请求类型的默认文件夹的对象。
Also you may consider using the GetDefaultFolder method of the Namespace class to get a Folder object that represents the default folder of the requested type for the current profile.
这篇关于将电子邮件从Outlook子文件夹导出到Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!