exporting data from Outlook to Excel

[Codman]

I have a task of creating a monthly report which shows the following data from outlook messages in a specifc folder. I need to show: -

a) The senders address
b) The Mail Header
c) The reply date and as the messages are always forwarded, the forwarding address.
d) The date of each event.

I'm blowed if I can find the objects to do this. Can anyone guide me here?

CodMan

 

[CautionMP]

Codman,
On the side of simple, have you looked at Exporting the folder to Excel from Outlook (File=>Import and Export...)? I believe this will get you the fields your looking for.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

Check out OutlookSpy. By far the best tool for disecting the Outlook Object Model (IMHO).

HTH

-----------
Regards,
Zack Barresse

 

[Codman]

Hi Guy's,

I tried the File export approach but could not find a field that shows the forward address or date. I assume this is not available using this function, or is it that I've missed it??

I also tried the outlookspy program and although this gives lots of objects and I can find the To: name and address, I still cannot see the forwarded: name and address. Any clues would be appreciated.

CodMan

 

[firefytr]

Do you have any code as of yet? If so, can you post it?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[CautionMP]

Codman,
Ok, I played around with the code a little bit and here is what I found.
[ol a]
[li]Outlook will return the Sender Name which is not necessarily the email address.[/li]
[li]Not sure what you mean my Mail Header, I used Subject.[/li]
[li]The forward date/address are 'look back' data, they are not stored with the original message. They can be derrived from the next message in the conversation*.[/li]
[li]I used the [tt]SentOn[/tt] field.[/li][/ol]

* As near as I can tell there is a [tt]ConversationTopic[/tt] and [tt]ConversationIndex[/tt] that are used in conjunction with an action prefix (RE:, FWD:, ...) to determine what has been done with a message. The code below grabs all these fields so you can see what I'm talking about.

With that said here is a possible starting point, this runs on my machine (Excel/Outlook 2k) without the need for adding any external references to the project.

Sub GetOutlookItems()
On Error Goto GetOutlookItems_Error
'Outlook stuff
Dim objOutlook As Object
Dim objFolder As Object
Dim objTarget As Object
Dim objItem As Object
'Excel stuff
Dim wksOutput As Worksheet
Dim rngToSort As Range
Dim lngRow As Long

Set wksOutput = ActiveSheet
'Header row
lngRow = 1
wksOutput.Cells(lngRow, 1) = "SenderName"
wksOutput.Cells(lngRow, 2) = "Subject"
wksOutput.Cells(lngRow, 3) = "ConversationTopic"
wksOutput.Cells(lngRow, 4) = "ConversationIndex"
wksOutput.Cells(lngRow, 5) = "To"
wksOutput.Cells(lngRow, 6) = "SentOn"
lngRow = 2

'get the outlook stuff
Set objOutlook = GetObject(, "Outlook.Application")
'This would be a public folder
Set objFolder = objOutlook.Session.Folders("Finance")
'This is a sub-folder in the public folder
Set objTarget = objFolder.Folders("Agency HR")
For Each objItem In objTarget.Items
  If objItem.Class = 43 Then 'olMail
    With objItem
      wksOutput.Cells(lngRow, 1) = .SenderName
      wksOutput.Cells(lngRow, 2) = .Subject
      wksOutput.Cells(lngRow, 3) = .ConversationTopic
      wksOutput.Cells(lngRow, 4) = GUIDToString(.ConversationIndex)
      wksOutput.Cells(lngRow, 5) = .To
      wksOutput.Cells(lngRow, 6) = .SentOn
    End With
    lngRow = lngRow + 1
  End If
Next objItem

'Sort the stuff in Excel
wksOutput.Range("A1:F" & lngRow).Sort Range("B2"), xlAscending, Range("C2"), , xlAscending, , , xlYes
Clean_Up:
Set wksOutput = Nothing
Set objItem = Nothing
Set objTarget = Nothing
Set objFolder = Nothing
Set objOutlook = Nothing
Exit Sub
GetOutlookItems_Error:
Debug.Print Err.Number, Err.Description
Resume Clean_Up
End Sub

Function GUIDToString(GUID As Variant) As String
Dim arrByte() As Byte
Dim intOrdinal As Integer
arrByte = GUID
For intOrdinal = 0 To UBound(arrByte)
  GUIDToString = GUIDToString & Hex$(arrByte(intOrdinal))
Next intOrdinal
End Function

Now the fun part will be how to do the reporting you want from this data.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Codman]

OK, I entered the code in an Excel module and although it creates a column label and the code seems to loop, it doesn't give any data. Did you anticipate me putting the code in Outlook? If so, I get an error message on the line statring Dim wksOutput As Worksheet.

Cod Man

 

[CautionMP]

Codman,
Excel is correct. I forgot to mention that you will need to change a couple things in the code before it will work.
The [tt]"Finance"[/tt] and [tt]"Agency HR"[/tt] need to be updated to match your actual folder names.

'This would be a public folder
Set objFolder = objOutlook.Session.Folders("Finance")
'This is a sub-folder in the public folder
Set objTarget = objFolder.Folders("Agency HR")

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Codman]

Hi CMP,

I tried changing the names to: -

a) Public folder names. Ours starts "All Public Folders") with sub folders as "Quotes"

b) Changed the name to "Inbox" and then "France" as folders in my mail box.

If I step through the code as in a) the code jumps to the debug.print line after it tries to execute Set objTarget = objFolder.Folders("Web Enquiries"). This is a folder in the public folders path and I've checked the spelling etc. Can you offer any guide to the problem. I get the header names OK, but no underlying data.

I'm running Office 2003 by the way

CodMan

 

[CautionMP]

Codman,
So your structure looks like this?
[tt] -Outlook Today
?Inbox
?France
-All Public Folders
?Quotes
?Web Enquiries[/tt]

If so, this should step to the right folder.

...
'Grab the [i]All Public Folders[/i] folder **See note below
Set objFolder = objOutlook.Session.Folders("All Public Folders")
'Now we need to move in on level to the [i]Quotes[/i] folder
Set objFolder = objFolder.Folders("Quotes")
'This is a sub-sub-folder in the public folder
Set objTarget = objFolder.Folders("Web Enquiries")
...

**Note: You can get to the Quotes folder directly in one line by doing this:
[tt]Set objFolder = objOutlook.Session.Folders("All Public Folders").Folders("Quotes")[/tt]


Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Codman]

Hi CMP

Still have problems I'm afraid I've included your code and additions as below. When I step through to the lines starting " Set objFolder =......." the code skips to the debug.print line and I get the error message in the immeadiate window of "-2147221233 The operation failed. An object could not be found."

It's not finding the outlook object for some reason. As far as the folder structure goes, you're correct it looks like: -

MailBox

-In Box
-France

and...

Public Folders

-All Public Folders
-Web Enquiries

Any help greatly appreciated

CodMan

'get the outlook stuff
Set objOutlook = GetObject(, "Outlook.Application")
'This would be a public folder
Set objFolder = objOutlook.Session.Folders("All Public Folders").Folders("Quotes")
For Each objItem In objTarget.Items
  If objItem.Class = 43 Then 'olMail
    With objItem
      wksOutput.Cells(lngRow, 1) = .SenderName
      wksOutput.Cells(lngRow, 2) = .Subject
      wksOutput.Cells(lngRow, 3) = .ConversationTopic
      wksOutput.Cells(lngRow, 4) = GUIDToString(.ConversationIndex)
      wksOutput.Cells(lngRow, 5) = .To
      wksOutput.Cells(lngRow, 6) = .SentOn
    End With
    lngRow = lngRow + 1
  End If
Next objItem

 

[CautionMP]

Codman,
It's been a while since I have worked with Public Folders and I no longer work in an environment that uses them so it's hard for me to test. Here is one last thought after looking at MSDN for a while.

Set objFolder = objOutlook.Session.Folders("Public Folders").Folders("All Public Folders").Folders("Quotes")

I don't know how familiar you are with the Locals window (how I usually find this stuff in Outlook) so here is a function that might help. It runs from any office application (I'm using Excel 2k SR-1). To use: open one of the messages in the "[tt]Quotes[/tt]" folder then play the macro. It should show in the Immediate window what the folder structure is according to Outlook.

Sub WhereAmI()
On Error Resume Next
Dim appOutlook as Object
Dim objInspector As Object
Dim objItem As Object
Dim intLevel As Integer
Set appOutlook = GetObject(, "Outlook.Application")
Set objInspector = ActiveInspector
Set objItem = objInspector.CurrentItem
Do
  Debug.Print "Parent" & intLevel & "(" & TypeName(objItem) & "): " & _
              objItem.Parent.Name
  intLevel = intLevel + 1
  Set objItem = objItem.Parent
Loop Until Err.Number <> 0
Set objItem = Nothing
Set objInspector = Nothing
Set appOutlook = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)