[Stratos]

I found the last of the Shadowlords mods quite good.

Most mods are more or less hack'n'slash oriented as making a roleplaying-heavy mod requires alot of scripting. If you want more roleplaying, you might want to look for mods that requires you to play a pre-made character or a particular class. Those can often offer more than just wave after wave of enemies, but only if you play the kind of character the mod maker wants you to play.

To cut it short, there are basically two kinds of mods; combat-heavy or story-heavy ones. Both can offer a few quests, items or other goodies to each (or at least most) of the classes, but if you want a true roleplaying experience, you need to either get online or play normal PnP.