April 1, 2020
Code & Design
Despite it’s popularity, Office365 is criminally underused. Cutting-edge AI, near-immersive VR, & other sizzling tech topics often eclipse the world of ERP (enterprise resource planning) — basically GSuite, Office365, SalesForce, & SAP. Yet I submit that these oversights are decidedly myopic. ERP software has quietly marched along, compounding with small, but significant advancements. Consider that in the last ten years, the Office 365 ecosystem has added the following capabilities:
That’s a shortlist to be sure, but the point stands: this isn’t your early 00s business suite anymore. Modern Office365 is a power-packed business behemoth.This is purely anecdotal, but from my time in finance, startups, tech & consulting, I’d venture that upwards of 90% of businesses use Office365, yet less than 10% explore past basic, manual, data entry. Even the advanced Excel masters (lookups/index/match) I’ve met shy away from exploring behind the scenes to VBA. I’ve become less shocked over time, but it’s still incredible that the average Office365 user has zero idea about the automation potential each app holds:
People Use Office365 Apps Separately, But They’re Actually *Built* To Work In Tandem — To *Automate* Basic Cross-App Processes
VBA & programming libraries for the main Office365 apps (Excel, Word, Inbox, Powerpoint) have existed since the early 2010s; since then cross-app automation was possible — yet rarely seldom used. These days though? The environment around these integrations has exploded in all sorts of verticals & horizontals. As they say, there’s more than one way to skin a cat, well, there’s more than one way to automate Office365 processes. Here, we’ll cover three methods that vary in levels of technical proficiency:
I. Integration Platforms
II. Add-In Store
III. VBA Scripting
Each of these has it’s pros & cons; however, we can generalize that, top-to-bottom they’re both increasingly customizable & complicated. It’s highly likely that the user-friendly prepacked processes from the integration platforms or Add-In store fit 80%+ of your automation needs, but it’s this last 20% of customized solutions where VBA scripting shines.
Our first stop is in the world of integration platforms — they’re an entirely separate layer of independent, competing web apps that act as a central integration hub for small automation processes. In the last decade, a few of these apps have hit the mainstream & are now quite robust & functional. From Zapier, to IFTTT, Automate.io & many others, your options are a-plenty. To give you an idea of what one of these looks like in action, check out the following screen-grab from Zapier:
The interface above shows possible Microsoft Excel to Microsoft Outlook automation processes. Typically they have out-of-the-box “zaps” on the bottom half of the form; just in case they don’t have what you need though, you can also create a custom “zap” with a beautiful UX of drop-downs & forms. For the curious, this is easily the most recommended starting-point — just seeing how all of these “Office” suites can connect really gets your imagination going for potential integrations.
It’s not marketed swell whatsoever, however, Office365 has a literal market of Add-Ins for most of their platforms. To access it, simply open up your most-recently-used Office365 app (such as Excel), head over to the Developer tab in your ribbon (you might need to unhide this in “settings” first) & click on the “Add-ins” button — it’ll take you to the following gallery:
To provide context, there are currently 473 published apps under the Excel category. Some are free, most are freemium, yet the point stands: the average Office365 isn’t even aware that this add-in store exists. This same-store holds add-ins for the rest of the corresponding apps (Word, Outlook, etc…).
Onto the last option, scripting custom solutions. Similar in principle to any other type of product development, the more custom the need, the more resources required (read: time & capital). While both integration platforms & public add-ins offer generalized automation solutions, it’s custom scripting solutions where one can get very granular with the details. There are additional 3rd-party libraries that one can run to communicate with the Office365 environment, however, there are only two-natively supported solutions: JavaScript through add-ins or VBA through the built-in IDE.
A. Private Add-Ins
For the JavaScript-slinging younger crowd, this is likely the option you’re most likely to use. Released for production only as recently as 2018, the syntax is very modern & user-friendly; essentially it was the single-largest overhaul of the Office365 environment.
B. VBA Scripts
VBA scripting, the second method, is the old-school way of automating across Office365. VBA, which stands for Visual Basic for Applications is a strongly-typed, verbose, legacy language that’s the golden standard for Office automation. Relative to JavaScript, Python or more modern languages, VBA’s learning curve is quite steep, however, the fact that it was the singular standard for Office automation for the last few decades means there’s an excellent trove of online learning resources. These are all top-notch starting points for anyone searching for tutorials:
Both options unlock the deepest level of customization within or across the Office365 app suite. Again though, it’s likely that your needs are already met with an integration platform or the add-in store.
Despite an insane amount of competition, from corporate Salesforce to newer entries like Tableau & AirTable, Office365 & GSuite continue to dominate. Whether you’re a casual user for personal purposes or an employee with deadlines, it’s highly likely that you’re under-using Office365. Weekly reports, data downloads, spreadsheet reconciliations, customized emails…what processes will you automate?
