X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Looping Through Messages In A Folder., Office 2013    
post Oct 30 2017, 11:38 AM

Utter Access VIP
Posts: 6,230
Joined: 22-June 04
From: North Carolina

The following code has two issues. Sometimes, I get an error message on this line.
Set FldrOut = olNS.Folders(MailBox).Folders("Inbox").Folders(InFolder).Folders(SubFolder)

And sometimes I don't. When I do I have to close both Excel and Outlook, start outlook and launch the spreadsheet again. That is problem 1.

Problem 2 is. if I comment out the useful stuff (downloading the attachment and moving the email) - the msgbox shows the subject for every message in the folder.

If I leave the useful stuff in, it processes half the messages and exits the Next olMail loop. So if I have 7 messages, it processes 4 of them and leaves 3. If I run it again, it processes two and leaves 1. The only time it clears the folder is when there is only one message back. I thought I knew how to loop.

Sub ProcessReport()
Dim olApp As Outlook.Application
Dim olNS As Namespace
Dim olMail As Variant
Dim FldrIn As Mapifolder
Dim FldrOut As Mapifolder
Dim olAtt As Outlook.Attachment
Dim olmi As MailItem

Dim MailBox As String           ' Labor Analysis mailbox
Dim InFolder As String          ' Dock Schedules folder
Dim SubFolder As String         ' Processed folder
Dim ExcelFolder As String       ' Folder to which to download attachments

' Initalize variables
MailBox = Range("MailBox")
InFolder = Range("In_Folder")
SubFolder = Range("Sub_Folder")
ExcelFolder = Range("Excel_Folder")

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set FldrIn = olNS.Folders(MailBox).Folders("Inbox").Folders(InFolder)
Set FldrOut = olNS.Folders(MailBox).Folders("Inbox").Folders(InFolder).Folders(SubFolder)

For Each olMail In FldrIn.Items
    MsgBox olMail.Subject
    For Each olAtt In olMail.Attachments
        olAtt.SaveAsFile ExcelFolder & "\" & olAtt.Filename
    Next olAtt
    olMail.Move FldrOut
Next olMail

Set FldrIn = Nothing
Set olNS = Nothing
Set olApp = Nothing

End Sub

Attached File(s)
Attached File  Dock_Schedule_Reader.zip ( 16.99K )Number of downloads: 5
Go to the top of the page
post Oct 30 2017, 11:46 AM

Access Wiki and Forums Moderator
Posts: 73,495
Joined: 19-June 07
From: SunnySandyEggo

Hi Dan,

On issue #2, since you are "moving" the email to a subfolder, you are effectively deleting them from the original location. When you loop through the items in a collection while deleting them at the same time, the item positions move as you delete an item. So, if you grab the first item and then delete it, the second item becomes the first item, and when you move to the next item in the loop, you are actually grabbing the original third item from the list.

The best way to loop through items you want to delete is by going backwards. Grab the last item first, delete it, then grab the previous item, which would still have the same location because deleting the last item won't make the other items move positions.

Hope it makes sense...
Go to the top of the page
post Oct 30 2017, 11:56 AM

Utter Access VIP
Posts: 6,230
Joined: 22-June 04
From: North Carolina

It makes absolute sense. It's analogous to removing rows from an excel sheet. It's not like looping through files in a windows folder.

Now I just have to figure out why I get that intermittent error message.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 11:36 PM