在指定日期内对电子邮件使用限制方法 [英] Using Restrict method for emails within a specified date

问题描述

我正在创建一个宏，以按主题和收到的日期在我们的团队共享框中获取电子邮件.我的问题是，一旦选择日期(例如1/16/2018至1/17/2018)，对象中将只存储很少的电子邮件. 在下面的屏幕截图中，我有9个项目应用了限制方法.它应该是2018年1月16日之后至今收到的14封电子邮件(屏幕快照中正确的Outlook邮件)，但是5封电子邮件未存储在对象中.谁能帮我吗?我很烂！

I am creating a macro to get email by subject and received date in our team shared box. My problem is that once I select date (e,g 1/16/2018 to 1/17/2018), only few emails are stored in object. In below screenshot, I have 9 items which are applied restrict method. It should be 14 items emails which are received after 1/16/2018 to now(right outlook mail in screenshot), but 5 emails are not stored in object. can anyone help me out? I'm STUCK!

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer
Dim olItems As Outlook.Items
Dim myItems As Outlook.Items
Dim DateStr As Date
Dim DateEnd As Date
Dim oOlResults As Object

Dim DateToCheck As String
Dim DateToCheck2 As String
Dim DateToCheck3 As String

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

Dim olShareName As Outlook.Recipient
Set olShareName = OutlookNamespace.CreateRecipient("[e-mail]")
Set Folder = OutlookNamespace.GetSharedDefaultFolder(olShareName, olFolderInbox).Folders("sub1").Folders("sub2")
Set olItems = Folder.Items


'DateStr = 1/16/2018
'DateEnd = 1/17/2018

DateStr = Format(Range("From_Date").Value, "DDDDD HH:NN")
DateEnd = Format(Range("To_Date").Value, "DDDDD HH:NN")

'DateStr = DateAdd("d", -1, DateStr)
'DateEnd = DateAdd("d", 1, DateEnd)

DateToCheck = "[ReceivedTime] > """ & DateStr & """"
DateToCheck2 = "[ReceivedTime] <= """ & DateEnd & """"
DateToCheck3 = "[SenderName] = ""[e-mail]"""

Set myItems = olItems.Restrict(DateToCheck)
Set myItems = myItems.Restrict(DateToCheck2)
Set myItems = myItems.Restrict(DateToCheck3)

i = 1

For Each myitem In myItems
    ' MsgBox myitem.ReceivedTime

     Range("eMail_subject").Offset(i, 0).Value = myitem.Subject
     Range("eMail_date").Offset(i, 0).Value = myitem.ReceivedTime

     i = i + 1

Next myitem

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing


End Sub

推荐答案

在解释我的评论时，您可以尝试以下注意事项:

Expounding my comment you can try using below with the following considerations:

1. 接收日期，datereceived以UTC表示.因此，您需要根据您的UTC调整时间.就我而言，它是UTC8，所以我需要调整8小时前的时间_{( 注意:没有文档支持此操作，但是当我进行测试时，它以UTC表示.
   情况并非总是如此).}

2. 日期应表示为字符串，如此处.

1. Received date, datereceived is expressed in UTC. So you need to adjust your time depending on your UTC. In my case it is UTC8 so I need to adjust the time 8 hours earlier _{(Note: No documentation to support this, but when I did my testing, it is expressed in UTC.
   It may or may not always be the case).}

2. Date should be expressed as string as stated here.

尽管日期和时间通常以日期格式存储，但是查找和限制"方法要求将日期和时间转换为字符串表示形式.

Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation.

示例:

mydate = Format(Now,"\'m/d/yyy hh:mm AM/PM\'") '/* will give '1/23/2018 01:36 PM' */

sendername可能包含电子邮件地址或电子邮件名称.

sendername may contain the email address or the email name.

---

Sub stancial()
    Dim olItems As Outlook.Items
    Dim olFolder As Outlook.Folder
    Dim olNS As Outlook.NameSpace
    Dim olEmail As Outlook.MailItem
    Dim i As Long

    Dim efilter As String, startdt As String, endindt As String, _
        myUTC As Integer, sentby As String

    myUTC = 8 '/* this is your UTC, change to suit (in my case 8) */

    startdt = Format(DateAdd("h", -myUTC, _
              CDate("1/18/2018 12:00 PM")), "\'m/d/yyyy hh:mm AM/PM\'")
    endindt = Format(DateAdd("h", -myUTC, _
              CDate("1/18/2018 4:00 PM")), "\'m/d/yyyy hh:mm AM/PM\'")
    sentby = "'[e-mail]'" '/* can be sendername, "doe, john" */

    Set olNS = Application.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
    '/* filter in one go, where datereceived is
    'expressed in UTC (Universal Coordinated Time) */
    efilter = "@SQL= (urn:schemas:httpmail:sendername = " & sentby & _
              " And (urn:schemas:httpmail:datereceived >= " & startdt & _
              " And urn:schemas:httpmail:datereceived <= " & endindt & "))"

    Set olItems = olFolder.Items.Restrict(efilter)

    With olItems
        For i = .Count To 1 Step -1 '/* starting from most recent */
            If TypeOf .Item(i) Is MailItem Then
                Set olEmail = .Item(i)
                Debug.Print olEmail.Subject, olEmail.ReceivedTime
            End If
        Next
    End With
End Sub

这篇关于在指定日期内对电子邮件使用限制方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！