Solved

For Each loop in Outlook VBA

Posted on 2003-10-29
6
499 Views
Last Modified: 2008-02-20
Hi,

I wrote the code below that is not completing the loop for each message in the Public Folder I have pointed it to. However if I remove the 'Request.Move DestFolder' it works fine, but does not move the items.

Can you tell me where I went wrong?

Also, I wanted this script to run as soon as someone placed an item in the Public Folder.. is that possible?? I apologize if this are simple questions, but I am quite new to VBA.

Thanks!
Kristy Self

Public Sub SpamMail()

Dim myNS As NameSpace
Set myNS = GetNamespace("MAPI")

Dim DestFolder As MAPIFolder
Set DestFolder = myNS.Folders("Public Folders").Folders("All Public Folders").Folders("SPAM Block Requests").Folders("For NT Team Review")

Dim outgoing As MailItem

Dim Request As MailItem

Dim msg As MAPIFolder
Set msg = myNS.Folders("Public Folders").Folders("All Public Folders").Folders("SPAM Block Requests")

For Each Request In myNS.Folders("Public Folders").Folders("All Public Folders").Folders("SPAM Block Requests").Items

    Set outgoing = Application.CreateItem(olMailItem)
        outgoing.Subject = "SPAM Block Request via Public Folder"
        outgoing.To = "email@email.com"
        outgoing.Body = "Dear Help Desk," & vbCrLf & vbCrLf & "A client has placed a request to block SPAM in the SPAM Block Requests Public Folder. Attach this email to a new ticket and follow the instructions below." & vbCrLf & vbCrLf & "Create a ticket using the following Remedy Category, Type, Item and Assignment group." & vbCrLf & vbCrLf & "Requester: CALPINE CUSTOMER" & vbCrLf & vbCrLf & "Category: NT SERVERS SOFTWARE" & vbCrLf & "Type: EMAIL" & vbCrLf & "Item: SPAM" & vbCrLf & vbCrLf & "Assignment Group: CALS_ENTERPRISE_USER_ADMIN" & vbCrLf & vbCrLf & "Additional Information for NT Team to use" & vbCrLf & "______________________________________________" & vbCrLf & "Email Sender: " & Request.To & vbCrLf & "Email Date: " & Request.SentOn & vbCrLf & "Email Subject: " & Request.Subject
        outgoing.SentOnBehalfOfName = "email@email.com"
        outgoing.Send
        Request.Move DestFolder

Next Request


End Sub
0
Comment
Question by:KristyS96
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 3

Accepted Solution

by:
bacon7181 earned 125 total points
ID: 9645178
When you perform the Request.Move DestFolder it removes the item from its current location which invalidates the iterator.

You can not modify a collection while iterating through it.

Try using a reverse loop counter.  This way you can safely remove items from the list as you iterate over each item.

For Counter = TotalItems To 1 Step -1
   ...
Next

0
 

Author Comment

by:KristyS96
ID: 9645365
Okay.. makes enough sense, but could you help me implement it into this code?

Sorry to be such a pain..
0
 
LVL 3

Expert Comment

by:bacon7181
ID: 9645568
Not a problem at all.

Please forgive me if I use an incorrect variable or two, I cant test this right now.


dim loopCounter as integer
dim itemCount as integer

' We want the number of items in the folder, I think the following is correct
itemCount = myNS.Folders("Public Folders").Folders("All Public Folders").Folders("SPAM Block Requests").Items.Count

' Loop starting at the last item and ending at the first item
For loopCounter = itemCount to 1 Step -1

    ' Now we want the actual item we are working with
    Request = myNS.Folders("Public Folders").Folders("All Public Folders").Folders("SPAM Block Requests").Items(loopCounter)

    Set outgoing = Application.CreateItem(olMailItem)
        outgoing.Subject = "SPAM Block Request via Public Folder"
        outgoing.To = "email@email.com"
        outgoing.Body = "Dear Help Desk," & vbCrLf & vbCrLf & "A client has placed a request to block SPAM in the SPAM Block Requests Public Folder. Attach this email to a new ticket and follow the instructions below." & vbCrLf & vbCrLf & "Create a ticket using the following Remedy Category, Type, Item and Assignment group." & vbCrLf & vbCrLf & "Requester: CALPINE CUSTOMER" & vbCrLf & vbCrLf & "Category: NT SERVERS SOFTWARE" & vbCrLf & "Type: EMAIL" & vbCrLf & "Item: SPAM" & vbCrLf & vbCrLf & "Assignment Group: CALS_ENTERPRISE_USER_ADMIN" & vbCrLf & vbCrLf & "Additional Information for NT Team to use" & vbCrLf & "______________________________________________" & vbCrLf & "Email Sender: " & Request.To & vbCrLf & "Email Date: " & Request.SentOn & vbCrLf & "Email Subject: " & Request.Subject
        outgoing.SentOnBehalfOfName = "email@email.com"
        outgoing.Send
        Request.Move DestFolder

Next loopCounter



I hope that gets you going.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:KristyS96
ID: 9645654
Actually... nevermind.. I got it on my own.. thanks for the answer!!

However, I still would like to be able to have this script run when someone places an email in that folder... any suggestions for that?

0
 
LVL 3

Expert Comment

by:bacon7181
ID: 9645714
The Outlook Application object offers the following event:

NewMail    - Occurs when new mail arrives, even if a Rules Wizard rule moves the message out of the inbox.  Does not fire for each new message but once per new download of new messages.

So if you add an Application_NewMail() function handler to your ThisOutlookSession you could fire your macro whenever new mail arrives.


I dont know if that will work for items in a public folder though.
0
 

Author Comment

by:KristyS96
ID: 9646139
I tried that and it's not exactly working, but I think Public Folders are handled like Posts instead of something like New_Mail(). I'm not sure if there is a way to associate this script with that event?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question