For the 2003-2007 Rangers the smallest engine you will be able to find is the 3.0L V6. I would, however look more into the 4.0L V6, the 3.0L makes similar power to the 4 cylinder engine but gets almost the same gas mileage as the 4.0L. I'd also go with the 4.0L over the 3.0L simply because my father had bad luck with the 3.0L in his Aerostar (needed rebuild at 100k miles, and again at 145k miles), and one of my co-workers with an 05' land up having serious engine problems with the 3.0L (under warranty thankfully). I'm sure there are other people on the forum who will stand up for the 3.0L as they may have had good experiences with it, I've just as a whole seen 4.0s go a lot longer than 3.0s.
The 4cyl is the only Ranger engine that really returns decent/good gas mileage. Gas mileage is my main gripe with the 4.0L, but my truck is a 4x4 and has 4.10 (taller) gears. The 4.0L, is a tough engine though, and has never came close to letting me down.
The transmission thing comes down to preference. For the automatics one of the most unreliable transmissions was the A4LD which Ford stopped putting in the Rangers in the 90s. The newer automatics are much better. The newer manual Rangers use the m5od transmission which is supposed to be one of the most reliable transmissions you can for a Ranger. The manual transmission can sometimes be finicky with gears 1-3, some people have perfectly smooth shifting transmissions, some don't. My transmission doesn't like to go into 1st unless I'm at a stop (which is fine, some transmissions actually lock you out of 1st unless stopped as a safety precaution), 1st to 2nd and 2nd to 3rd shift can be difficult when its -20F or colder outside, and same goes for 4th to 3rd and 3rd to 2nd when its -20F outside. Gets easier as the transmission warms, probably just the transmission fluid thickening from the coldness. Its summer time and all shifts are smooth in my Ranger, any issues I've had are climate related, and I've had similar issues in my last car (Toyota made). I have little experience with automatic Rangers, but in the years you mentioned I would think it should be as good as the manual Ranger.
Hope this helps.