#639 – Macros, Ghosts and GALs

Since the?early days , Microsoft always?kept an eye on what its competitors were doing . It was once?de rigeur?to produce “battlecards” which would show feature-by-feature how one product is better than its competitor, thus?assuring the customer they should buy?this?one . Thankfully, times have?mostly moved on ?to just building as good a product as possible and then let customers and?the markets decide ?– sometimes, they get improved and honed over time to be the best out there, and sometimes they get?dispatched to the boneyard ?as times move on.

In the late 1990s, Office and Exchange?(and later, SharePoint)?Server were seen as Microsoft’s entrants into the burgeoning “Groupware ” market, which became subsumed into “Knowledge Management ” c2000. Key competitors to Exchange & Outlook were?Lotus Notes ?and?Novell GroupWise , both of which came from being collab tools and gained email functions. Notes was arguably much more mature and feature rich?even if the UI was sometimes clunky , GroupWise was much leaner but found a niche in several industries. Amazingly,?GroupWise is still a thing ?and Notes evolved first into IBM Notes/Domino and was eventually sold off to now be?HCL Notes ?and?HCL Domino .

One of the early moves Microsoft made to elevate Office apps to more than just writing documents, was to try to make the docs more capable through adding Macros, and later, Visual Basic for Applications. This allowed a moderately skilful user to dabble in programming to make smarter applications centered around documents; what seemed like a good idea at the time unwittingly unleashed a wave of malware, where?bad actors ?wrote macros to do undesirable things. Following the “Melissa ” worm in 1999, Office stopped Macros running without asking the user for permission. Using Macros for anything more than tinkering never really took off.

Macros disabled entirely

Microsoft?announced in February 2022 that all Office Macros ?in content received online would be disabled completely; this was?temporarily rolled back ?in some test builds for some changes to be made in how it works, but for many, the warning will still be there if you open a Macro-enabled file that you’ve downloaded or been sent.

There are still some very useful Office macros out there, and if you do need to run one that you know is from a trustworthy source, there is a workaround – save the file to your PC locally, then right-click on it to look at the file’s properties, tick the “Unblock” option and apply that. You’ll now be able to choose to run the macro unencumbered.

One such handy macro was discussed?back in?December 2021 in ToW 611 , and is used to find Ghost meetings – ie ones you have arranged but everyone has declined?(or at least not accepted). The macro spins through all future meetings in your calendar and lists the ones you’ve organised but where you’re likely to be the only attendee who shows up. Particularly useful at this time of year if lots of people are about to take time off over the summer, and may have declined a few recurring meetings but you – as organiser – still have them in your calendar.

For the latest version of the macro,?download the ZIP file ?to your machine and expand it?(or just copy the XLSM file that’s within and put it somewhere else), do the property Unblock thing as above, open in Excel, click the button to allow content, then the Scan Calendar button and you’re all set. You still need to go into Outlook and look at the appropriate date then decide if you want to cancel those meetings or not.

Another more powerful?macro – though a little more esoteric – is one which does bulk resolution against the?Global Address List , so if you give it a list of display names and/or alias names, it will show the full name, title, department, office, email, and alias name of that person. Handy if you want to get the full details of everyone who is going to attend a meeting, but if you just have a longish list of names then you could just paste them in and see how it goes. This was covered back in?ToW 575 . One usage scenario recently was to estimate the number of people who were attending a group meeting, but were based at other offices and would therefore need accommodation.

Here’s an example output of over 500 names who were invited to a large meeting; by just providing their display names in column A, it took the sheet about 30 seconds to complete, with 10 identified as distribution lists and 50 unknowns who couldn’t be resolved, either due to no longer being in the GAL or because there were more than one possible name listed.

If you can manually find the unknown person/people in the GAL, then get their alias name and paste that into column 1 instead of the ambiguous display name, then try to run it again.