Excel vba macro to read Public folder in Outlook

[steve62]

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

 

[Diane Poremsky [MVP]]

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 by email:
mailto:[email protected]

EMO - a weekly newsletter about Outlook and Exchange:
mailto:[email protected]

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

 

[Sue Mosher [MVP]]

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/community/en-us/default.mspx?dg=microsoft.public.outlook.program_vba

Also, if you do post there, please note any errors and which code statement
raises them.

 

[steve62]

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:
mailto:[email protected]

EMO - a weekly newsletter about Outlook and Exchange:
mailto:[email protected]

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

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.

 

[steve62]

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 usedthe
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 stringhttp://www.outlookcode.com/codedetail.aspx?id=492- searches for a folder by
namehttp://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 athttp://www.microsoft.com/office/community/en-us/default.mspx?dg=micro...

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








- Show quoted text -

Sue,

Thank you SO much for your reply. I really am not a VB programmer at
all. But I do have this need to automate this function, and VB looks
like the correct tool. In a past version I had the exact same code,
except the whole folder name was explicitly spelled out in the
strPublicFolder. I looked at the examples you suggested, and frankly,
because of my total lack of VB experience, i do NOt understand them.
Could you spell it out for me in a very simple way. Please.