Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

For Each loop in Outlook VBA

Posted on 2003-10-29
6
Medium Priority
?
504 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 500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…
Suggested Courses

926 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