A Microsoft Outlook email forum. Outlook Banter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Outlook Banter forum » Microsoft Outlook Email Newsgroups » Outlook - General Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel vba macro to read Public folder in Outlook



 
 
Thread Tools Search this Thread Display Modes
  #1  
Old September 17th 09, 12:37 AM posted to microsoft.public.outlook
steve62
external usenet poster
 
Posts: 2
Default Excel vba macro to read Public folder in Outlook

Can anyone PLEASE tell me why this doesn't work? It DID work when I
went after my own inbox...

Sub ListAllItemsInInbox()


Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
'Workbooks.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Received"
Cells(1, 3).Formula = "Attachments"
Cells(1, 4).Formula = "Read"
With Range("A11").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
StrPublicFolder = "Public Folders\Favorites\Completed_Apps"
Set OLF = GetObject(" ", _
"Outlook.Application").GetNamespace
("MAPI").GetFolder(StrPublicFolder)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail
messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime,
"mm/dd/yyyy")
Cells(EmailCount + 1, 3).Formula = .Attachments.Count
Cells(EmailCount + 1, 4).Formula = Not .UnRead
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False

End Sub
Ads
  #2  
Old September 17th 09, 04:15 AM posted to microsoft.public.outlook
Diane Poremsky [MVP]
external usenet poster
 
Posts: 12,991
Default Excel vba macro to read Public folder in Outlook

instead of
Public Folders\Favorites\Completed_Apps

try the exact path to the folder
Public Folders\All Public Folders\where_ever\Completed_Apps

--
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com

Outlook Tips by email:


EMO - a weekly newsletter about Outlook and Exchange:


Do you keep Outlook open 24/7? Vote in our poll:
http://forums.slipstick.com/showthread.php?t=22205

"steve62" wrote in message
...
Can anyone PLEASE tell me why this doesn't work? It DID work when I
went after my own inbox...

Sub ListAllItemsInInbox()


Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
'Workbooks.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Received"
Cells(1, 3).Formula = "Attachments"
Cells(1, 4).Formula = "Read"
With Range("A11").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
StrPublicFolder = "Public Folders\Favorites\Completed_Apps"
Set OLF = GetObject(" ", _
"Outlook.Application").GetNamespace
("MAPI").GetFolder(StrPublicFolder)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail
messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime,
"mm/dd/yyyy")
Cells(EmailCount + 1, 3).Formula = .Attachments.Count
Cells(EmailCount + 1, 4).Formula = Not .UnRead
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False

End Sub


  #3  
Old September 17th 09, 03:39 PM posted to microsoft.public.outlook
Sue Mosher [MVP][_2_]
external usenet poster
 
Posts: 89
Default Excel vba macro to read Public folder in Outlook

If you look in the object browser, you'll see that there is no
Namespace.GetFolder method. To access your own inbox, you would have used the
GetDefaulFolder method. To get a non-default folder, you need to walk the
folder hierarchy using the Folders collections or use a function that does
that for you. For examples, see:

http://www.outlookcode.com/codedetail.aspx?id=628 - uses a folder path string
http://www.outlookcode.com/codedetail.aspx?id=492 - searches for a folder by
name
http://www.outlookcode.com/codedetail.aspx?id=1164 - uses a folder path
string in the Public Folders hierarchy

FYI, there is a newsgroup specifically for general Outlook programming
issues at
http://www.microsoft.com/office/comm....program_v ba

Also, if you do post there, please note any errors and which code statement
raises them.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

"steve62" wrote:

Can anyone PLEASE tell me why this doesn't work? It DID work when I
went after my own inbox...

Sub ListAllItemsInInbox()


Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
'Workbooks.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Received"
Cells(1, 3).Formula = "Attachments"
Cells(1, 4).Formula = "Read"
With Range("A11").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
StrPublicFolder = "Public Folders\Favorites\Completed_Apps"
Set OLF = GetObject(" ", _
"Outlook.Application").GetNamespace
("MAPI").GetFolder(StrPublicFolder)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail
messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime,
"mm/dd/yyyy")
Cells(EmailCount + 1, 3).Formula = .Attachments.Count
Cells(EmailCount + 1, 4).Formula = Not .UnRead
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False

End Sub

  #4  
Old September 17th 09, 04:32 PM posted to microsoft.public.outlook
steve62
external usenet poster
 
Posts: 2
Default Excel vba macro to read Public folder in Outlook

On Sep 16, 9:15*pm, "Diane Poremsky [MVP]" wrote:
instead of
Public Folders\Favorites\Completed_Apps

try the exact path to the folder
Public Folders\All Public Folders\where_ever\Completed_Apps

--
Diane Poremsky [MVP - Outlook]
Outlook Tips:http://www.outlook-tips.net/
Outlook & Exchange Solutions Center:http://www.slipstick.com

Outlook Tips by email:


EMO - a weekly newsletter about Outlook and Exchange:


Do you keep Outlook open 24/7? Vote in our poll:http://forums.slipstick.com/showthread.php?t=22205

"steve62" wrote in message

...



Can anyone PLEASE tell me why this doesn't work? *It DID work when I
went after my own inbox...


Sub ListAllItemsInInbox()


Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
* *Application.ScreenUpdating = False
* *'Workbooks.Add ' create a new workbook
* *' add headings
* *Cells(1, 1).Formula = "Subject"
* *Cells(1, 2).Formula = "Received"
* *Cells(1, 3).Formula = "Attachments"
* *Cells(1, 4).Formula = "Read"
* *With Range("A11").Font
* * * *.Bold = True
* * * *.Size = 14
* *End With
* *Application.Calculation = xlCalculationManual
* *StrPublicFolder = "Public Folders\Favorites\Completed_Apps"
* *Set OLF = GetObject(" ", _
* * * * * * * * * * * * "Outlook.Application").GetNamespace
("MAPI").GetFolder(StrPublicFolder)
* *EmailItemCount = OLF.Items.Count
* *i = 0: EmailCount = 0
* *' read e-mail information
* *While i EmailItemCount
* * * *i = i + 1
* * * *If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail
messages " & _
* * * * * *Format(i / EmailItemCount, "0%") & "..."
* * * *With OLF.Items(i)
* * * * * *EmailCount = EmailCount + 1
* * * * * *Cells(EmailCount + 1, 1).Formula = .Subject
* * * * * *Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime,
"mm/dd/yyyy")
* * * * * *Cells(EmailCount + 1, 3).Formula = .Attachments.Count
* * * * * *Cells(EmailCount + 1, 4).Formula = Not .UnRead
* * * *End With
* *Wend
* *Application.Calculation = xlCalculationAutomatic
* *Set OLF = Nothing
* *Columns("A").AutoFit
* *Range("A2").Select
* *ActiveWindow.FreezePanes = True
* *ActiveWorkbook.Saved = True
* *Application.StatusBar = False


End Sub- Hide quoted text -


- Show quoted text -


Thank you SO much for your reply.....In a previous version I DID have
the exact folder path. But that did not work either.
 




Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
export from Outlook 07 Public Folder calendar to Excel MiataMX5 Outlook - Calandaring 1 June 20th 08 04:41 AM
macro to update excel when email received into outlook folder amorrison2006@googlemail.com Outlook and VBA 1 July 17th 07 10:42 PM
Macro to extract subject and sender name of messages in folder to Excel file bony_tony Outlook and VBA 0 March 7th 07 02:16 AM
public folder contact read only wilecoyote Outlook - General Queries 0 January 17th 07 06:30 PM
Read public folder via IMAP aaronfude@gmail.com Outlook - General Queries 1 April 24th 06 10:44 AM


All times are GMT +1. The time now is 06:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.Search Engine Friendly URLs by vBSEO 2.4.0
Copyright ©2004-2025 Outlook Banter.
The comments are property of their posters.