I don't think this will be best to do this in Excel VBA as it would be easier to have a listener inside Outlook, have a look at the below, it sends a template each time a new mail pops into a shared inbox. Once the code has been pasted into the 'ThisOutlookSession' module of Outlook, shut down Outlook and then reopen (and enable macro's), at this point it should be ready to spy mails dropping into the Inbox of shared folder.
Private WithEvents inboxItems As Outlook.ItemsDim outlookApp As Outlook.Application
Private Sub Application_Startup()
Dim objectNS As Outlook.NameSpace
Set outlookApp = Outlook.Application
Set objectNS = outlookApp.GetNamespace("MAPI")
Set inboxItems = objectNS.Folders("Shared Folder Name").Folders("Inbox").Items
End Sub
Private Sub inboxItems_ItemAdd(ByVal Item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim MessageInfo
Dim Sender_ As String
Dim olmailtemp As Outlook.MailItem
If TypeName(Item) = "MailItem" Then
Set olmailtemp = outlookApp.CreateItemFromTemplate("Location Of Your Template\Test.oft")
With olmailtemp
.Display
.To = Item.SenderEmailAddress
.Subject = Item.Subject
'.Send
End With
End If
ExitNewItem:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ExitNewItem
End Sub