+1 for
Dune . I believe the French version is still in print or at least very easy to get (I have two copies, one unpacked). This game is pure awesome, IF you have at least 4, ideally 6 players. The mechanics are good on their own, but they also reflect the theme oh so very well. Be careful with the expansions though, I wouldn't recommend them for a new group. Some of the Dune sessions I participated at were the very best gaming experience I ever had, taking into account all the pc games, boardgames and pen and paper games I played.
I generally prefer the "waros", or "weuros", that is the games which lie somewhere between the typical american wargames and the more or less abstract and dry eurogames. Dune is one of such games, but there are others I would like to recommend:
The End of the Triumvirate (Das Ende des Triumvirats) - Game exactly for three people. Set during the Roman Civil war, it's more of an eurogame, but also an extremely cutthroat affair. Very well balanced, fast (about 1 hour) and tense. It's real knife fight in a phone boot, as they say.
Some of the games designed by Martin Wallace (Warfrog/Treefrog) are quite good. The best in my opinion is
Liberté and
After the Flood. Clever mechanics and rich theme (French revolution in case of the former and Ancient Mesopotanian kingdoms in the latter).
Shogun has been mentioned. It's a good game, but it's previous version (almost the same mechanics, but different theme) called
Wallenstein has a better map (in terms of gameplay).
Also try
Guerilla . Great card game, mechanics are total mindfuck, strong on theme too.
As for some non-hex wargames:
Twilight Struggle and
Hannibal: Rome vs. Carthage are both good card-driven wargames for two players.
You may also try
Battlelines, awesome and somewhat hardcore, purely card-based wargame on operational level, Stalingrad theme.
These days I find so much more enjoyment playing boardgames compared to pc games. It's one of the few entertainment areas that haven't declined (almost on the contrary), as good new games are released regularly. It's also funny how boardgames are often regarded as even more nerdy thing than pc games around where I live.
EDIT: Added some pictures.