Set Outlook to Compact on Close

There's a great tip over at Permanently delete from pst-file and Compact Now as well as Compact on Exit that shows you how to automatically set Outlook to compact PST files on exit. It's similar to the "Compact On Close" option available in Access (see SetOption for VBA syntax). Now if only there was something similar for Excel.

Set Option Using VBA

The above article shows you the registry key to set, so we're going to take that and of course set the key using VBA. Today I was a bit lazy so I borrowed the registry-writing code from Read and Write Windows Registry with VBA. However, I want to change the code slightly. The first thing I did was create a custom enumeration to handle the possible registry key types:

Enum RegKeyTypes
  REG_SZ
  REG_DWORD
  REG_EXPAND_SZ
  REG_BINARY
End Enum

Now I take the registry-writing procedure and alter it as follows:

' adapted from http://vba-corner.livejournal.com/3054.html
Sub RegKeySave(RegKeyName As String, _
               RegKeyValue As String, _
               Optional RegKeyType As RegKeyTypes = 0)

  Dim myWS As Object
  Dim regType As String

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")

  'write registry key
  ' convert Enum to String
  Select Case RegKeyType
  Case 1
    regType = "REG_DWORD"
  Case 2
    regType = "REG_EXPAND_SZ"
  Case 3
    regType = "REG_BINARY"
  Case Else  ' REG_SZ is default, per original code
    regType = "REG_SZ"
  End Select

  ' write to registry
  myWS.RegWrite RegKeyName, RegKeyValue, regType

End Sub

I like this code better because it allows me to use Intellisense to choose the registry key type from a dropdown, and I can still set the default to String, the same as the original code.

The Code

So, given the previous code that creates registry keys for us, here is the entire code for telling Outlook to compact my PST file on close:

Sub AddCompactOnCloseRegKey()

  Dim regKey As String

  Const defaultRegKey As String = "HKCU\Software\Microsoft\Office\<version>\Outlook\PST\PSTNullFreeOnClose"

  ' get version
  regKey = Replace(defaultRegKey, "<version>", Application.Version)

  Call RegKeySave(regKey, "1", REG_DWORD)

End Sub

If the key already exists, this code sets (or changes) it's value. Otherwise the key is created and its value is set.

Note that I happen to run this code in Excel; you could easily run this in Outlook as well. Since this a one-time procedure, and you can only have one Outlook install, you could easily just hardcode your Outlook version into the code.