Extracting Outlook messages metadata to Excel spreadsheet

[peddy00]

Hi,

I have 26 emails in a folder with path C:\Users\Peter\Desktop\New folder, all with the file extension .msg. I'd like to extract the sender, recipient, Cc, and other data from the emails, and set them in a spreadsheet. I found something that I thought would be helpful at a different forum (though, I don't need the body of the message in my spreadsheet), but I have a few problems with it.

First, how is that macro supposed to be called? When I'm in the VBA editor, and hit F5, the macro doesn't start.

Second, I can't see where I'm supposed to put the specific path I want to use into the code.

Third, just wanting to see what would happen, I deleted Path As String from the first line of the macro, then run the code, but the compiler gives me an error, saying that MyOutlook As Outlook.Application is a "User-defined type not defined."

Anyway, all I really care about is getting To, Cc, From, SentOn, and, if possible, a list of any attachments in the emails, I don't need the macro referred to above to work.

If this isn't clear, please feel free to ask for clarification. Thanks.

Peter

 

[Comfy]

Thanks for all the patient help, Comfy. I now have all the information I need.

Peter

You're more than welcome

 

[Alexander1987]

Hello Peter,

Can you please help me out, I know its been a while since you were dealing with this task, but I really could use some advise from you!
I have the same need as you did, so I have lots of Outlook .msg files in a folder and I want to extract their metadata to an Excel spreadsheet automatically.
I am new in VBA, so I just copy-pasted your code into a new module, changed the Path for my case, but while compiling I received an error message: "Sub or function is not defined".
What am I doing wrong?

Thanks for your time!

With Best Regards
Alexander

 

[Alexander1987]

I'm terribly sorry for my inattention, of course I am greeting Comfy also and hope you could help me too!

Alexander

 

[agouveia]

Hello,

I'm trying to run this same code... with my own path attached to it, what I have looks like this:

Sub GetMailInfo()


Dim MyOutlook As Outlook.Application
Dim msg As Outlook.MailItem
Dim x As Namespace
Dim Path As String
Dim i As Long


Set MyOutlook = New Outlook.Application
Set x = MyOutlook.GetNamespace("MAPI")

Path = "C:\Users\gouveiar\TEST 2"
FileList = GetFileList(Path + "*.msg")


Row = 1


While Row <= UBound(FileList)


Set msg = x.OpenSharedItem(Path + FileList(Row))


Cells(Row + 1, 1) = msg.Subject
Cells(Row + 1, 2) = msg.SenderName
Cells(Row + 1, 3) = msg.SenderEmailAddress
Cells(Row + 1, 4) = msg.CC
Cells(Row + 1, 5) = msg.To
Cells(Row + 1, 6) = msg.SentOn
Cells(Row + 1, 7) = msg.Size
If msg.Attachments.Count > 0 Then
For i = 1 To msg.Attachments.Count
Cells(Row + 1, 7 + i) = msg.Attachments.Item(i).FileName
Next i
End If

Row = Row + 1
Wend


End Sub
Sub test()
Dim p As String, x As Variant


p = "C:\Users\gouveiar\TEST 2"
x = GetFileList(p)
Select Case IsArray(x)
Case True 'files found
MsgBox UBound(x)
Sheets("Sheet1").Range("A:A").Clear
For i = LBound(x) To UBound(x)
Sheets("Sheet1").Cells(i, 1).Value = x(i)
Next i
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub






Function GetFileList(FileSpec As String) As Variant
' Taken from Excel Tips From John Walkenbach: Getting A List Of File Names Using VBA
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False




Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String




On Error GoTo NoFilesFound




FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound




' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function




' Error handler
NoFilesFound:
GetFileList = False
End Function


The problem is that, my latest run stops at Row <= UBound and says I have a type 13 mismatch issue. Anyone could please help me with this???
Ana