I had the same situation until I found an application called "WhoIsIt" in the market. It does exactly what you are looking for.
I downloaded WhoIsIt, and am still unsure if I like it. For one, I dont think the vibrate feature is working correctly. But that is the minor problem. The main issue I have is after setting custom tones for messages (gmail, text, SMS, etc) the standard message tone still plays too. So when I get email I get a double tone. Does anyone know of a way to fix this? Also, I still cant get a tone for my pop3 email account. Am I just SOL on that one?