What do I need to do to make Exim handle /usr/ucb/vacation processing automatically, so that people could just create a .vacation.msg file in their home directory and not have to edit their .forward file?

Add a new router like this, immediately before the normal localuser router:

vacation:
  driver = accept
  check_local_user
  require_files = $home/.vacation.msg
  transport = vacation_transport
  unseen

and a matching new transport like this:

vacation_transport:
  driver = pipe
  command = /usr/ucb/vacation $local_part

However, some versions of /usr/ucb/vacation do not work properly unless the DBM file(s) it uses are created in advance - it won't create them itself. You also need a way of removing them when the vacation is over. Another possibility is to use a fixed filter file which is run whenever .vacation.msg exists, for example:

vacation:
  driver = redirect
  check_local_user
  require_files = $home/.vacation.msg
  file = /some/central/filter
  allow_filter

The filter file should use the if personal check before sending mail, to avoid generating automatic responses to mailing lists. If sending a message is all that it does, this doesn't count as a significant delivery, so the original message goes on to be delivered as normal.

Yet another possibility is to make use of Exim's autoreply transport, and not use /usr/ucb/vacation at all.