I can't believe no one is posting Command and Conquer Generals?! It was a total modern RTS game with top down view, although you could scroll down to get closer to the action. Completely modern weapons- nothing futuristic, although some of the stuff about China may have been exaggerated (like the overlord tank).
Well RA is not that futuristic, but maybe World in Conflict? and if you want something not so modern like WWII i suggest Company of Heroes
World at war is based on the late 80's and early 90's when the Soviet Union was still in existence- that is not exactly modern (but I guess it is not that bad since you can still have some jet fighters and stuff)
I do agree that the first and second Red Alerts were not that futuristic (excluding RA2 expansion pack, though... that was totally a science fiction upgrade or something like that)
This game (Steel Panthers) looks good, is freeware as well!... the graphics are not very good though but I guess that is normal considering all developments were around enhancing the game and not the game's graphics engine... Good find
Also you can check out some of the close combat games based on modern battles, such as road to Baghdad (although this hasn't been well received) or the enhanced version called modern tactics... In fact Road to Baghdad and its enhanced version Modern tactics were the "civilianized" version of the combat simulation game the company designed for the US military called Combat: Marines
Another modern rts (Turn based tactics and real time strategy in combat mode) is Combat Mission (rival to Close Combat) and one of their mdoern titles is Shock Force ths game also has three modules apparently (more info on the wikipedia page)