Writing a macro to e-mail a worksheet from an Excel workbook

I spent most of today trying to catch up with my expenses (in order to plug the rather large hole in my bank balance before Christmas).  I work for a large IT company, and to say that our systems are antiquated would be an understatement: I understand that my expense claims are entered onto a computer system prior to payment but, before that, I have to complete an Excel spreadsheet and mail a hard copy, with receipts attached, to a team that processes them (which is corporate-speak for wasting my time, their time, and my manager’s time to quibble over minor infringements).  Recently a new level of bureaucracy was added to the process and, before snail-mailing the hard copy to be processed, I also have to e-mail a soft copy to my manager for approval, using a pre-defined format for the mail subject header.

You can probably tell by the tone of this post that I’m no fan of this process.  I understand from contacts at Microsoft, for example, that their system is entirely electronic, although paper receipts do also need to be submitted for audit purposes and I can’t see why we couldn’t do something similar.  Still, it could be worse: when I worked for a major fashion design, marketing and retail organisation a few years back, they insisted that I staple each receipt to a sheet of A4 paper first…

Anyway, after messing up the process a couple of times today and recalling messages with incorrectly formatted subjects, I decided that it’s something that should be automated.  I’ve never written any Visual Basic for Applications (VBA) before, but, armed with a few code snippets from the web, I managed to write a macro (the whole thing took me about 30 minutes to pull together and debug):

Sub SendToManager()
' SendToManager Macro
' Macro to send completed expense worksheet to one's Manager
' Keyboard Shortcut: Ctrl+Shift+M

'Create a new Workbook Containing 1 Sheet (right most) and sends as attachment.


With ActiveWorkbook

.SendMail Recipients:="mail@domain.com", Subject:="Expenses for approval: " " & Range("C8").Value & ", " & Range("O8").Value & ", " & Format(Range("O9").Value, "Long Date") & ", " & Format(Range("Q48").Value, "Currency")

.Close SaveChanges:=False

End With

End Sub

The code is here for anyone that might find something similar useful… I’m sure that it will need modification to suit someone else’s requirements but the basic idea is here.  Basically, we create a copy of the right-most worksheet in our Excel workbook (I create a new sheet for each claim, and work left to right…), then we take that and send it to the specified recipient (one change might be to prompt for a user name) and format the subject with data from the sheet that eventually reads “Expenses for approval: name, employee number, claim date, claim value” before sending the mail.  Simple really.

Here are a few links that helped me out in doing this:

An introduction to business intelligence for IT Managers

A few weeks ago, I caught one of the IT Manager series of webcasts that Microsoft is running, where Andrew Fryer was introducing Business Intelligence for IT Managers (I’ll steer clear of the obvious joke about IT managers and intelligence there… I might want a job as an IT Manager one day…). This was an interesting presentation for a couple of reasons: it’s not a topic that I know well; and Andrew presented 290 slides in less than an hour (which sounds a lot, but it wasn’t – he used slides with just a few words or a picture, in rapid succession – and I like that style).

I can’t find the recorded version of the presentation online but this blog post attempts to capture the main points.

According to Wikipedia, Business Intelligence (BI) can be defined as follows:

“Business intelligence (BI) refers to skills, technologies, applications and practices used to help a business acquire a better understanding of its commercial context. Business intelligence may also refer to the collected information itself.”

That’s a bit of a mouthful, but basically it makes BI seem hard. So, let’s think about intelligence without the business context – is it: knowledge and understanding (we used to think the world was flat!); about meaning and context (some information can seem obvious without context); about foresight (to predict future events); the ability to solve complex problems; or the ability to make decisions?

We make decisions all the time – and some of those decisions are poor ones – so if intelligence is about making decisions (and decisions require people), what makes a good decision? The answer is information. The information provides the meaning and context together with the background information (knowledge and understanding, likelihood of a given event occurring, etc.) to allow someone to make the right decision.

Information has been used throughout history to share stories, to learn and to discover things. Over time, information has helped to provide answers and to unlock secrets, which allowed innovation. Information has provided answers – and answers have allowed people to make decisions.

In a business context, the information is derived from data (about people, products, places, etc.). Where there are questions (which products are best? how are the sales figures looking? how are my people?), some insight is required to provide meaning and to convert raw data to information.

That data needs to be stored – originally it was stored in paper files and later on computer disks and tapes – but it also needs to be managed. The advent of databases provided a means for data to be stored and managed but business applications were needed to abstract the management of the database from business end users. These business applications provided a better way to collect data, easing the process of data entry and managing access, to ensure that those who needed access were able to find answers to business questions. But is wasn’t easy – the data was sourced from many locations. Reports were one approach, but they were one-dimensional and siloed, fragmented and lacking insight.

The advent of data warehouses allowed data from multiple locations to be organised, managed and accessed – or consumed. Now the business applications could analyse as well as report and the term “Business Intelligence” was born. Promising more access, from more locations, BI vendors created demand for more data. This led to businesses wanting faster access to data (improved performance). But as the volume of data exploded, so did the use of personal computers, and most data ended up in desktop productivity applications like Microsoft Excel, and Access. There was no single version of the truth upon which to base decisions, the data was hard to maintain and the BI tools cost a lot of money, so vendors had to find a way to reduce costs and offer increased functionality. The result was a period of vendor consolidation in the BI tools space and the formation of a few BI platforms, from companies like Oracle, SAP, IBM and Microsoft, offering more tools and more functionality, for both online and offline access.

But, for all the promises, BI tools were still not working. Business users were still confused and the business couldn’t get the answers that were needed. It wasn’t about people – it was still about disparate systems, with access to data controlled by the IT department. An overstretched IT department. So business users started to circumvent IT departments, but the BI tools were not intuitive and the users didn’t have the time to be IT administrators. Suddenly BI was about usability, and turning data into the right format to be easily consumed by people, with that data managed by IT.

There’s not just the data in the databases to consider either – there is unstructured data too. That unstructured data comes from places like blogs, wikis, e-mail messages, documents, presentations, and videos – at one point analysts considered that 80% of business was conducted based on unstructured data.

So BI is about the right person, accessing the right data, at the right time – and it needs to be people-centric because it’s generally people that make decisions, not computers. Businesses need to do more to collaborate, search, and communicate about questions and answers in order to drive innovation. Even in today’s times of economic uncertainly, BI is still a priority at CxO level in order for businesses to do more with less, to provide better insight for better decision-making, for more people.

Reporting and scorecards are important components of the BI toolset, along with dashboards to display key performance indicators, for analysis. On the desktop we still use applications like Excel but the data lives in the warehouse. Other BI features include data mining (e.g. the shopping basket analysis that supermarket chains carry out using our loyalty cards). For unstructured data, we have portals for collaboration.

In today’s BI implementations the critical success factors are sponsorship (at a senior level in the company), a compelling need, a culture of analysis (rather than looking for divine inspiration) and, most importantly, partnership between the IT department and business users.

I don’t pretend to know anything about any of the specialist BI tools but, on the Microsoft infrastructure side, we already have some useful tools. Office gives us desktop applications like Excel, there are collaboration services in the form of SharePoint products and technologies, and we have a scalable database engine in SQL Server – there’s more information on Microsoft’s BI blog and learn more about the products on Microsoft’s BI website. There’s also advice on planning for BI in the SharePoint Server TechCenter, webcasts, videos, virtual labs and podcasts and more advice for IT Managers and their teams on the TechNet website. Finally, if you just want the highlights and a bit of technical analysis, Andrew Fryer’s “Insufficient Data” blog is worth a read.

Controlling the view on diagrams generated with Visio 2007’s Save As Web feature

I’ve been doing some work recently with a “taxonomy” of technology “building blocks”. Even though a taxonomy is technically expressed as a hierarchy, technology terms do not really fit into a hierarchical structure – what we really need is a network diagram but management want it to look like an organisation chart (some cynical people might say that’s all they understand)!

My colleague, Alan Dodd, who understands TOGAF (I’m not an enterprise architect) has been instrumental in defining a structure that we can slice many different ways, generating views based on particular metadata and he’s also the one who came up with the idea of using Visio 2007’s Organisation Chart Wizard to import data from an Excel spreadsheet and use the column headings as metadata. Excel data for importing into Visio with the Organization Chart WizardFor example, if I have columns of: Item; Parent Item; Vendor; and URL, I can build the hierarchy using the Item and Parent Item columns and the Vendor and URL columns can be defined as metadata on the shapes in Visio, from where I can save the whole diagram as a web page (and the URL data will actually work as a link). Add a bit of conditional formatting and we have something that’s actually quite usable as a navigational tool for linking to the various technology building blocks.

The problem I had was that my diagram was huge and needed to be zoomed it to 500% in order to be legible. Increasing the font size didn’t help either, as that just needed larger shapes, making the overall diagram larger (and so the default, whole page, view was just as tiny). What I needed was a way to adapt the zoom factor on the diagram… for instance to set the initial view to 500%.

It turns out that’s perfectly possible using ?zoom=500 on the end of the URL to load the diagram. After a brief conversation on the Microsoft Discussion Groups, John Goldsmith has helpfully posted the four basic URL parameters accepted by Visio-generated diagrams served via HTTP.

The next steps will be to make the diagram zoom closer than 500% and then the big one… to automatically generate the Excel data from a SharePoint document library. Answers on a postcard…