Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

exporting data from Outlook to Excel

Status
Not open for further replies.
Codman

Codman

Technical User
Nov 25, 2003
44
GB
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
 
Sort by date Sort by votes
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)
 
Upvote 0 Downvote
Check out OutlookSpy. By far the best tool for disecting the Outlook Object Model (IMHO).

HTH

-----------
Regards,
Zack Barresse
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
Code: 
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)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
Code: 
'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)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
Code: 
...
'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)
 
Upvote 0 Downvote
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


Code: 
'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

 
Upvote 0 Downvote
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.
Code: 
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.
Code: 
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)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
590
remeng
remeng
Andrzejek
  • Locked
  • Question
Outlook Template replace text 2
Replies
13
Views
1K
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Retain signature in Outlook
Replies
17
Views
583
strongm
strongm
Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
464
hermanlaksko
hermanlaksko
Andrzejek
  • Question
Copy data between SQL and Oracle
Replies
4
Views
69
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top