I've used both ESRI and open source tools for map automation.
The ESRI components are mentioned above, but if you have a lot of labeling to do the maplex extension is something that would save a lot of time. So
- ArcMap with the maplex extension for map authoring.
- mapbooks with arcpy.mapping to script the map output.
I have had trouble with the generation of legends with arcpy.mapping but if you have the same legend on each map, this may not be an issue. If you have access to ArcGIS Server you can publish you cartography as a map service and generate maps with the REST api, but this might be overkill.
If you don't have ESRI tools I'd use a similar setup to the mapserver stack mentioned above but using python and mapnik.
- Postgis / spatialite / shapefiles for your vector data.
- Qgis for any data work, and for easy mapnik previews.
- Mapnik for cartographic rendering.
- Mapnik's python bindings for scripting the map output (many formats avail).
- Inkscape for any custom markers, graphics or legends.
Both postgis and mapnik require a bit more front end work to set up. Mapnik was simply the best rendering engine when I first used it, ESRI and mapserver have caught up since then.
In short, at work I'd use ESRI tools as the software provides a great interface for quick cartography, and the maplex labeling engine works very well, even if arcpy.mapping isn't quite as flexible as I'd like.
At home I'd use the open source stack because it is much more flexible and infinitely cheaper!