Category Archives: Office

Create an Outlook Appointment from a Mail

Although there is the functionality within Outlook where you can drag an email message and drop it on the calendar menu to automatically create an appointment – it copies many of the attributes of the email but it does not copy the mail sender & recipients to the appointment.

The following macro will allow you to do this:

Continue reading

Outlook Calendar To Excel

I need to produce a weekly report detailing what work I have carried out. I diligently record this in my Outlook calendar. My weekly report needs to be submitted in Microsoft Excel.

The following macro will pull the entries for the last seven days from my calendar and store it in Excel format.

Continue reading

Outlook Macro – Lookup

How often are you in Outlook and you receive an e-mail from someone and you want to know a little bit more about them. It may be as simple as looking up their phone number so that you can get back to them.

This lookup may be on your own intranet site or using LinkedIn, Facebook or Twiter.

The following Outlook macro will allow you to do this.

Continue reading

Track Changes In Excel

One of the main reasons that I use Microsoft Excel is to keep lists to share with multiple people. Microsoft Word has great versioning and colabarative editing capabilities, Excel not so much. The main problem I have is knowing when a particular row has been changed and who changed it.

The following macro implements this.

Continue reading

Filter Cells In Excel With Strike Through Formatting

I have just finished reviewing an excel spreadsheet where a number of the rows were formatted with strikethough : like this.

The formatting was valid and the rows have to stay there – but I have no need to review those rows. In excel you can filter rows, by contents or even by colour but not by format.

Enter a VBA user function:

Continue reading

Uselfull Excel Macro – Make Contents Page

I commonly have large excel spreadsheets with many tabs or worksheets within the same workbook.

I use the following Microsoft excel macro to generate a contents page.

NOTE: I’m assuming the “Contents” worksheet will be the first and the list will be generated from A2 downwards.

Private Sub CommandButton1_Click()
    For i = 2 To Sheets.Count
        Range("a" & i) = Sheets(i).Name
        ActiveSheet.Hyperlinks.Add Anchor:=Range("a" & i), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
    Next i
    Columns("A:A").EntireColumn.AutoFit
End Sub

Useful Outlook Macros

Quite often I want to create a task immediately off the back of sending an e-mail – usually to remind me to follow up with the recipient after a period of time. I created a macro to do this rather than having to remember to go into tasks and do this manually – better to keep the flow rather than having to remember the steps.

Continue reading

Add Reminders To Outlook Appointments

A Reminder
Creative Commons License photo credit: Andrew Coulter Enright

If like me your Outlook calendar can get a bit hectic and others can add meetings to your calendar sometimes you end up simply “reacting” to your schedule and jumping from one appointment to the next on “autopilot”. It’s at times like these that Outlook’s ability to create a reminder can save your dignity.

However, sometimes the meeting organiser does not set a reminder. I have created an Outlook macro that will look for incoming meeting requests, without a reminder set and give you the option to set a reminder.

Continue reading

Stay Focussed – Stay OUT of Outlook!

One of my main productivity “sinks” is continually monitoring Outlook to see if any mails have arrived.

I have a rule set up that notifies me if an “important” email lands but for this to run, outlook needs to be running (minimised of course). I can’t resist the temptation to just “have a peek” outside my first thing in the morning, last thing at night email window.

To get around this I want to be “discouraged” from opening Outlook.

To implement this I have used Antonio Franca’s fantastic WinTrigger AutoHotkey script.

Continue reading

Multiple Excel Text Boxes With Common Content

I have just had to place a textbox object onto 17 worksheets within the same excel workbook, each text box containing the same commentary text. Type it once and then copy the text box and paste, job done.
Of course as soon as I have finished it I spot my typo and realise I will have to do it all again.
Only NOW do I slow down and think (OK I Google it) of the proper way to do it.
When you create a text box, rather than typing the text into the text box as I have been doing for years, with the text box selected you can type into the formula bar. In my case I put in the formula “=Metadata!B2″. This means that the textbox will display whatever I type into cell B2 on my “Metadata” worksheet.
Now I edit the textboxes I have created to reference the formula and in the future if the text needs to change, I change it in Metadata!B2 and the new text appears in all of the textboxes that refer to it!
NOTE: The text displayed in a formula driven text box is limited to 256 characters.

Last updated by at .