When you get a new mail in Entourage then a small window appears with some basic info about the new mail you receveid. Is it possible to do the same thing with Apple Mail?
Try Growl and the GrowlMail plugin, simply click to view the message, and it's free! http://growl.info/
I've used Mail.appetizer before and it was my favorite of all the mail notification apps I've tried. It's not yet compatible officially with Snow Leopard, but there are development versions available that will probably work (link from their twitter account).
Note that in recent versions of OS X, Mail requires that plugins list the specific versions of it that they support; they can't just say they're compatible with anything newer than a certain version. The current development version of Mail.appetizer is for 10.6.5 and Mail 4.4; if you're running 10.6.6, that might be a problem. See this OSXHints article for a way to fix it.