ReceivedTime errors when trying to read emails saved in shared drive folder

Question

I'm trying read a stack of saved .msg emails in a folder on a shared drive.

I can't get into Outlook to search directly because my organisation won't allow me to because it's a department shared email.

The saved folder is my workaround. I need to pull the file path and the date that we received the email from these files and put them into Excel. I can't take the date that the file was created because otherwise that'll be the date that I saved the file.

I managed to get the file path, but as soon as I try to get the received time it breaks.

I tried setting the ReceivedTime as an object, a string, a date.
If I Dim it as an object it whines that it's object not set, if I set the ReceivedTime it does the same.
If I dim it as string I get error 91, if I remove the with statement it still gives me error 91.
If I delete ReceivedTime = MailItem.ReceivedTime it moves on to the next time it's mentioned and yells at me about that part.

I checked my spelling of Received all through the code and that's not it.

Here's my code at the moment. I edited the file location for privacy. It works without the received time part, so the file location isn't the problem.

Sub FileSearchAlt()

Worksheets("Sheet1").Activate

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
Dim oMsg As Outlook.MailItem
Dim MailItem As Object
Dim ReceivedTime As String

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder("C:\Users\THE LOCATION OF MY FILE")

With oMsg

    ReceivedTime = MailItem.ReceivedTime

    For Each oFile In oFolder.Files
        Cells(i + 1, 1) = "C:\Users\THE LOCATION OF MY FILE" & oFile.Name
        Cells(i + 1, 2) = MailItem.ReceivedTime
        
        i = i + 1
    
    Next oFile
End With

End Sub

`ReceivedTime` is a date and you cannot read it until you open the mail item in outlook. That's when all of the email's properties will be populated. Right now you are working with just a local file that doesn't know what the received time is. https://learn.microsoft.com/en-us/office/vba/api/outlook.mailitem.receivedtime — Kostas K., Jun 14 '22 at 10:31
You want a `MailItem` object https://learn.microsoft.com/en-us/office/vba/api/outlook.mailitem but you are working with a `File` object https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/file-object. These are completely different things. — Kostas K., Jun 14 '22 at 10:36
`MailItem` variable is never initialized. Where is it supposed to come from? — Dmitry Streblechenko, Jun 14 '22 at 15:38
Does this answer your question? [Excel VBA: Loop Through a Directory of .msg files](https://stackoverflow.com/questions/56569481/excel-vba-loop-through-a-directory-of-msg-files) — niton, Jul 13 '22 at 22:33

score 0 · Answer 1 · answered Jun 14 '22 at 15:38

0

It seems you are trying to get OOM properties from MSG files saved to the disk:

Cells(i + 1, 2) = MailItem.ReceivedTime

In that case you need to open such files in Outlook and then read the properties using the Outlook object model. The OpenSharedItem method allows to open a shared item from a specified path or URL. This method is used to open iCalendar appointment (.ics) files, vCard (.vcf) files, and Outlook message (.msg) files. The type of object returned by this method depends on the type of shared item opened.

answered Jun 14 '22 at 15:38

Eugene Astafiev

47,483
3
24
45

But how does that work with the difference between searching a desktop type folder and an outlook folder? I don't understand how to use outlook to search for files that aren't in outlook. Whenever I try to look up people coding with OpenSharedItem things they're only searching IN outlook folders, which isn't what I want. I can't seem to see anyone doing what I need to do. It seems like searching a desktop folder and searching outlook items won't agree, so I don't know what to do. – Unda Jun 24 '22 at 13:19
Windows folders and Outlook folders are entirely different entities that are not related to each other. – Eugene Astafiev Jun 24 '22 at 19:08

score 0 · Answer 2 · answered Jul 27 '22 at 21:44

Dir allows access to files in desktop folders.

Option Explicit ' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant

Sub FileSearchAlt()

Dim oApp As Object
Dim oNs As Object

Dim oFSO As Object
Dim desktopFolder As Object

Dim fPath As String
Dim fName As String
Dim fPathName As String

Dim i As Long

Dim oMsg As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

fPath = "C:\Users\THE LOCATION OF MY FILE"

'Dim enviro As String
'enviro = CStr(Environ("USERPROFILE"))
'fPath = enviro & "\Test\"

Debug.Print "fPath........: " & fPath

If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
    Debug.Print "with backslash added"
    Debug.Print "fPath........: " & fPath
End If

Set desktopFolder = oFSO.GetFolder(fPath)
Debug.Print "desktopFolder: " & desktopFolder.Name

Set oApp = CreateObject("Outlook.Application")
Set oNs = oApp.getnamespace("MAPI")

Debug.Print "Dir parameter: " & fPath & "*.msg"
fName = Dir(fPath & "*.msg")

Debug.Print "fName........: " & fName

Do While fName <> ""

    fPathName = fPath & fName
    Debug.Print "fPathName....: " & fPathName
    
    Set oMsg = oNs.OpenSharedItem(fPathName)
    
    With Worksheets("Sheet1")
        .Cells(i + 1, 1) = fPathName
        .Cells(i + 1, 2) = oMsg.receivedTime
        i = i + 1
    End With
    
    fName = Dir
    Debug.Print "fName........: " & fName
    
Loop

Debug.Print "Done."

End Sub

ReceivedTime errors when trying to read emails saved in shared drive folder

2 Answers2