Excel vba macro to read Public folder in Outlook

  • Thread starter Thread starter steve62
  • Start date Start date
S

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("A1:D1").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:D").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False

End Sub
 
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.
 
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...
Click to expand...
Sub ListAllItemsInInbox()
Click to expand...
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("A1:D1").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:D").AutoFit
   Range("A2").Select
   ActiveWindow.FreezePanes = True
   ActiveWorkbook.Saved = True
   Application.StatusBar = False
Click to expand...
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you SO much for your reply.....In a previous version I DID have
the exact folder path. But that did not work either.
 
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 -
Click to expand...

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.
 
Back
Top