Solved

For Each loop in Outlook VBA

Posted on 2003-10-29
6
491 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now