I used it once to join two music libraries by going inside each folder, and inside that one, etc etc, and then pulling the MP3's from them. Then putting all of those in a folder replacing duplicates so there would only be one copy of each song. I then added all of the songs to iTunes through automator.
You can also get it to auto update iTunes to a folder that you just put music into and then delete the music automatically. Kind of like a dropbox for mp3s. You can use it for tons of stuff.
If you are editing a bunch of documents the same way you can get it to help you in that process.
Its weird cause it seems like automator is built for people who don't know how to, or don't want to write scripts to do tasks for them, but may need to do a lot of the same thing over and over. But I personally find it easier to write a python/pearl script for most of the stuff I do that seems tedious, because I can customize it however I want. Automator is for an odd niche of people it seems. People technically savvy enough to use a utility like this, but don't want to write their own scripts. It is really really powerful though.
Cool sites:
http://automatorworld.com/
http://dougscripts.com/