none
Automatically Send an email to a user if an email from a user has not been received by a specified time. RRS feed

  • Question

  • I have to compile data for a report and am tired of having to send emails to remind people to send me their data.  I would like a way to send a reminder email to someone if I don't receive an email from them with specific text in the subject.  The reminder email should start to be sent on a Wednesday and continue through Friday being sent at a certain time each of those days. Once the expected email is received the macro should stop.  There should also be a way to easily stop the macro form executing for any given span of time (like for vacation and such).

    Thanks in advance!

    Regards,

    -Heinz Gruber

    Thursday, September 22, 2016 1:54 PM

Answers

  • Hi,

    I suggest you create appointment or task to run the macro at specific time. 

    E.g. 

    Private Sub Application_Reminder(ByVal Item As Object)
    If Item.Subject = "testReminder" Then
    Call setReminder
    End If
    End Sub

    To detect if there is email with specific subject from a user, please try code below.

    I am referring to Search the Inbox for Items with Subject Containing "Office"

    Sub setReminder()
        Dim oT As Outlook.Table
        Dim oRow As Outlook.Row
        Dim Filter As String
    
        Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
        Filter = "[From] = 'celeste'"      
        Set oT = Application.Session.GetDefaultFolder(olFolderInbox).GetTable(Filter)
        If oT.EndOfTable = True Then
        Call sendEmail
        End If
    
        Do Until oT.EndOfTable
            Set oRow = oT.GetNextRow
            If InStr(1, oRow("Subject"), "testSubject") = 0 Then
            i = i + 1
            End If
        Loop
        
        If i > 1 Then
        Call sendEmail
        End If
    End Sub
    
    Sub sendEmail()
    'Create an object of CDO type
    Set testMail = CreateObject("CDO.Message")
    'Enable SSL Authentication
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    'Enable basic smtp authentication
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    'Specify SMTP server and port
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    'Specify user id and password
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxx@xx"
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxx"
    'Update the configuration fields
    testMail.Configuration.Fields.Update
    
     'Specify email properties
             testMail.Subject = "test"
             testMail.From = "xxx"
             testMail.To = "xx"
             testMail.CC = ""
             testMail.BCC = ""
             testMail.TextBody = "This is the Text Body"
    'Send mail
    testMail.Send
    Set testMail = Nothing
    End Sub
    


    Sunday, September 25, 2016 12:24 PM
    Moderator
  • Hello Heinz,

    You can develop a VBA macro or COM add-in to implement the required functionality. Following both ways you need to create a timer which can fire events with a specified period of time. In the Tick's event handler you can compile the report and send it to users.

    The NewMailEx event of the Application class is fired when a new item is received in the Inbox. This event fires once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem, MeetingItem, or SharingItem. The EntryIDsCollection string contains the Entry ID that corresponds to that item. 

    The NewMailEx event fires when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. You should not assume that after these events fire, you will always get a one-item increase in the number of items in the Inbox.

    So, basically, you need a flag which reflects the state - is the code for sending reports is enabled or not. In the Timer's Tick event you may check it each time before preparing and sending reports.

    Finally, you may find the Getting Started with VBA in Outlook 2010 article helpful. Also take a look at the Outlook VBA - Run a code every half an hour page.


    [custom.development]

    Monday, September 26, 2016 9:27 AM

All replies

  • Hi,

    What I am to suggest will achieve the end-goal (Remind user at specific time of day), but no auto-stop feature:

    https://support.office.com/en-us/article/Flag-an-item-for-follow-up-7b0f0c0e-21a6-41f0-96de-2cbda41e7eb1

    Hope this helps !

    Cheers,

    Chirag 

    Thursday, September 22, 2016 2:56 PM
  • Hi,

    I suggest you create appointment or task to run the macro at specific time. 

    E.g. 

    Private Sub Application_Reminder(ByVal Item As Object)
    If Item.Subject = "testReminder" Then
    Call setReminder
    End If
    End Sub

    To detect if there is email with specific subject from a user, please try code below.

    I am referring to Search the Inbox for Items with Subject Containing "Office"

    Sub setReminder()
        Dim oT As Outlook.Table
        Dim oRow As Outlook.Row
        Dim Filter As String
    
        Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
        Filter = "[From] = 'celeste'"      
        Set oT = Application.Session.GetDefaultFolder(olFolderInbox).GetTable(Filter)
        If oT.EndOfTable = True Then
        Call sendEmail
        End If
    
        Do Until oT.EndOfTable
            Set oRow = oT.GetNextRow
            If InStr(1, oRow("Subject"), "testSubject") = 0 Then
            i = i + 1
            End If
        Loop
        
        If i > 1 Then
        Call sendEmail
        End If
    End Sub
    
    Sub sendEmail()
    'Create an object of CDO type
    Set testMail = CreateObject("CDO.Message")
    'Enable SSL Authentication
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    'Enable basic smtp authentication
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    'Specify SMTP server and port
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    'Specify user id and password
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxx@xx"
    testMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxx"
    'Update the configuration fields
    testMail.Configuration.Fields.Update
    
     'Specify email properties
             testMail.Subject = "test"
             testMail.From = "xxx"
             testMail.To = "xx"
             testMail.CC = ""
             testMail.BCC = ""
             testMail.TextBody = "This is the Text Body"
    'Send mail
    testMail.Send
    Set testMail = Nothing
    End Sub
    


    Sunday, September 25, 2016 12:24 PM
    Moderator
  • Hello Heinz,

    You can develop a VBA macro or COM add-in to implement the required functionality. Following both ways you need to create a timer which can fire events with a specified period of time. In the Tick's event handler you can compile the report and send it to users.

    The NewMailEx event of the Application class is fired when a new item is received in the Inbox. This event fires once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem, MeetingItem, or SharingItem. The EntryIDsCollection string contains the Entry ID that corresponds to that item. 

    The NewMailEx event fires when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. You should not assume that after these events fire, you will always get a one-item increase in the number of items in the Inbox.

    So, basically, you need a flag which reflects the state - is the code for sending reports is enabled or not. In the Timer's Tick event you may check it each time before preparing and sending reports.

    Finally, you may find the Getting Started with VBA in Outlook 2010 article helpful. Also take a look at the Outlook VBA - Run a code every half an hour page.


    [custom.development]

    Monday, September 26, 2016 9:27 AM