The way automation works is that at the beginning of every game, all players are assigned AI subsystems, even human players. The only difference between AI players and human players is that AI players are 100% controlled by those subsystems, while human players are not. The subsystems are present and churn away in the background even if their powers are limited; advisor and plot recommendations are often these subsystems' outputs to human players. One of those AI subsystems, HomelandAI, controls, among other things, AI scouts and AI workers. If you automate a worker or set a unit to automatically explore (effectively making it an automated scout), HomelandAI will simply take control of the unit until a special condition is reached (eg. no more tiles to explore for scouts, nearby enemy unit for workers).
In the unmodded game, no other automation is possible (well, there's citizen automation, but that doesn't really count). However, there are plenty of mods that allow you to let the AI subsystems take control of things like research and production in cities (the only one I can think of off the top of my head is the CPP, or Community Patch Project, and I'm not completely certain). Automating more types of units is a lot trickier, since a lot of the AI's functionality revolves around unit "batches"/armies that get created based on pre-defined recipes call "operations", but I wouldn't see why, say, missionaries couldn't be automated.