Automatic Response Controller


 

I’ve been getting more comments and requests on my Suggestions page. Here’s one from a poster who goes by the name mannagod7.

In microsoft’s support section for auto-replies @ http://office.microsoft.com/en-us/outlook-help/send-out-of-office-notices-with-pop3-imap-and-hotmail-accounts-HA010354932.aspx?CTT=1, they say, “The reply using a specific template rule in the Rules Wizard sends your automated reply only one time to each sender during a single session. A session is every time that you start Outlook until you exit the application. This rule prevents Outlook from sending repetitive replies to a single sender from whom you receive multiple messages. During a session, Outlook keeps track of the list of users to whom it has responded. If you exit and then restart Outlook, it is considered a new session and the list of the senders who have received automated replies is reset.”

Is there a way to stop the reset function from running on exit of Outlook, so that even when I do restart Outlook, someone who has already received my auto-reply will not receive another one.

In short, what mannagod7 would like is a solution that keeps track of who it’s responded to even between Outlook sessions. I don’t think there’s any setting that will prevent Outlook from resetting the list of who it’s replied to. As I explained to mannagod7, my best guess is that Outlook stores the list in memory which, of course, is wiped when Outlook closes. The only solution I can think of is to use code to emulate the “reply using a specific template” rule. Keep in mind that this solution only applies when Outlook is not being used with Exchange.

The solution I’ve devised uses a combination of an Access database, some code, and a rule. Here’s how it works. The rule identifies the response conditions (i.e. under what circumstances a response will be sent). When the rule is triggered it runs the code. The code checks the database to see if a response has already been sent to the author of the message that triggered the rule. If a response has already been sent to that sender, then processing halts. Otherwise, an automatic response is generated and sent back using a designated template. For this solution the template can be any message saved to the “Drafts” folder.

Once you begin using the solution you’ll notice a new folder named “Templates” created in your “Documents” or “My Documents” folder (depending on what version of Windows you’re using). The solution uses this folder to store the Access database it uses as well as temporary copies of the templates. The solution also creates the database.

You can edit a template at any time by opening the message in “Drafts” and making changes to it. When you change a template the code will wipe the corresponding response history from the database. For example, assume I have two response templates called “Response 1” and “Response 2”. Both templates are in use and responses have been sent using each one. If I edit the template “Response 1”, then the code will wipe out the response history for that template. This means that someone who had already gotten a response using that template will get another the next time they send me a message. If you change the subject line of a template while it’s in use, then you need to make a corresponding change to the code immediately. If you don’t, then the next time you launch Outlook you’ll get an error because the code will no longer be able to find the template.

One additional point. This solution only works when Outlook is running. It is not a replacement for the autoresponse mechanism available for mailboxes on an Exchange server. The Exchange autoresponse mechanism runs at the server, meaning that responses are sent even when Outlook is not running. This does not.

Finally, please be sure to test the code in your environment before putting it into production. I’ve done limited testing and there may be bugs in the code.

Requirements

  1. Outlook 2007 or 2010
  2. Access 2007 or 2010

Instructions

Follow these instructions to add the code to Outlook.

The Template

Create your response templates. Each template will be a message saved to the “Drafts” folder. A template should not include your signature as Outlook will insert your signature automatically when it creates a response message. If you include a signature, then there’ll be two of them in each response. Note that your template can include anything a normal message would including attachments. If you need multiple autoresponses (e.g. one for business messages and another for personal messages), then create a message for each. Keep in mind that the subject line for each response must be unique.

Code

The code comes in two parts.

Part 1

Follow these instructions to add part 1 of the code.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select InsertClass Module
  5. In the Properties panel click on Name and enter clsAutoResponse
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included a comment where changes are needed or allowed.
  8. Click the diskette icon on the toolbar to save the changes
Const DBNAME = "Outlook.accdb"

Private WithEvents olkTemplate As Outlook.MailItem

Private adoCon As Object, _
        adoRec As Object, _
        strTemplateName As String, _
        strTemplateFolder As String, _
        strEID As String
        
Private Sub Class_Initialize()
    Dim objFSO As Object, _
        objShell As Object
    Set objFSO = CreateObject("Scripting.FileSystemobject")
    Set objShell = CreateObject("WScript.Shell")
    strTemplateFolder = objShell.SpecialFolders("MyDocuments") & "\Templates\"
    If Not objFSO.FolderExists(strTemplateFolder) Then objFSO.CreateFolder strTemplateFolder
    If Not objFSO.FileExists(strTemplateFolder & DBNAME) Then CreateDatabase (strTemplateFolder & DBNAME)
    Set objFSO = Nothing
    Set objShell = Nothing
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strTemplateFolder & DBNAME & ";Persist Security Info=False;"
End Sub

Private Sub Class_Terminate()
    adoCon.Close
    Set adoCon = Nothing
End Sub

Public Sub Reply(olkMsg As Outlook.MailItem)
    Dim olkReply As Outlook.MailItem, strSQL As String
    Set adoRec = adoCon.Execute("SELECT resAddress FROM Responded WHERE resTemplate = '" & strTemplateName & "' AND resAddress = '" & olkMsg.SenderEmailAddress & "'")
    If (adoRec.BOF) Or (adoRec.EOF) Then
        Set olkReply = Application.CreateItemFromTemplate(strTemplateFolder & strTemplate & ".oft")
        With olkReply
            .Recipients.Add olkMsg.SenderEmailAddress
            .Recipients.ResolveAll
            .Send
        End With
        strSQL = "INSERT INTO Responded (resTemplate,resDate,resAddress) VALUES('" & strTemplateName & "','" & Date & "','" & olkMsg.SenderEmailAddress & "')"
        adoCon.Execute strSQL
    End If
    adoRec.Close
    Set adoRec = Nothing
End Sub

Public Sub Template(strName As String, strSubject As String)
    strTemplateName = strName
    strEID = GetItemByName(strSubject)
    SaveTemplateToFileSystem
End Sub

Sub CreateDatabase(strFilename As String)
    Const adKeyPrimary = 1
    Const adInteger = 3
    Const adDate = 7
    Const adWChar = 130
    Dim adoCat As Object, _
        adoTable As Object, _
        tblCollection As New Collection, _
        strConn As String
    Set adoCat = CreateObject("ADOX.Catalog")
    Set adoTable = CreateObject("ADOX.Table")
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename & ";"
    adoCat.Create strConn
    tblCollection.Add "Responded"
    With adoTable
        .Name = "Responded"
        .Columns.Append "ID", adInteger
        .ParentCatalog = adoCat
        .Columns("ID").Properties("AutoIncrement").Value = True
        .Keys.Append "PrimaryKey", adKeyPrimary, "ID"
        .Columns.Append "resDate", adDate
        .Columns.Append "resTemplate", adWChar
        .Columns.Append "resAddress", adWChar
    End With
    adoCat.Tables.Append adoTable
    Set adoTable = Nothing
    Set tblCollection = Nothing
    Set adoCat = Nothing
End Sub

Private Sub olkTemplate_Write(Cancel As Boolean)
    adoCon.Execute "DELETE * FROM Responded WHERE resTemplate = '" & strTemplateName & "'"
    SaveTemplateToFileSystem
End Sub

Private Sub SaveTemplateToFileSystem()
    Dim olkTemp As Outlook.MailItem
    Set olkTemp = Session.GetItemFromID(strEID)
    olkTemp.SaveAs strTemplateFolder & strTemplateName & ".oft", OlSaveAsType.olTemplate
    Set olkTemplate = Session.GetItemFromID(strEID)
    Set olkTemp = Nothing
End Sub

Private Function GetItemByName(strName As String) As String
    Dim olkTemp As Outlook.MailItem
    Set olkTemp = Session.GetDefaultFolder(olFolderDrafts).Items.Find("[Subject] = '" & strName & "'")
    If TypeName(olkTemp) <> "Nothing" Then
        GetItemByName = olkTemp.EntryID
    Else
        GetItemByName = ""
    End If
    Set olkTemp = Nothing
End Function

Part 2.

Follow these instructions to add part 2 of the code.

Outlook 2007.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click ToolsTrust Center
  6. Click Macro Security
  7. Set Macro Security to Warnings for all macros
  8. Click OK
  9. Close Outlook
  10. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.

Outlook 2010.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click File and select Options
  6. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  7. Click Macro Settings
  8. Select either of the two bottom settings (i.e. Notifications for all macros or Enable all macros (not recommended; potentially dangerous code can run). The choice of which to choose is up to you. If you select Notifications, then you’ll be prompted at times to enable macros. If you pick Enable all then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  9. Click Ok until the dialog-boxes have all closed
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
'Handles the first autoresponse condition.
Dim objAR1 As clsAutoResponse

'Handles a second autoresponse condition.  Remove the next line if you don't have a second condition.  You can create additional autoresponse conditions as needed by adding additional lines like this one.
Dim objAR2 As clsAutoResponse

Private Sub Application_Quit()
    'Remove unnecessary lines as needed.
    Set objAR1 = Nothing
    Set objAR2 = Nothing
End Sub

Private Sub Application_Startup()
    'Sets up the first autoresponse handler
    Set objAR1 = New clsAutoResponse
    With objAR1
        'On the next line replace "Test1" with the name of you template.  The name must be unique but can be anything you want it to be.  Replace "Test Template One" with the subject line of your template.  This will be the subject line of the message in the "Drafts" folder that you're using as a template.
        .Template "Test1", "Test Template One"
    End With

    'Sets up the second autoresponse handler.  If you don't have a second autoresponse, then you can delete the next 5 lines of code.
    Set objAR2 = New clsAutoResponse
    With objAR2
        'On the next line replace "Test2" with the name of you template.  The name must be unique but can be anything you want it to be.  Replace "Test Template Two" with the subject line of your template.  This will be the subject line of the message in the "Drafts" folder that you're using as a template.
        .Template "Test2", "Test Template Two"
    End With
End Sub

Sub AutoReply1(Item As Outlook.MailItem)
    objAR1.Reply Item
End Sub

'Delete the following subroutine if you don't have a second autoresponse.
Sub AutoReply2(Item As Outlook.MailItem)
    objAR2.Reply Item
End Sub

The Rule

Create a rule that fires for those messages you want to respond to. Set the rule’s action to “run a script” and select “AutoReplyx” (where “x” is the number of the response) as the script to run. For example, the first rule you create will run “AutoReply1”. If you create a second autoresponse, then it would run “AutoReply2”. Delete or disable a rule to stop the autoresponse process. As long as the rule exists and is enabled it will continue to send automatic responses.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s