In short, it is an application that allows you to automate routine tasks and more.
For example, lets say when you sit down to your computer, you typically start iTunes, iPhoto and Mail. Then you always start your playlist in iTunes called "My List", and search mail for items with subjects containing "Do Today".
You can create an Automator Workflow that will do all these things for you.
A more detailed description follows:
Automator is a workflow application introduced as part of Mac OS X 10.4 (Tiger). It leverages the power of AppleScript to provide a drag-and-drop workbench for streamlining repetitive tasks. Unlike traditional scripting solutions, you create workflows by organizing packages of code called actions. Automator comes with a set of actions you can use right away. Plus Apple provides links to third party actions that you can add to your collection. Since you're working in a user interface, you don't need to know any programming to get started. Just arrange the actions in the order you want them executed and hit the run button. Those more comfortable with programming can, however, embed scripts inside workflows or develop new Automator actions. Once you've created a workflow, you can save and distribute it as a stand-alone application that can be used by others who have Mac OS X 10.4 or later.
To learn more:
http://support.apple.com/kb/HT2488