This feature can be found on Thunderbird. Where you can select any sound file as the notification sound.
Different sounds based on email filters would be even better but not necessary.
You can see here how Thunderbird does it
(Originally posted by fcastilloec on GitHub.)
(Original post has 11 upvotes.)