I thought it might be nice to couple Terry Tao's great general answer by showing we can write down an explicit limit to the classical case for the simple harmonic oscillator. These solutions are an example of "coherent states". I learned this from an old blog post by John Baez which I can't find right now; Wikipedia has a less helpful exposition.

We work with an oscillator of frequency $\omega$, so the potential energy is $(1/2) m \omega^2 x^2$ and Shrodinger's equation is
$$i \hbar \frac{\partial}{\partial t} \psi = - \frac{\hbar^2}{2 m} \frac{\partial^2}{(\partial x)^2} \psi + \frac{m \omega^2}{2} x^2 \psi.$$

As usual, it is convenient to define
$$a = \sqrt{\frac{m \omega}{2 \hbar}}\left(x+\frac{\hbar}{m \omega} \frac{\partial}{\partial x} \right) \quad \mbox{annihilation}$$
$$a^{\dagger} = \sqrt{\frac{m \omega}{2 \hbar}}\left(x-\frac{\hbar}{m \omega} \frac{\partial}{\partial x} \right) \quad \mbox{creation}.$$

The lowest energy state is the kernel of $a$, namely $\psi_0 := \exp(-m \omega x^2/(2 \hbar))$; it gives rise to the solution $e^{i \omega t/2} \psi_0$. Then $\frac{1}{\sqrt{n!}} (a^{\dagger})^n \psi_0$ is the $n$-th energy state, so $e^{i (n+1/2) \omega t} (a^{\dagger})^n \psi_0$ is the $n$-th solution to the time dependent equation (up to normalization). I prefer to rewrite this as $(e^{i \omega t} a^{\dagger})^n (e^{i \omega t/2} \psi_0)$.

If $F(z)=\sum f_n z^n$ is any power series then, at least formally, $F(e^{i \omega t} a^{\dagger})(e^{i \omega t/2} \psi_0)$ is a solution of Schroedinger's equation, since it is a linear combination of the pure energy states above.

In particular, take $F(z) = \exp(C z)$ for some scalar $C$. So
$$\exp\left( C e^{i \omega t} \sqrt{\frac{m \omega}{2 \hbar}}\left(x-\frac{\hbar}{m \omega} \frac{\partial}{\partial x} \right) \right) (e^{i \omega t/2} \psi_0)$$
solve Schrodinger's equation. We reduce clutter by setting $C\sqrt{\frac{\hbar}{2 m \omega}}=R$; the constant $R$ has units of distance.
So our solution is
$$\exp\left( e^{i \omega t} \left(\frac{R m \omega}{\hbar} x-R \frac{\partial}{\partial x} \right) \right) (e^{i \omega t/2} \psi_0)$$

Now, the commutator of $\tfrac{R m \omega}{\hbar} x$ and $R \tfrac{\partial}{\partial x}$ is $\tfrac{m R^2 \omega}{\hbar}$, which commutes with both $x$ and $\partial/\partial x$. So, by Baker-Cambell-Hausdorff (and discarding some global constants) we can rewrite $(\ast)$ as
$$\exp(e^{2 i\omega t} \tfrac{m R^2 \omega}{\hbar}) \exp(e^{i \omega t}\tfrac{R m \omega}{\hbar} x ) \exp\left( -e^{i \omega t} R \frac{\partial}{\partial x} \right) (e^{i \omega t/2} \psi_0).$$

The exponential of differentiation is translation, so this is
$$\exp(e^{2 i\omega t} \tfrac{m R^2 \omega}{\hbar}) \exp(e^{i \omega t}\tfrac{R m \omega}{\hbar} x ) (e^{i \omega t/2} \psi_0(x-R e^{i \omega t})).$$

One can then do a bunch of work translating each formula into its real and complex part, which I omit. At the end of the day, one get's a solution to Schrodinger's equation which roughly looks like
$$e^{i A(t)} \exp\left(\tfrac{m \omega}{\hbar} \left[-(x-R \cos(\omega t))^2/2 - i R \sin(\omega t) x \right] \right).$$
Here $A$ is a big messy function I am unwilling to work out.

This is the sort of gaussian beam solution Terry was talking about -- it is localized both in position and in Fourier space. In position space, it is a Gaussian centered at $x=R \cos (\omega t)$. As $\hbar \to 0$ (with $R$ fixed), the Gaussian becomes tighter and tighter until, in the limit, it is a delta function at $R \cos (\omega t)$ -- the classical solution to the problem. Meanwhile, the momentum is a Gaussian centered at $-m R \omega \sin(\omega t)$. Again, as $\hbar \to 0$, the Gaussian becomes a delta function at $-m R \omega \sin(\omega t)$ -- the classical solution. (Of course, you could ignore all the discussion about $a$ and $a^{\dagger}$ and just directly check that this solves Schrodinger's equation. If you do, please let me know what constants I left out!)

If one tries to take the $\hbar \to 0$ limit of some simpler solutions like the pure energy states, they bunch up at the origin while spreading out over all of momentum space. You need a moderately complicated solution like this to get both limits to make sense.

I will close by noting a heuristic way to think about $\exp(C a^{\dagger})$. The coefficient of the $n$-th energy state is $C^n/\sqrt{n!}$ (putting in the correct normalization constant.) So, if we observe the energy of this particle, we have probability proportional to $C^{2n}/n!$ of getting the answer $(n+1/2) \hbar \omega$. In other words, the energy of this particle is a Poisson random variable with expected value $C^2 \hbar \omega+\hbar \omega/2$. Plugging in for $C^2$, this is $m R^2 \omega^2/2+\hbar \omega/2$. The $m R^2 \omega^2/2$ term is the energy of the classical solution. So this solution may be thought of as the best attempt to mimic an energy of $m R^2 \omega^2/2$ when we only have access to the discrete levels $n \hbar \omega$.