Although an old thread, I came across it 11/4/2013.
The best game that meets what you are asking for and more.... is:
Lords of Magic.
Lords of Magic is a turn-based strategy PC game designed for Windows 95/98 by Sierra Entertainment. The game was intended to combine elements of Heroes of Might and Magic II and Lords of the Realm II. The special edition also contains the Legends of Urak quest pack, a set of five individual quests that revolve around stories unrelated to the main plot of the game.
There are eight different factions: life, death, order, chaos, air, earth, fire, water. Each faction represents their element with their own set of units, heroes and spells. The Heroes of the game consist of three different types which offer unique game play and tactics. They are Warrior, Thief and Mage. The Warrior acts as a Tank and leads armies into battle. The Thief works best alone being able to steal money and spy. The Thief can even kidnap other heroes, ransoming them for money and artifacts. The Mage is very weak with low hit points, but can use spells which can change the entire battle in your favor, summoning great monsters or raining down fire and lightning from behind the lines. There are over 160 different spells and they are different for each faction. These spells are divided into four types - "offensive", "defensive", "overland" and "general knowledge". There are over 90 different artifacts as well to discover.
The game includes a single-player mode and limited multiplayer features on LAN and the Internet as well.