CODEWITHSUNDEEP
excelvbaoutlook
Unda
Unda

Reputation: 1

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

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

Upvotes: 0

Views: 256

Answers (2)

niton
niton

Reputation: 9199

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

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

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.

Upvotes: 0

Related Questions