How would you start automating my job?

Question

At my new job, we sell imported stuff. In order to be able to sell said stuff, currently the following things need to happen for every incoming shipment:

• Invoice arrives, in the form of an email attachment, Excel spreadsheet
• Monkey opens invoice, copy-pastes the relevant part of three columns into the relevant parts of a spreadsheet template, where extremely complex calculations happen, like =B2*550
• Monkey sends this new spreadsheet to boss (email if lucky, printer otherwise), who sets the retail price
• Monkey opens the reply, then proceeds to input the data into the production database using a client program that is unusable on so many levels it's not even worth detailing
• Monkey fires up HyperTerminal, types in "AT", disconnect
• Monkey sends text messages and emails to customers using another part of the horrible client program, one at a time

I want to change Monkey from myself to software wherever possible. I've never written anything that interfaces with email, Excel, databases or SMS before, but I'd be more than happy to learn if it saves me from this.

Here's my uneducated wishlist:

• Monkey asks Thunderbird (mail server perhaps?) for the attachment
• Monkey tells Excel to dump the spreadsheet into a more Jurily-friendly format, like CSV or something
• Monkey parses the output, does the complex calculations
• Monkey sends a link to the boss with a web form, where he can set the prices
• Monkey connects to the database, inserts data
• Monkey spams costumers

Is all this feasible? If yes, where do I start reading? How would you improve it? What language/framework do you think would be ideal for this? What would you do about the boss?

Answer 1

There are lots of tools that you could apply here, including Python, Excel macros, VB Script, etc.

In this case, PowerShell seems like an excellent choice, as it naturally combines COM access to Office, .NET, and scripting, and is all-around-awesome. If you already know a suitable technology, you'll get the job done fastest with what you know. Othewise, PowerShell.

(C# 4.0 is also reasonable, although earlier versions suck when interacting with Office's COM interfaces.)

Don't get carried away trying to solve the whole problem at once. Start by picking a small, easy part that gets you a lot of value right away. You are more likely to succeed this way. (To get your boss to agree, you need success fast. If you aren't telling your boss, you need success even faster!). Once you have that done, you can use your new-found free time (maybe only a few minutes per day) to extend your tools and skills to the next bite-sized piece. Success will accelerate success.

In time you will replace monkey with code, and either get a promotion or quit in disgust and get a better job.