UtterAccess.com
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    
 
   
dflak
post Oct 30 2017, 11:38 AM
Post#1


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


The following code has two issues. Sometimes, I get an error message on this line.
CODE
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.

CODE
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
        DoEvents
    Next olAtt
    olMail.Move FldrOut
    DoEvents
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: 0
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
theDBguy
post Oct 30 2017, 11:46 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,037
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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
dflak
post Oct 30 2017, 11:56 AM
Post#3


Utter Access VIP
Posts: 6,011
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.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th November 2017 - 07:57 AM