This one was so fun to use. I miss those BFME games.

I like lifesteal spells, like Life Drain (Spirit Mastery) from Titan Quest. I also love to use weapons with that enchantment whenever possible.

Unepic has some nice spells that you get later on that allow you to do things like permanently upgrade your equipment.

There's also the Reflect Projectiles spell, which many players ignore because you need to spend a lot of skill points on a skill you don't get until near the end of the game, but which turns out to make the endgame much easier. The most practical ways to get this spell are to use the item that lets you respec (you only get one, unless you have earned certain achievements that makes it possible to purchase more with a special currency) and to play on the hardest difficulty (which gives you more skill points, and where you're most likely to need this spell).

Yes, lifesteal effects are fun.

Final Fantasy 5 has a few nice ones. The Drain spell isn't that good, but there are other options:
* Vampire, if it hits, will drain an amount equal to half the difference between the caster's current and maximum HP, and it can be acquired quite early (as soon as you get the first set of Jobs). (On GBA, this spell is stronger than it should be due to a bug, and will drain just enough to fully restore the caster's HP.)
* Spellblade Drain makes the caster's attacks with swords drain HP. Unfortunately, good swords might be a bit scarce around this point in the game, and you may wish to use Spellblade Break to kill enemies faster (immunity to it is much rarer than you'd expect, and it works reliably if the target isn't immune). One nice thing about Spellblade Drain is that it can be combined with commands that improve your physical attacks, allowing you to do even more damage. (Ninja with Spellblade dual-wielding knives; just cast Berserk and she'll drain even more HP.)
* Drain Kiss (Succubus Kiss on GBA). This isn't really a spell, but rather a specific outcome of the Chemist's !Mix ability. You can get access to this attack not too far into the game (before you get Drain or Spellblade Drain), it always hits, and it hits as hard as the Bahamut summon, which doesn't appear until late game.

They're also fun in The Quest, though you need to be undead in order to use such spells. (Becoming undead is a choice you make at character creation.)

Morrowind and Oblivion also allow you to create some nice HP drain spells. Arena has weapons that can be used to cast Lifesteal, and said items can be used a large number of times before they break (and, if you're a Knight, will even automatically get repaired).