Daniel Hausdorf
asked on
adding Filter with reference to Subject Field not working
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:
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
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
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
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.
ASKER
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
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
ASKER
Thanks for that :)
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
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.
ASKER
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers !
Found the missing bracket aswell.
I will test this and let you know.
Many many thanks for your help ! Much appreciated
Cheer
daniel
Found the missing bracket aswell.
I will test this and let you know.
Many many thanks for your help ! Much appreciated
Cheer
daniel
ASKER
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
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
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.
ASKER
Once again, many thanks !
ASKER
Excellent !
Quick and fast !
Quick and fast !