I'm afraid I have to add a dissenting voice to this discussion. Automator was one of the few main features that I considered killer, and I've been really disappointed by it.
Initially, it is very difficult to shrug off the fact that it runs about as well as a rhinocerous covered in treacle through a minefield. It is absolutely dog slow on my iBook, and it has repeatedly frozen on me when even doing basic actions such as selecting actions. (FWIW, this is my fourth Tiger installation for various reasons, and it's been the same on all of them. This time it's a clean install.) It's difficult to increase productivity with it when it won't work without freezing up half the time.
Then it's just the scope of it. The main actions I want automated I just can't do. I frequently upload things to a server for various website projects and then link to them and comment on them on the site itself. What I'd love to be able to do, for example, would be:
Step 1 - Choose the file to upload to the preset path
Step 2 - Choose the Link content
Step 3 - Add comment
On this, it would then upload the file, and incorporate my link and the comment to my webpage, and upload that.
I'll readily admit that I'm no computer scientist, but this is supposed to be scripting for everyone - if I can't manage it, then this certainly isn't scripting for everyone.
(Incidentally, I say this not in an arrogant way, but I think we all know the situation - not that many people are good with computers, and if a strong hobbyist user can't master something, very few others can. If the main body of interest for Automator is for people that were already into scripting anyway, it has totally defeated the object of its creation.)
Automator is an ambitious app, and one I'd like to see continue to evolve over the next few years. It has great potential, but for me, that potential has barely been tapped.