Mail.app by itself is a rather bare bones email client. However, there are a bevy of utilities for it that make it rather capable. One of my favorite such utilities is Bronson Beta's Mail.appetizer, which notifies you (in great detail) when you receive new messages. Sadly, every time a new version of Mac OS X is released, it seems to stop working. The developer has historically released new versions, but no official version for Snow Leopard is out yet. (He has been floating new development versions out through Twitter).
As a response, developer Erik Hinterbichler has taken the initiative to make a Snow Leopard-compatible clone in Herald. It's got the features I know and love from Mail.appetizer -- a translucent alert window, action buttons at the bottom of the window, control over which mailboxes trigger a notification, and even control over appearance. From my testing, it works as advertised. I especially enjoyed the full message-reading capability. No matter the length, you can scroll through the message and read it.
Unfortunately, there's one issue with Herald that unfortunately precludes me from using it: It doesn't work with Spaces. Notifications only appear in the same space that Mail is running in. If you don't use Spaces, this won't matter to you, but it's a deal-breaker for me.