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

The big parts are Excel and email. Excel can be handled with either COM or some sort of interaction with OpenOffice.org. Email, well, there's dozens of ways to do it. My hammer of choice is Python, along with pywin32 or PyUNO, and poplib and smtplib.

Boss... will always be boss. Not always very much you can do about the icky wetware stuff.

Answer 2

One solution to #1 is to send email to a Unix server (instead of Exchange) and use procmail to dump out attachments (see http://gimpel.ath.cx/howto_fetch_proc_metamail.html for an example of how)

As for boss, have a nice web page which you can email him a link to. And send him a short email (3 lines or less) telling him that using that page will save him 30 mins of work over the course of a month and you 2 hours of work in a month. Just be prepared to back up the #s.

However, very high level, un less you're prepared to do the whole automation thing on your own time, you better be able to sell your boss that overall time savings x6 months are less than time to develop this. 'cause may be monkey salary in his eyes is low enough that the cost of software is just not worth it - and sadly he just may be right depending on how complicated a bulletproof robust solution is.

Answer 3

As I noted above, your last question is probably the most salient. It is probably best approached as a personal skunkwork project where you show the boss a completed product one day, collect your innovation bonus and then get fired because a stupider monkey can now do your job instead of you.

Answer 4

I'd start by asking myself the following questions

1. Does the invoice have to come via email or can there be a web form where the users can enter the data? There is a easy way to put a form on google docs so you can download the response in excel format in a common format set by you. I'm sure there are better ways too.
2. Does the boss need to create a new spreadsheet of can you provide him with a database app where he can view your form, enter the price, check "approved" and have that fire off the process that puts it in the production db?
3. Can the interface to the client program be worked around? Can you have some other app call the client
4. Can the text to the end user be sent by you and not the client app? If so, ca you automate that part

Just some thoughts.

Answer 5

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.