Roughly a module can be thought of as a vector bundle on the spectrum, where the dimension of fibers may vary. Let me give some examples and facts:
A free modules corresponds to trival vector bundles, or more generally projective modules correspond to vector bundles as you already pointed out.
Let $R$ be the coordinate ring of a variety and $I$ a radical. Then the $R$ module $R/I$ corresponds to attaching a one dimensional vector space on each point of $Z(I)$ and the zero vector space everywhere else. For Example $R=k[x,y]$ and $I=(x,y)$ gives the skyscraper sheaf at the origin. $I=(x)$ gives the trivial one dimensional bundle on the y-axis etc. If your Ideal is not a radical, the situation is slightly more complicated. $R/I$ can be thought of as the trival bundle on an infinitesimal neighborhood of Z(I).
Another nice example is a geometric explanation why the tensorproduct $\mathbb Z/p \otimes_{\mathbb Z} \mathbb Z/q$ for say $p,q$ without common divisor vanishes. Our space $spec(\mathbb Z)$ consists just of a point for each prime (and a generic point). Now with the above intuition in mind, our two modules are geometrically just one dimensional vectorspaces attached to infinitesimal neighborhoods of the prime divisors of $p,q$. Since $p$ and $q$ have no common divisor there is no point where both $\mathbb Z/p$ and $\mathbb Z/q$ have nonzero fiber. As with vectorbundles, tensor produduct of modules can be thought of geometrically as fiberwise tensor product ($i^*$ commutes with $\otimes$). But of course the fiberwise tensor product vanishes because there are no points where both modules have nonzero fibers, so $\mathbb Z/p \otimes \mathbb Z/q=0$ .
Finally any finitely generated module (more generally a coherent sheaf on a noetherian scheme) is built up of vector bundles on subspaces in the following way: There exists a stratification of spec(R) such that the module pulled back to the strata is a vector bundle. This follows from Hartshorne Ex II.5.8.
