导出到Excel的电子邮件数据-按接收日期排序 [英] Email data exported to Excel - Sort by Received Date

问题描述

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

I am writting a macro to export email data to excel file for monthly report.

但是我已经意识到，提取出的数据与日期并不一致.全部混乱了.

However I have realised that the data being pulled out is not accordingly to dates; all jumbled up.

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

Some mailboxes have tons of emails
Hence taking efficiency issue in consideration, is there any way to get the data in latest to oldest order?

下面是代码的一部分:

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

推荐答案

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

You need to sort the Items collection first:

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

这篇关于导出到Excel的电子邮件数据-按接收日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！