Archive

Archive for the ‘VBA’ Category

On Efficieny and Coding

June 2, 2011 Leave a comment

I’m a man who values efficiency, and I strive to achieve it in any thing I do.

As part of my role of a development team leader – I was constantly bombarded with dozens of emails each day – most of which were important to read, and to respond to. I found myself spending most of my day looking at Outlook and not much time left for coding or even code-review.

I got a tip from someone that said – use colors. Reading emails takes time because most of them are written in plain colors – black & white. Nothing differentiates words from one another and the email is just harder to read. Using colors makes the emails I write readable to others – so it doesn’t really save me time, but the though was that it would eventually catch fire and everyone will start doing it.

So I decided to implement the tip. At first people were surprised. Then, they gave me positive feedback that the emails are nice to read. Some even started using the same technique for them selves. Unfortunately, I cannot say it greatly improved things – but it was a slight improvement for the better. The plus side was that people wrote shorter emails – since they didn’t want to spend too much time coloring them.

But that is not the main story for me – I like the idea of using colors to make my emails clearer – even if only for myself.

After a week or two of coloring my emails – I got tired of spending so much time on it, so I decided to automate it.

I wrote a set of VBA macros that allowed me to color the currently selected text with ease, and I bound them into keyboard shortcuts – making it easier to color my emails.

The following macro colors the selected text/word with a single color (fore color)


Sub EmphesizeSelectedText(color As Long)
Dim msg As Outlook.MailItem
Dim insp As Outlook.Inspector

Set insp = Application.ActiveInspector
If insp.CurrentItem.Class = olMail Then
Set msg = insp.CurrentItem
If insp.EditorType = olEditorWord Then
Set document = msg.GetInspector.WordEditor
Set rng = document.Application.Selection

With rng.Font
.Bold = True
.color = color
End With
End If
End If

Set insp = Nothing
Set rng = Nothing
Set hed = Nothing
Set msg = Nothing
End Sub

Below are utility macros I did for particular colors:

Sub EmphesizeSelectedTextAsBlue()
EmphesizeSelectedText (RGB(0, 112, 202))
End Sub

Sub EmphesizeSelectedTextAsRed()
EmphesizeSelectedText (RGB(255, 0, 0))
End Sub

Sub EmphesizeSelectedTextAsGreen()
EmphesizeSelectedText (RGB(0, 176, 80))
End Sub

Sub EmphesizeSelectedTextAsOrange()
EmphesizeSelectedText (RGB(255, 128, 0))
End Sub

Sub EmphesizeSelectedTextAsPurple()
EmphesizeSelectedText (RGB(112, 48, 177))
End Sub

I also made the following macro for highlighting text (back color)

Sub HighlightSelectedText(color As Long)
Dim msg As Outlook.MailItem
Dim insp As Outlook.Inspector

Set insp = Application.ActiveInspector
If insp.CurrentItem.Class = olMail Then
Set msg = insp.CurrentItem
If insp.EditorType = olEditorWord Then

Set document = msg.GetInspector.WordEditor
Set rng = document.Application.Selection

With rng.Font
.Shading.BackgroundPatternColor = color
End With
End If
End If
Set insp = Nothing
Set rng = Nothing
Set hed = Nothing
Set msg = Nothing
End Sub

And of course I wrote some utility macros for particular colors:

Sub HighlightSelectedTextAsGrey()
HighlightSelectedText (RGB(192, 192, 192))
End Sub

Sub HighlightSelectedTextAsYellow()
HighlightSelectedText (RGB(255, 255, 0))
End Sub

Sub HighlightSelectedTextAsRed()
HighlightSelectedText (RGB(255, 0, 0))
End Sub

Binding them to keyboard shortcuts was a piece of cake – Outlook 2007 supports it easily:

Each macro got its own button with a matching (as much as possible) color.
When clicking on Alt – you can see the shortcut for each color:

Overall I can say – it is nice to take an ordinary, day-to-day activity and turn it into a “software” development task – as a developer, I really enjoyed it.

Categories: Tools, VBA Tags: , ,
%d bloggers like this: