How do I e-mail reports based on Excel documents?

February 20, 2009 3:50 PM

Is there a nice way for me to run a report on an Excel spreadsheet and have that report e-mailed to me? I have a spreadsheet with dates of contracts expiring, I would like for it to shoot me an e-mail notifying me that I'm within 30 days of that contract expiring, that a new contract has been added and so on.

I'm using Excel2007 and have access to a TechNet license so I can test and try before I buy. I have a spreadsheet I created that breaks down some complicated contracts where certain things happen at certain times. It would be really neat if it would remind me when certain events are about to happen, or when changes are made.

Is this something SQL Server Express or one of the for-pay variants will do? If so can I use Excel to construct and work on the spreadsheets and then have SQL pick it up and do the backend stuff?

There's nothing really complicated in the spreadsheets, they're all small (less than 100 rows) and unlinked. I know this has to be simple, that there's got to be something that will open the workbook, extract information out of cells and pass them onto my SMTP host.

Preferably Microsoft centered solution as I might have to pass this off to intelligent but non-technical people in the future.

posted by geoff. to Computers & Internet (6 answers total)

I imagine you can write a VBA macro to do this. I imagine that would call Outlook to send email. In a dumbass implementation of "security", Oulook will every ten minutes require human intervention to permit macros to run. Or at least it did last I tried writing VBA code i Outlook (circa 2003). Long story short, while there are work-arounds, it's probably going to be a giant pain in the ass.
posted by orthogonality at 3:55 PM on February 20, 2009

Response by poster: Hmmm, I was looking for something a bit more server based than an Outlook client running macros.
posted by geoff. at 5:01 PM on February 20, 2009

I'd do it with Excel VBA using the tips from here or here (using this add-in), name the macro Auto_open, and then use windows scheduled tasks to open the workbook every hour or whatever (with the VBA closing it after checking if a deadline has been reached, and mailing)... but that's cos I'm shit with windows scripting host, which is probably the right way to do it.
posted by pompomtom at 7:18 PM on February 20, 2009

Are you tied to Excel for some reason? This would be easy in Access, Filemaker, Project -- or a number of other apps. In Excel, I think it could be programmed with VBA, as has been suggested, but it will be some heavy coding, I bet.
posted by crapples at 7:50 PM on February 20, 2009

Seconding crapples. So, let me guess. You put all your data into Excel because it's what you know, right? Now you discover it may be difficult to do what you want with it. It would have been so much better to outline the whole project, including the email, and decide what application would be most appropriate. I vote for a simple Access app. I bet you can even import your spreadsheet into an Access database using a built-wizard.
posted by exphysicist345 at 11:39 AM on February 22, 2009

Response by poster: So, let me guess. You put all your data into Excel because it's what you know, right?

Yes, and it is very easy to manipulate the data and formulas, make it look pretty and hand it off. Security is enforced through by Active Directory and locking down the drives.

I think I will use Excel as a tool to define my data structures and outline what I want, when the spreadsheet matures to the point I feel comfortable moving to a more permanent solution I think I'll just move it directly to a Sharepoint list. This has the advantage of being able to export it to Excel if I need to manipulate it and change the data. I'm probably breaking a lot of RDBS rules here.

I think ideally I'd like the database to spit out reports in Excel on a SMB share every month, so if I get an e-mail and need to look at what's going on I have a pretty Excel spreadsheet that I can use and manipulate the data if I'm trying to figure something out.

So I think the solution I'm looking into right now involves canonical sql server spitting out data in XML for long-term archiving every month, spitting out Excel reports every month and accepting changes to Excel documents via Sharepoint. What scares me about this is that I'm not 100% comfortable with how Sharepoint manipulates data and I'm worried that a check in will bring the whole thing down and I don't want to get too heavily involved with backing up / restoring Sharepoint and its corresponding SQL instance, so I'll just rely on the XML files and restore from a clean instance if need be.

I really don't have my answer still, but if I get it into something more formal like this, I'm sure I can accomplish what I want in a less half assed way.
posted by geoff. at 9:31 AM on February 23, 2009