A simple mail filer for Microsoft Outlook (VBA)

Like many people I receive an unmanageable amount of email each day. Many days I get through only around 1/3 of the email I receive.

However, the role I am in professionally requires me to retain a large proportion of correspondence. Some because it relates to ongoing projects, other because of security, audit or compliance reasons.

In addition, I work across many projects. It isn’t unusual for me to be involved in two dozen projects at any one time on top of my day-to-day management work.

So I have many folders – hundreds in fact – and filing email into the right folder has become a real drag. It can take an appreciable amount of time to hunt down the correct folder and Outlook does not provide any way to search/filter folder names in the UI.

Thankfully, I have access to VBA in Outlook. While the experience of using VBA macro’s to control Outlook is rarely pleasant, it does get the job done – mainly.

My requirements for the utility were as follows:

  • Must let me select multiple emails, if any have already been filed, show me the folder(s) so I can quickly file new email to the same folder as the rest of the conversation.
  • Must give me a list of all my folders with a simple way of filtering the list by typing a few letters.
  • Must also let me open a folder for viewing instead of filing or cancelling.

A couple of hours later, I was able to create a new utility. This has been published to Github and you can find it at:

https://github.com/TotallyInformation/outlook-filer

Nearest Postcode Search in Microsoft Excel

I need to find a load of addresses by proximity to a postcode. I have the addresses in a Microsoft Access database.

  1. Download Paul Jenkins’s UK Postcode csv and import into Access as a table
  2. Create a query on your own table adding the following calculated field:
    <pre>PCregion: Trim( Left( [My Table]![Postcode], InStr( [My Table]![Postcode], ” ” ) ) )</pre>
  3. Create a second query that joins the above query to the imported postcode table. Join on the “PCregion” field created above. Also add in the latitude and longitude fields from the postcodes table

Well, I couldn’t be bothered to fight with Access so I exported the key tables and used Excel instead!

The principals are the same. Load the tables as tabs, load the Postcode data as a tab. Create a front sheet containing the reference Postcode (Create a Named Cell for this “STARTREGION”). Translate the reference Postcode to Latitude and Longitude by doing a VLOOKUP to the Postcode table, e.g.:

=VLOOKUP(TRIM(LEFT(RC2,SEARCH(" ",RC2 & " "))),UK_PostCodes!C1:C4,3,FALSE)

Where RC2 contains the reference Postcode and “UK_PostCodes!C1:C4” is the postcodes table (column 3 contains the Latitude and 4 the Longitude). Note that I’ve used R1C1 reference style as it is easier to mix fixed and relative references.

Now use the same formula in the main data tables so that you have Lat. and Long. columns added based on the Postcode on each line.

Finally add a “Distance” column to each data table with the following formula:

=ROUND(DEGREES(ACOS(SIN(RADIANS(STARTLAT))*SIN(RADIANS(RC28))+COS(RADIANS(STARTLAT))*COS(RADIANS(RC28))*COS(RADIANS(STARTLON-RC29))))*60*1.1515,0)

In this case, we’ve used the STARTREGION named cell and the Lat./Long. data is in columns 28 & 29 respectively.

The calculation and Postcode reference data came from HM2K’s website: How to search by nearest uk postcode in php.