script to pull email information onto excel

[Skopweb]

Hello
Can anyone help me with a script that can pull the outlook email information of a group mailbox on an excel sheet. Also is there a possibility to know the date & time the mail was first read and show in excel.
I need this to check if the mails that enter into the mail box is opened and read within a specified time.



Regards
skopweb

[Benzadeus]

"pull the outlook email information of a group mailbox"

The only information you want is when it was read the first time?

What are the rules of this group mailbox? Is it in a specified folder?

[Skopweb]

hello benzadeus
The only information you want is when it was read the first time?
yes: the date and time . also the time the mail was received

What are the rules of this group mailbox? Is it in a specified folder?
no rules are applicable it is attended from the main inbox folder of the group mailbox.

[Benzadeus]

Well, the rules I created here are

1 - All mails will be listed on spreadsheet;
2 - If mail is marked as read, display when it were read.

Is this it?

*Run with Outlook opened.
Option Explicit
Dim n As Long
Sub Launch_Pad()

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)

n = 0
Cells.ClearContents

Call ProcessFolder(olFolder)

Set olNS = Nothing
Set olFolder = Nothing
Set olApp = Nothing
Set olNS = Nothing
End Sub


Sub ProcessFolder(olfdStart As Outlook.MAPIFolder)

Dim olFolder As Outlook.MAPIFolder
Dim olObject As Object
Dim olMail As Outlook.MailItem

For Each olObject In olfdStart.Items
If TypeName(olObject) = "MailItem" Then
n = n + 1
Set olMail = olObject
Cells(n, 1) = olMail.Subject
If Not olMail.UnRead Then
Cells(n, 2) = olMail.ReceivedTime
Else
Cells(n, 2) = "Message is unread"
End If
End If
Next

'If you don't want to include subfolder, remove this routine (recursion)
For Each olFolder In olfdStart.Folders
Call ProcessFolder(olFolder)
Next
'***

Set olMail = Nothing
Set olFolder = Nothing
Set olObject = Nothing
End Sub

[Skopweb]

where should this code be entered
in excel workbook or outlook

[Benzadeus]

You should put the code in the Excel Application.

[Skopweb]

Hi
After entering the code in excel. keeping outlook open i run the macros it gives me the error
Compile Error : defined type not identified
..
The cursor highlights on
Sub ProcessFolder(olfdStart As Outlook.MAPIFolder)

[Benzadeus]

In the VBE, go in menu Tools, References.

Search for Microsoft Outlook 11.0 (or 12.0) Object Library, check the box, and then OK.

[Skopweb]

thanks its without errors
but how to redirect this to another inbox in outlook (my group mailbox)
it seems its taking info only from a selected inbox folder...
also the time that reflects does it show the time the mail was read first or something else..

regards
skopweb

[Benzadeus]

Well, change
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)


for
Set olFolder = olNS.PickFolder


or, if you don't want the pop-up, you can use directly
Set olFolder = olNS.Folders(a).Folders(b)

where a and b could be determined with reading the output of, for example:
Sub Determine_Folder()

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder

Dim a As Long
Dim b As Long
Dim i As Long

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")

Cells.ClearContents

For a = 1 To olNS.Folders.Count
For b = 1 To olNS.Folders(a).Folders.Count
i = i + 1
Cells(i, 1) = a
Cells(i, 2) = b
Cells(i, 3) = olNS.Folders(a).Folders(b)
Next b
Next a

Set olNS = Nothing
Set olApp = Nothing
Set olNS = Nothing
End Sub

[Benzadeus]

also the time that reflects does it show the time the mail was read first or something else..

I can't help you with this problem... maybe you could post this question with the code I did for you in Oulook Forum here...

[Skopweb]

Anyways thanks a ton for helping me. I'm not sure if i will get my answer for the first modified time.
However, incase if you come across the answer do let me know
..
Regards
Skopweb