Can I use automator to generate videos?
Automator can help script other applications but it is not able to create new content alone, such as movies.

Keynote and Other Alternatives

Have you considered using Apple's Keynote? Keynote can be used to create QuickTime movies involving impressively complex motion. Keynote supports AppleScript and can be automated.

Alternatively, the inexpensive and capable Motion is worth trying.

A more technical approach would be ImageMagik to create individual frames of the video and ffmpeg to stitch the frames together with music.