You can run a macro script from an outlook rule to process the messages as they arrive using worksheet data e.g.
Option Explicit
Sub DistributeMail(olItem As Outlook.MailItem)
Dim arr() As Variant
Dim iCols As Long
Dim sRefID As String
Dim sAgent As String
Dim sAgentID As String
arr = xlFillArray("C:\Path\Call Centre Admin.xlsx", "Sheet1")
For iCols = 0 To UBound(arr, 2) ' Second array dimension is columns.
sRefID = arr(0, iCols)
sAgentID = arr(1, iCols)
sAgent = arr(2, iCols)
If InStr(1, olItem.Subject, sRefID) > 0 Then
olItem.Move Session.GetDefaultFolder(olFolderInbox).folders(sAgent)
Exit For
End If
Next iCols
lbl_Exit:
Exit Sub
End Sub
Private Function xlFillArray(strWorkBook As String, _
strWorksheetName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long
strWorksheetName = strWorksheetName & "$]"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkBook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1
With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function
This assumes that your user folders are sub-folders of Inbox i.e.
olItem.Move Session.GetDefaultFolder(olFolderInbox).folders(sAgent)