Consulting

Results 1 to 4 of 4

Thread: Email Template Fires Each Time You Receive a Fresh Email

  1. #1

    Email Template Fires Each Time You Receive a Fresh Email

    Hi Guys,

    I have put together some basic code. I am looking to create a macro so it fires an automated template reply (HTML Format) each time you receive a fresh email in a shared inbox.

    I have tried to do this using Outlook VBA under newmailex and newmail events - to little success. Also get entry id. I've now started to look at Excel vba and I am still running into problems.

    I would be eternally grateful if someone could please check and help.

    Thanks so much
    Attached Files Attached Files

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    Forever in your debt, This was bugging the heck out of me. Thank you so much for taking the time to respond, Georgiboy.

    Is it possible to get the template to launch under HTML format? I've added .HTMLBody property to the With olmailtemp block as follows:

    .HTMLBody = olmailtemp & .HTMLbody - but it still launches without the proper formatting?

    Many thanks
    Last edited by Mikeyabosbht; 05-21-2022 at 12:56 PM.

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    How about:
    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 oft file\Test.oft")
            With olmailtemp
                .Display
                .BodyFormat = olFormatHTML
                .To = Item.SenderEmailAddress
                .Subject = Item.Subject
                '.Send
            End With
        End If
        
    ExitNewItem:
        Exit Sub
    ErrorHandler:
        MsgBox Err.Number & " - " & Err.Description
        Resume ExitNewItem
    End Sub
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •