Solved

For Each loop in Outlook VBA

Posted on 2003-10-29
6
496 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

832 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