Solved

For Each loop in Outlook VBA

Posted on 2003-10-29
6
500 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
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!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB 6 error 5 in windows 10 but not in XP 7 73
vbModal 12 75
MS SQL Update query with connected table data 3 81
Powerpoint 2013: Change cell reference in excel link 3 141
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

752 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