I have Automator scripts for ejecting drives, Spotllight-tagging whole folders, getting URLs in Safari to plug in to Speed Download (or DL manager of choice), and finding and deleting multiple files. Pretty simple stuff, but I'd love to see if other folks use it for other stuff that is more practical.
My only gripe with Automator (and this is more with other developers) is that there aren't a whole lot of 3rd party apps that I use that support it. Maybe next time, though.