excel - 导出到 Excel 的电子邮件数据 - 按接收日期排序

Question

我正在编写一个宏来将电子邮件数据导出到 excel 文件以获取月度报告。

但是，我意识到提取的数据与日期不相符；一切都困惑了。

有些邮箱有大量的电子邮件
因此考虑到效率问题，有什么方法可以按最新到最旧的顺序获取数据？

以下是部分代码:

Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itm As Object

'Select export folder
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder

'Open and activate Excel workbook.
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
appExcel.Application.Visible = True

'Copy field items in mail folder.
 For Each itm In fld.Items
'Check item type
If TypeName(itm) = "MailItem" Then
intColumnCounter = 1
Set msg = itm
intRowCounter = intRowCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SenderName
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = GetLastVerb(msg)
End If
Next

Answer 1

您需要先对 Items 集合进行排序:

set Items = fld.Items
Items.Sort "ReceivedTime", true
For Each itm In Items
  ...