View Full Version : script to pull email information onto excel
Skopweb
08-28-2009, 05:56 AM
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
09-02-2009, 04:16 AM
"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
09-02-2009, 10:37 AM
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
09-02-2009, 01:22 PM
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
09-02-2009, 08:23 PM
where should this code be entered
in excel workbook or outlook
Benzadeus
09-03-2009, 02:24 AM
You should put the code in the Excel Application.
Skopweb
09-03-2009, 07:26 AM
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
09-03-2009, 07:30 AM
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
09-03-2009, 07:36 AM
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
09-03-2009, 08:08 AM
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
09-03-2009, 08:11 AM
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
09-03-2009, 10:04 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.