outlook macro that parses subjects of all emails present in a folder

Hello all,
I need an help: my monitoring system (zabbix) sends to my email account a number of alerts (both problem and resolution) of my system.
What I would like to have is that the outlook  macro or rule can manage different email. I wish that an email with problem will be marked as unread until it doesn't receive the email confirmation of the problem resolution: for example I've in subject of a problem
Check Last Transcoded - PROBLEM
and I wish this remains unread until I receive the email message:
Check Last Transcoded - OK
then both PROBLEM and OK messages have to be marked as read and moved to the archive folder.

there's a way to do that?
the macro should be activated everytime a new email arrives in this folder.

Thank you very much
best regards
Nick
nschwendAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ltlbearand3Commented:
I have a few questions to determine how complicated or tricky we have to try to make this solution.

When the new messages arrive that you want to leave as unread - do you leave those as unread or do we have to try to manage keeping these marked as unread (this is difficult and may be impossible based on how you use outlook).

If you leave those as unread, then really we just need a solution to manage moving the matching emails.  At that point I suggest creating a rule that runs each time you receive an email from your system.  In the rule, have it run a script and then put this macro into a module in the Outlook VBA:

Public Sub ProcessingEmails(Item As Outlook.MailItem)
    Dim objFolder As Outlook.Folder
    Dim strSubject As String
    Dim objProbEmail As Outlook.MailItem
    
    If Right(Item.Subject, 2) = "OK" Then
        Set objFolder = Outlook.Session.Folders("YourArchiveNameHere")
        
        ' Find Matching Email
        strSubject = Left(Item.Subject, Len(Item.Subject - 2)) & "PROBLEM"
        
        Set objProbEmail = Item.Parent.Items.Find("[Subject] = " & strSubject)
        
        Item.Move objFolder
        objProbEmail.Move objFolder
        
    End If
End Sub

Open in new window

0
nschwendAuthor Commented:
Hi ltlbearand3
thank you very much for your reply!!
what I wish to know is, at any time, if I look at the folder of my monitoring system email, all the problem are still not solved. They could be neither read nor unread... due the fact I wish to move the solved problems on another folder.

but how can I implement the macro that will be called by the rule?

your solution can be applied on any kind of subject? because as you can imagine I've more than 50 checks that Zabbix does with their related alert.

thank you very much.
Nick
0
ltlbearand3Commented:
OK.  Lets back up a few steps and really define what is required.  

First off, lets deal with the read / unread nature. For the problem emails, are these required to stay in an unread status or can we not worry about that if we will automatically move them to a different folder once the OK email is received?  If they need to stay in an unread status, why and what events would cause them to be marked as read?

Next, lets deal with the incoming OK emails.  Is the from email always from the same email address?  If so, is this the same email address as the problem emails?  If it is the same email for both, is there any distinguishing text in the subject line of all OK email to distinguish them from the problem emails?  

These answer determine how to proceed.  As a side note, to set up a rule click on Rules from Home Ribbon, then click create rule and then click advanced options.  Then follow the prompts.  We will need to the script set up first, though.

The script can work on a variety of subject, but will be dependent upon some consistency such as all OK emails will have a similar format (though some words can change) and that the problem emails all have basically the same subject.  This is where we need more defined requirements to make this actually work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nschwendAuthor Commented:
Hi,
I try to answer your question as simpler as possible:
1st. I wish that the emails will be marked as read once the problem has been solved (so both the email with the problem and the email with the OK have been arrived).
2nd. they are coming from the same email address. normally the text is the following (as I wrote in the example above):
Problem subject:
"Check Last Transcoded - PROBLEM"
solution subject:
"Check Last Transcoded - OK"
As you can see the alert is in the first part of the subject, the situation (OK or Problem) at the end of the subject...
could be a small list of email subject:
"Queries per seconds"
"Number of audio transfers"
"No XML received"
"Disk I/O overloaded"
and many others...

hope it helps.
thank you a lot.
regards
Nick
0
ltlbearand3Commented:
OK.  Lets try this.  Copy This code into a macro in Outlook.

1.  Open the VBA Editor - The easiest way is to press ALT-F11
2.  on the menu click Insert >> Module
3.  Paste this code into the module window

Public Sub ProcessingzabbixEmails(Item As Outlook.MailItem)
    Dim objFolder As Outlook.Folder
    Dim strSubject As String
    Dim objProbEmail As Outlook.MailItem
    
    If Right(Item.Subject, 2) = "OK" Then
        Set objFolder = Outlook.Session.Folders("YourArchiveNameHere")
        
        ' Find Matching Email
        strSubject = Left(Item.Subject, Len(Item.Subject - 2)) & "PROBLEM"
        
        Set objProbEmail = Item.Parent.Items.Find("[Subject] = " & strSubject)
        objProbEmail.UnRead = False
                
        Item.Move objFolder
        objProbEmail.Move objFolder
        
        Item.UnRead = False
        Item.Move objFolder
    End If
End Sub

Open in new window


You will need to update the line of code that says "YourArchiveNameHere" with the name of your archive folder where you want the emails to be moved after the OK email is received.  

Now the next time you get an email from zabbix do the following to set up the rule:
On the Home Ribbon click Rules >> Create Rule
Select the top box "From  . . . "
Click Advanced Options
Click Next
Select the option in the list to "run a script"
Click on the link for "a script" and select the ProcessingZabbixEmails option

That should be all you need.  The script will then be run each time you get an email from zabbix.  It will look for the word OK at the end of the subject line (ex.  "Number of audio transfers - OK").  It will mark that email as read and move it to the archive folder.  It will also look for the associated problem email ex "Number of audio transfers - PROBLEM", mark it as read and move it to the archive folder.

Give it a try and let us know how it works.
0
nschwendAuthor Commented:
Hi,
I've update my outlook rule with the one you see in the printscreen
outlook rulebut I got this error:
error
what could it be?
thank you very much
Nick
0
nschwendAuthor Commented:
modified this line:
 
        ' Find Matching Email
        strSubject = Left(Item.Subject, (Len(Item.Subject) - 2)) & "PROBLEM"

Open in new window

0
nschwendAuthor Commented:
Hi,
after fixing the problem with the parenthesis, I got a problem with the folder. In my account structure I've the folder:
"Archived Production Alerts"
folderfolder2
but I got this problem:
error
This is the code:
code
thank you very much
Regards
Nick
0
ltlbearand3Commented:
Sorry about the parenthesis problem.  Glad you got that fixed.  Now for the folder problem.  It looks like this folder a sub folder of another folder.  In the properties box there should be a line for location (just below where you cut off the screen shot).  

So if we are drilling down to a subfolder, you need to use code like this:
    Set objF = Session.Folders("MainFolder").Folders("SubFolderName")

Open in new window



If the location shows something like "\\Archive"  Then an example that might work for you would be:
    Set objF = Session.Folders("Archive").Folders("Archived Production Alerts")

Open in new window

0
nschwendAuthor Commented:
Hi,
thank you for your reply.
As you can see in the printscreen
foldersthis is my structure. My Mailbox Name is my email address and then the folders are just below. In the
"Current_Production_Alerts" should be populated by current problems
"Archived_Production_Alerts" should be populated with old (and solved) problems.

I guess the should be:
Set objF = Session.Folders("myemailaddress").Folders("Archived_Production_Alerts")
correct?
thank you again
Nick
0
nschwendAuthor Commented:
HI,
now is quite weird, because seems that the script is no more called... I tried putting a msgbox, restoring the error with the parenthesis but nothing happens.
... wtf... no idea why... if I put as action to do a sound works correctly. when I put to run the script nope...
0
nschwendAuthor Commented:
Now seems to work if I keep the open issue in the Inbox.
There's a way to move the emails with problem in a dedicated folder (Current_Production_Alerts) and then move away from there once archived?
I did with the moving option in the rules but works correctly the for the problem. but once I got the OK the scripts checks in the Inbox and I got an error...


thank you very much.
Nick
0
nschwendAuthor Commented:
... second issue:
I've got some subject with special characters:
"Number of active audio transfers is below 6! - OK"
while processing these subject I got a problem on the line

Set objProbEmail = Item.Parent.Items.Find("[Subject] = " & strSubject)
(I've deleted the popup and I don't remember the exact message).

thank you again
Nick
0
ltlbearand3Commented:
Ok.  Let me see if I can get all the issues here.

First off, this code will not run properly if you move the file first as it does not know where to find the email.  We can make the move part of the code and that should address the problem of the code not runnings.

Second, yes the line to set the folder could be something like this:
Set objFolder = Outlook.Session.Folders("myemailaddress@mydomain.com").Folders("Archived_Production_Alerts")

Open in new window


Now since you are using a folder off the main email account and we need to use two different folders, I would change the code around some to get our folders (see below).

Concerning the special characters.  I should have coded for that - sorry.  I have updated the code to handle that part.  Give this new code a try.  Update your rule to not move the file to production as this code will do that for you:

Public Sub ProcessingzabbixEmails(Item As Outlook.MailItem)
    Dim objParent As Outlook.Folder
    Dim objFolder As Outlook.Folder
    Dim objProductionFldr As Outlook.Folder
    Dim strSubject As String
    Dim objProbEmail As Outlook.MailItem
    
    Set objParent = Outlook.Session.GetDefaultFolder(olFolderInbox).Parent
    
    Set objProductionFldr = objParent.Folders("Current_Production_Alerts")
    
    If Right(Item.Subject, 2) = "OK" Then
        Set objFolder = objParent.Folders("Archived_Production_Alerts")
        
        ' Find Matching Email
        strSubject = Left(Item.Subject, Len(Item.Subject) - 2) & "PROBLEM"
        
        Set objProbEmail = objProductionFldr.Items.Find("[Subject] = '" & Replace(strSubject, "'", "''") & "'")
        objProbEmail.UnRead = False
                
        Item.Move objFolder
        objProbEmail.Move objFolder
        
        Item.UnRead = False
        Item.Move objFolder
    Else
        Item.UnRead = True
        Item.Move objProductionFldr
    End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nschwendAuthor Commented:
THANK YOU for your support.
this work like a charm.
thank you very much for all your time!
really really appreciate
Kind regards
Nick
0
nschwendAuthor Commented:
Hi ltlbearand3,
first of all thank you very much for your answer... everything works fine except when I have special characters in the subject like this one:

"OMNEON2 NFS Free space on rsis-cp-haproxy1 /mnt/fs2< 10% - PROBLEM" and
"OMNEON2 NFS Free space on rsis-cp-haproxy1 /mnt/fs2< 10% - OK"

there's a way to skip all special characters or to replace them?
thank you again
regards
Nick
0
ltlbearand3Commented:
I just ran a test and the code found that subject just fine on my machine.  What makes you think it is not working?

I suggest adding in
Debug.Print Replace(strSubject, "'", "''")

Open in new window

right after
strSubject = Left(Item.Subject, Len(Item.Subject) - 2) & "PROBLEM"

Open in new window

to verify what subject it is trying to find.  Also put
Debug.Print objProbEmail.Subject

Open in new window

right after
Set objProbEmail = objProductionFldr.Items.Find("[Subject] = '" & Replace(strSubject, "'", "''") & "'")

Open in new window

to see what it found.
0
nschwendAuthor Commented:
Hi,
since I put the debug.print I don't have any more issue... I don't know why...
thank you again.
best regards
Nick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.