0

I'm trying from Excel to scan a shared inbox for emails with attachments, which were received on a certain date. The aim is to save the attachments and import them into the workbook running the code.

Here's the code I have so far adapted from Download attachment from Outlook and Open in Excel to scan the inbox and print some info on the emails it finds

Sub extractEmailDetails()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object, oOlInp As Object
    Dim oOlItm As Object
    Dim strDateFrom As String, strDateTo As String

    Dim searchDate As Date

    searchDate = #12/9/2015# 'mm/dd/yyyy
    strDateFrom = "'" & Format(searchDate, "dd/mm/yyyy") & "'"
    strDateTo = "'" & Format(searchDate + 1, "dd/mm/yyyy") & "'"

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInp = oOlns.Folders("SHR-Cust Ops MI Team Inbox")
    Set oOlInb = oOlInp.Folders("Inbox")

    '~~> Store the relevant info in the variables
    For Each oOlItm In oOlInb.Items.Restrict("[attachment] = True AND [receivedTime] > " & strDateFrom & " AND [receivedTime] < " & strDateTo)
        Debug.Print oOlItm.ReceivedTime & " " & oOlItm.Subject
    Next

End Sub

When I search for the 8th of December it only brings back emails that were received after 8am.

I changed the settings for working hours in the calendar to midnight to midnight (no working hours) and the code then brought back all emails for the specified date. However, I can't leave my calendar with no working hours. Is there a way to change the default behaviour to ignore the working hours?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
diberlee
  • 1

2 Answers2

0

It sure sounds like your are getting GMT + your local time zone offset. What is your TZ?

Dmitry Streblechenko
  • 62,942
  • 4
  • 53
  • 78
  • I'm in the GMT timezone. I did notice earlier that the calendar was displaying the weather for Washington and didn't think anything of it. – diberlee Dec 11 '15 at 00:11
0

After messing around with this a little I've found a solution. A very obvious one. You can't just provide the date, you also need to provide a time, so:

[...]
strDateFrom = "'" & Format(searchDate, "dd/mm/yyyy") & "'"
strDateTo = "'" & Format(searchDate + 1, "dd/mm/yyyy") & "'"

Becomes

[...]
strDateFrom = "'" & Format(searchDate, "dd/mm/yyyy hh:mm") & "'"
strDateTo = "'" & Format(searchDate + 1, "dd/mm/yyyy hh:mm") & "'"
diberlee
  • 1