Document Review Reminders


Last week I stumbled across this tweet from a gentleman named Andrew Hall

I need a data storage system that I can tag a doc with a review date which pops up when the file needs reviewing. Any ideas?

Someone had already replied to Andrew suggesting that he create an Outlook task for this and attach the document to be reviewed to it. While I agree with creating an Outlook task, I’m not as keen on attaching the document. There are pros and cons to doing that. One of the cons is that attaching the document to the task will likely create a separate instance of it. The only way to avoid this would be to attach the document and then delete the original. I think a better solution is to create a link to the document. That doesn’t take up space in your mailbox, something that could be a significant problem if your mailbox size is limited, and it doesn’t create an additional copy of the document, so there’s no worry about having to copy the document back to its original location when you’re done. What we need is a simple way of creating the task, one that doesn’t require opening Outlook, creating a task, filling in all the details, adding a link to the document, etc. Andrew would also like to have the review date entered into the document’s properties. To handle that requires the use of Dsofile, a Microsoft provided library (i.e. a DLL) for making changes to document properties. This Microsoft KB article has more information on what Dsofile is and how it works. It also contains the link for downloading and installing the library. If you’re really curious about Dsofile, then you can learn more about how to use it at this Microsoft TechNet page.

The solution I came up with is to use a script (VBScript) and Windows’ built-in Send to menu. On the odd chance that you aren’t familiar with the Send to menu, it’s the options you see when you right-click a file in Windows Explorer or the file dialog of any Microsoft application (e.g. Word, Excel) and select Send to from the resulting menu. The menu typically contains options like “Documents”, “Fax recipient”, and “Mail recipient”. What you may not know is that you can add menu selections simply by placing a link (i.e. a .lnk file) or properly written script in the Send to folder. For this solution we need a script that creates a task for each file selected when Send to is called.

The script itself is unremarkable. It connects to Outlook, takes the list of file names passed to it by Send to, and creates a document review task for each one. The script has options that enable you to set a default task subject, due date, and reminder time, as well as whether each task is displayed so you can make changes to it before saving or just saved. I also included an option for enabling/disabling setting a document property via Dsofile. That will allow those of you who aren’t allowed to download and install a DLL to at least use the Outlook portion of the script. It will also allow you to use the script if you just don’t want to use the document property portion of it. I included comments in the script describing each option setting.

Once the script is in place you can use it by selecting one or more files, right-clicking and selecting Send to, then selecting Document Review (or whatever name you gave the script). You’ll be prompted for a due date for each file you selected. If you’ve chosen to see the tasks before saving them, then a task for each file should appear on screen. If you chose to save the tasks without displaying them, then if you look in Outlook you should see your newly created tasks.

Please be sure and test this to make sure it works to your satisfaction before attempting to use it in a production setting. I tested the code before I posted it and I believe it’s working properly, but that does not mean it will work properly for you. Test it first.

Instructions.

  1. Open Notepad.
  2. Copy the code below and paste it into Notepad.
  3. Edit the code as needed. I included comments were things can/need to change.
  4. Save the file with a .vbs extension. I recommend naming it Document Review, but you can name it anything you want.
  5. Move/copy the file to your Send to folder. The simplest way to get to that folder is to click your Windows Start button, enter shell:sendto in the search field, and press Enter.
  6. An alternative to placing the script file in the Send to folder is to leave the script where it is (e.g. you’re My Documents folder), create a shortcut to it, and put the shortcut in the Send to folder. This has a couple of benefits. First, you can keep all your scripts (i.e. those that work with Send to and those that don’t) in one place. Second, a shortcut can have a unique icon whereas all VBScript files use the same icon. To create a shortcut, right-click the script and file and select Create shortcut. Now move/copy the shortcut (i.e. the .lnk file) to the Send to folder using the instructions in the previous step. To change the shorctu’s icon, right-click it and select Properties. When the properties dialog-box appears click the Change Icon button. Find and select another icon. You can also change the shortcut’s name (that’s on the General tab) as well as other properties as you see fit.
  7. If you plan to use the document property capability, then download and install Dsofile. If you are using Office 2007 or higher, then you’ll also need to ensure that Microsoft Office Compatibility Pack is installed. That’s covered in the Dsofile instructions (it’s in the References section of the page). Keep in mind that it is only possible to set document properties for MS Office files. Dsofile cannot set them for other file types. I don’t know if Dsofile will work with documents on a SharePoint site or not. I can’t test that as I don’t have a SharePoint installation to test with.
    1. 'On the next line edit the default task subject as desired.  The filename of the document will be appended to this text.
      Const DEF_SUBJECT = "Review Document: "
      'On the next line edit the number of days in the future the due date will default to.
      Const DEF_DUEDATE = 7
      'On the next line set the value to True if you want tasks to display a reminder, False if you do not.  If the value is set to True, then the reminder will appear at the time indicated by DEF_REMINDER.
      Const SET_REMINDER = True
      'On the next line edit the default reminder time as desired.  You can ignore this if SET_REMINDER is set to False.
      Const DEF_REMINDER = "7:00 AM"
      'On the next line set the value to True if you want the task to save without being displayed, False if you want the task to be displayed so you can edit it.
      Const AUTOSAVE = True
      'On the next line set the value to True if you want the review date set in the document properties, False if you do not.
      Const SET_DOC_PROP = True
      
      '***** Best not to change anything below this point. *****
      Const SCRIPT_NAME = "Document Review Reminder"
      Const olTaskItem = 3
      
      Dim objFSO, objFile, objDSO, olkApp, olkTsk, varArg, datDue
      datDue = DateAdd("d",DEF_DUEDATE,Date)
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set olkApp = CreateObject("Outlook.Application")
      For Each varArg In WScript.Arguments
          datDue = InputBox("What date must the review of the file'" & varArg & "' be completed by?", SCRIPT_NAME, datDue)
          If IsDate(datDue) Then
              Set objFile = objFSO.GetFile(varArg)
              Set olkTsk = olkApp.CreateItem(olTaskItem)
              With olkTsk
                  .StartDate = Date
                  .DueDate = datDue
                  If SET_REMINDER Then
                      .ReminderSet = True
                      .ReminderTime = .DueDate & " " & DEF_REMINDER
                  Else
                      .ReminderSet = False
                  End If
                  .Subject = DEF_SUBJECT & objFSO.GetFileName(varArg)
                  .Body = "file:\\" & objFile.ShortPath
                  If AUTOSAVE Then
                      .Save
                  Else
                      .Display
                  End If
              End With
              On Error Resume Next
              If SET_DOC_PROP Then
                  Set objDSO = CreateObject("DSOFile.OleDocumentProperties")
                  With objDSO
                      .Open CStr(varArg)
                      .SummaryProperties.Comments = "Review by: " & datDue
                      .Save
                      .Close
                  End With
                  Set objDSO = Nothing
              End If
              On Error Goto 0
          Else
              MsgBox "The date you entered for the file '" & varArg & "' was not valid.  Skipping the file.",vbCritical+vbOKOnly, SCRIPT_NAME
          End If
      Next
      Set objFSO = Nothing
      Set objFile = Nothing
      Set objDSO = Nothing
      Set olkTsk = Nothing
      Set olkApp = Nothing 
      

      Useage.

      To use this solution

      1. Open Windows Explorer
      2. Select one or more files
      3. Right-click and select Send to
      4. Select Document Review (or whatever name you used if you didn’t use Document Review)
      5. The script will prompt you for the due date for each selected file
      6. A task should be created for each file selected. If you chose to display the tasks before saving, then the tasks should appear onscreen. Otherwise, you should see them when you switch to Outlook.
      7. If you are using the document property option, then you should see “Review by: ” in the Comments property of the document. Note that this will overwrite anything already in the Comments field.

      Revisions.

      • Revision 1

      I created this revision for Dan who asked if it would be possible for the solution to handle folders as well as documents. Initially I was going to create an entirely new script, but realized it would be easier to incorporate the added functionality into this script. Scripts written with VBScript don’t distinguish between files and folders passed as parameters. All I had to do was devise a means of detecting whether a parameter is a file or a folder. I accomplished that through the FileSystemObject’s FileExist method. If it returns “True”, then the parameter points to a file. Otherwise, the parameter must point to a folder. Once the script knows what was passed it uses a couple of IF … THEN tests to act accordingly. This version includes an additional function that the original does not have. That function is IIF (immediate if). IIF is a function that exists in both Visual Basic (VB) and Visual Basic for Applications (VBA), but not in VBScript.

      One additional note. Folders don’t have the same properties as files, making it impossible to use Dsofile to set a due date as is done with a file.

      Use the original instructions for this version too. The only change is that you may select folders as well as files.

      'On the next line edit the default task subject for documents as desired.  The filename of the document will be appended to this text.
      Const DEF_DOC_SUBJECT = "Review Document: "
      'On the next line edit the default task subject for folders as desired.  The path of the folder will be appended to this text.
      Const DEF_FOL_SUBJECT = "Review Folder: "
      'On the next line edit the number of days in the future the due date will default to.
      Const DEF_DUEDATE = 7
      'On the next line set the value to True if you want tasks to display a reminder, False if you do not.  If the value is set to True, then the reminder will appear at the time indicated by DEF_REMINDER.
      Const SET_REMINDER = True
      'On the next line edit the default reminder time as desired.  You can ignore this if SET_REMINDER is set to False.
      Const DEF_REMINDER = "7:00 AM"
      'On the next line set the value to True if you want the task to save without being displayed, False if you want the task to be displayed so you can edit it.
      Const AUTOSAVE = True
      'On the next line set the value to True if you want the review date set in the document properties, False if you do not.
      Const SET_DOC_PROP = False
      
      '***** Best not to change anything below this point. *****
      Const SCRIPT_NAME = "Document Review Reminder"
      Const olTaskItem = 3
      
      Dim objFSO, objItem, objDSO, olkApp, olkTsk, varArg, datDue, bolDoc
      datDue = DateAdd("d", DEF_DUEDATE, Date)
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set olkApp = CreateObject("Outlook.Application")
      For Each varArg In WScript.Arguments
          datDue = InputBox("What date must the review of '" & varArg & "' be completed by?", SCRIPT_NAME, datDue)
          If IsDate(datDue) Then
              If objFSO.FileExists(varArg) Then
                  bolDoc = True
                  Set objItem = objFSO.GetFile(varArg)
              Else
                  bolDoc = False
                  Set objItem = objFSO.GetFolder(varArg)
              End If
              Set olkTsk = olkApp.CreateItem(olTaskItem)
              With olkTsk
                  .StartDate = Date
                  .DueDate = datDue
                  If SET_REMINDER Then
                      .ReminderSet = True
                      .ReminderTime = .DueDate & " " & DEF_REMINDER
                  Else
                      .ReminderSet = False
                  End If
                  .Subject = IIf(bolDoc, DEF_DOC_SUBJECT, DEF_FOL_SUBJECT) & objFSO.GetFileName(varArg)
                  .Body = "file:\\" & objItem.ShortPath
                  If AUTOSAVE Then
                      .Save
                  Else
                      .Display
                  End If
              End With
              On Error Resume Next
              If bolDoc Then
                  If SET_DOC_PROP Then
                      Set objDSO = CreateObject("DSOFile.OleDocumentProperties")
                      With objDSO
                          .Open CStr(varArg)
                          .SummaryProperties.Comments = "Review by: " & datDue
                          .Save
                          .Close
                      End With
                      Set objDSO = Nothing
                  End If
              End If
              On Error GoTo 0
          Else
              MsgBox "The date you entered for the file '" & varArg & "' was not valid.  Skipping the file.", vbCritical + vbOKOnly, SCRIPT_NAME
          End If
      Next
      Set objFSO = Nothing
      Set objItem = Nothing
      Set objDSO = Nothing
      Set olkTsk = Nothing
      Set olkApp = Nothing
      WScript.Quit
      
      Function IIF(varTest, varTruePart, varFalsePart)
          If varTest then
              IIF = varTruePart
          Else
              IIF = varFalsePart
          End If
      End Function
      
      • Revision 2

      This revision is also for Dan. In this version I’ve added the ability to create an appointment as well as a task. You have either or both.

      Use the original instructions for this version too. if you’re already using one of the previous versions, then replace it with this version.

      'On the next line edit the default task subject for documents as desired.  The filename of the document will be appended to this text.
      Const DEF_DOC_SUBJECT = "Review Document: "
      'On the next line edit the default task subject for folders as desired.  The path of the folder will be appended to this text.
      Const DEF_FOL_SUBJECT = "Review Folder: "
      'On the next line set the value to True to create a task or False to not create a task
      Const CREATE_TASK = True
      'On the next line set the value to True to create a appointment or False to not create an appointment
      Const Create_APPT = True
      'On the next line edit the number of days in the future the due date will default to.
      Const DEF_DUEDATE = 7
      'On the next line edit the default appointment length in minutes
      Const DEF_DURATION = 60
      'On the next line set the value to True if you want tasks to display a reminder, False if you do not.  If the value is set to True, then the reminder will appear at the time indicated by DEF_REMINDER.
      Const SET_REMINDER = True
      'On the next line edit the default reminder time as desired.  You can ignore this if SET_REMINDER is set to False.
      Const DEF_REMINDER = "7:00 AM"
      'On the next line set the value to True if you want the task to save without being displayed, False if you want the task to be displayed so you can edit it.
      Const AUTOSAVE = True
      'On the next line set the value to True if you want the review date set in the document properties, False if you do not.
      Const SET_DOC_PROP = False
      
      '***** Best not to change anything below this point. *****
      Const SCRIPT_NAME = "Document Review Reminder"
      Const olAppointmentItem = 1
      Const olTaskItem = 3
      
      Dim objFSO, objItem, objDSO, olkApp, olkTsk, olkApt, varArg, datDue, bolDoc
      datDue = DateAdd("d", DEF_DUEDATE, Date)
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set olkApp = CreateObject("Outlook.Application")
      For Each varArg In WScript.Arguments
          datDue = InputBox("What date must the review of '" & varArg & "' be completed by?", SCRIPT_NAME, datDue)
          If IsDate(datDue) Then
              If objFSO.FileExists(varArg) Then
                  bolDoc = True
                  Set objItem = objFSO.GetFile(varArg)
              Else
                  bolDoc = False
                  Set objItem = objFSO.GetFolder(varArg)
              End If
              If CREATE_TASK Then
                  Set olkTsk = olkApp.CreateItem(olTaskItem)
                  With olkTsk
                      .StartDate = Date
                      .DueDate = datDue
                      If SET_REMINDER Then
                          .ReminderSet = True
                          .ReminderTime = .DueDate & " " & DEF_REMINDER
                      Else
                          .ReminderSet = False
                      End If
                      .Subject = IIF(bolDoc, DEF_DOC_SUBJECT, DEF_FOL_SUBJECT) & objFSO.GetFileName(varArg)
                      .Body = "file:\\" & objItem.ShortPath
                      If AUTOSAVE Then
                          .Save
                      Else
                          .Display
                      End If
                  End With
              End If
              If Create_APPT Then
                  Set olkApt = olkApp.CreateItem(olAppointmentItem)
                  With olkApt
                      .Start = DateAdd("d", DEF_DUEDATE, Now)
                      .Duration = DEF_DURATION
                      .ReminderSet = SET_REMINDER
                      .Subject = IIF(bolDoc, DEF_DOC_SUBJECT, DEF_FOL_SUBJECT) & objFSO.GetFileName(varArg)
                      .Body = "file:\\" & objItem.ShortPath
                      If AUTOSAVE Then
                          .Save
                      Else
                          .Display
                      End If
                  End With
              End If
              On Error Resume Next
              If bolDoc Then
                  If SET_DOC_PROP Then
                      Set objDSO = CreateObject("DSOFile.OleDocumentProperties")
                      With objDSO
                          .Open CStr(varArg)
                          .SummaryProperties.Comments = "Review by: " & datDue
                          .Save
                          .Close
                      End With
                      Set objDSO = Nothing
                  End If
              End If
              On Error GoTo 0
          Else
              MsgBox "The date you entered for the file '" & varArg & "' was not valid.  Skipping the file.", vbCritical + vbOKOnly, SCRIPT_NAME
          End If
      Next
      Set objFSO = Nothing
      Set objItem = Nothing
      Set objDSO = Nothing
      Set olkApt = Nothing
      Set olkTsk = Nothing
      Set olkApp = Nothing
      WScript.Quit
      
      
      Function IIF(varTest, varTruePart, varFalsePart)
          If varTest then
              IIF = varTruePart
          Else
              IIF = varFalsePart
          End If
      End Function
      
      Advertisements

8 comments on “Document Review Reminders

  1. Thanks Dave, another great bit of code! Thank you!!
    I’ve got another idea…. Can you make it add a calendar entry rather than a task, or as well as a task, so that other people can be invited to review the document?

    • Dan,

      I’ve just added a revision that handles documents and folders. You’ll need to replace the code you have now with the revised version.

  2. Fantastic! Installed and using within 3 minutes! David you are a star! Can you make it work with a folder as well as a file?
    Thanks,
    Dan

    • Thanks, Dan! I’m glad you like the solution. Yes, I believe I can modify the code to work with folders instead of files. Such a solution won’t be able to implement the DSOFILE portion of the original since that only works with files.

    • Great! 🙂
      I disabled the doc properties so that would be fine. This might be of interest to others; I also turned off auto save and commented out the
      ‘datDue = InputBox(“What date must the review of the file'” & varArg & “‘ be completed by?”, SCRIPT_NAME, datDue)
      ‘If IsDate(datDue) Then
      and the end of the same if statement …
      ‘Else
      ‘ MsgBox “The date you entered for the file ‘” & varArg & “‘ was not valid. Skipping the file.”,vbCritical+vbOKOnly, SCRIPT_NAME
      ‘End If

      It meant it jumped straight into the reminder page where the date was easier to edit, feels a bit smoother.
      Hope that might help some people 🙂

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