Yes, it can be used in a good way. I frequently see "overwhelmed" being used for positive emotions when talking about praise or generosity, such as "Wow, I am overwhelmed by your generosity. Thank you so much for all of these gifts." In this context, there's a potential to be misunderstood, so I would instead say "I was overwhelmed by the greatness of your party" or "I was overwhelmed with happiness at your great party." My personal preference for this context though is to say "I was blown away at your great party" (more colloquial); or if you want a more sophisticated word, "I was astonished by your great party."

Overwhelmed does not sound strange in this usage. Given the context, it means that you were so impressed with the party that words are difficult. This is a positive comment. If you had said you were overwhelmed by the confusion or crowd, it might be take as a negative. If I received such a note as you have given, I would feel complimented. If you wanted to be totally clear, you could say "I was overwhelmed at how great your party was."