Children, pregnant women and people with mental health conditions will receive better support for alcohol and drug issues in a major funding boost.