Email Template Fires Each Time You Receive a Fresh Email

[georgiboy]

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