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

x
?
Solved

adding Filter with reference to Subject Field not working

Posted on 2016-11-08
12
Medium Priority
?
37 Views
Last Modified: 2016-11-09
Hi All,

I'm new to VBA scripting but i am making progress. Slowly and in baby steps but i'm getting there.

Currently we have a personal Inbox and a shared inbox. I have set up the following script which has a Pop Up (on the shared inbox) when a new email arrives. Now i want to filter these new Emails so that the pop up ONLY comes if in the Subject: <created with Priority P0> is found. I have tried and tried but unfortunately no success.

My current script:
Private WithEvents Items As Outlook.Items

Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder
Dim oFolder As Outlook.Folder
Dim FoldersArray As Variant
Dim i As Integer

On Error GoTo GetFolderPath_Error
If Left(FolderPath, 2) = "\\" Then
FolderPath = Right(FolderPath, Len(FolderPath) - 2)
End If
'Convert folderpath to array
FoldersArray = Split(FolderPath, "\")
Set oFolder = Application.Session.Folders.Item(FoldersArray(0))
If Not oFolder Is Nothing Then
For i = 1 To UBound(FoldersArray, 1)
Dim SubFolders As Outlook.Folders
Set SubFolders = oFolder.Folders
Set oFolder = SubFolders.Item(FoldersArray(i))
If oFolder Is Nothing Then
Set GetFolderPath = Nothing
End If
Next
End If
'Return the oFolder
Set GetFolderPath = oFolder
Exit Function

GetFolderPath_Error:
Set GetFolderPath = Nothing
Exit Function
End Function

Private Sub Application_Startup()
  Dim Ns As Outlook.NameSpace
  Set Ns = Application.GetNamespace("MAPI")
  Set Items = GetFolderPath("+support\Inbox").Items
End Sub
  
Private Sub Items_ItemAdd(ByVal Item As Object)
 On Error Resume Next
   MsgBox "new message  from " & Item.SenderName & " in shared folder"

End Sub

Open in new window

Ideally i would like the Pop Up to read :
New P0 has been opened. Please contact Customer ASAP

The Pop Up works perfectly, but currently this pops up everytime an email is received in the shared inbox.

Would there be a way to add the Ticket Nr to the MsgBox ?

Any help regarding this would be appreciated.

Many thanks
daniel
0
Comment
Question by:Daniel Hausdorf
  • 7
  • 4
11 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 41880008
Of course you can process and  extract the priority from the subject and display that. Same with Ticket Number. But to provide code, we need an example mail.
0
 

Author Comment

by:Daniel Hausdorf
ID: 41880013
Hi Qlemo,

many thanks for the response.

The Subject Field of an Incoming EMail looks like this:

Ticket INC00125742 created with Priority P0 Not Acknowledged

As said these come in to our Shared Mailbox.

Many thanks in advance.
Daniel
0
 

Author Comment

by:Daniel Hausdorf
ID: 41880015
Thanks for that :)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 71

Expert Comment

by:Qlemo
ID: 41880030
Private Sub Items_ItemAdd(ByVal Item As Object)
Dim ticket as String
Dim prio as String
  On Error Resume Next
  If Item.Subject like "Ticket * created with Priority *" Then
    ticket = MId(item.Subject, 7, 11)
    prio = Mid(item.Subject, len("Ticket INC00125742 created with Priority ", 2)
    MsgBox "New " & prio & " ticket " & ticket & " has been opened. Please contact Customer ASAP"
  End If
End Sub

Open in new window

This is not robust against subject line changes, and requires fixed positions and lengths for ticket no. and priority. More flexible approachs are more complex.
0
 

Author Comment

by:Daniel Hausdorf
ID: 41880036
Hi Qlemo,

you are FANTASTIC ! Many thanks for the quick response and reply.
Looks like i'm getting a 'Compile Error' at
Line 7 :  prio = Mid(item.Subject, len("Ticket INC00125742 created with Priority ", 2)

I will try to figure this out.
Do i add this script under the Application_Startup ?
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 41880041
My code snippet replaces the same SUB in your code (last 4 lines there). And I've left out a closing bracket (typing on tablet, so cannot test):
Private Sub Items_ItemAdd(ByVal Item As Object)
Dim ticket as String
Dim prio as String
  On Error Resume Next
  If Item.Subject like "Ticket * created with Priority *" Then
    ticket = MId(item.Subject, 7, 11)
    prio = Mid(item.Subject, len("Ticket INC00125742 created with Priority "), 2)
    MsgBox "New " & prio & " ticket " & ticket & " has been opened. Please contact Customer ASAP"
  End If
End Sub

Open in new window

0
 

Author Comment

by:Daniel Hausdorf
ID: 41880045
Cheers !

Found the missing bracket aswell.
I will test this and let you know.
Many many thanks for your help ! Much appreciated
Cheer
daniel
0
 

Author Comment

by:Daniel Hausdorf
ID: 41880058
Hi Olemo,

your script works perfectly !

Seeing that the Ticket Number will always have a new number, is there a way to have the ticket number as a variable in the script ?

Line7 : prio = Mid(item.Subject, len("Ticket INCXXXXXXXX created with Priority ")

Cheers
Daniel
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41880067
The ticket number is a variable already. The one in line 7 is just used as a template, to get the correct length of the overall string - you could use your X'd one, or put in something more obvious like INC12345678, or leave as-is.
0
 

Author Comment

by:Daniel Hausdorf
ID: 41880073
Once again, many thanks !
0
 

Author Closing Comment

by:Daniel Hausdorf
ID: 41880074
Excellent !
Quick and fast !
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

877 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