Even though this is an age old question, I would like to contribute the (cool) observation that Pearson's $\rho$ is nothing but the slope of the trend line between $Y$ and $X$ after means have been removed and the scales are normalized for $\sigma_Y$, i.e. after removing means and normalizing for $\sigma_Y$, Pearson's $r$ is the least-squares solution of $\hat Y=X\hat\beta$ where $\hat Y = Y / \sigma_Y$.
This leads to a quite easy decision rule between the two: Plot $Y$ over the $X$ (simple scatter plot) and add a trend line. If the trend looks out of place then don't use Pearson's $\rho$. Bonus: you get to visualize your data, which is never a bad thing.
If you aren't comfortable with Pearson's $\rho$, then Spearman's rank makes this a bit better because it rescales both the x-axis and the y-axis in a non-linear way (rank encoding) and then fits the trend line in the embedded (transformed) space. In practice, this seems to work well and it does improve robustness towards outliers or skew as others have pointed out.
In theory, I do think Spearman's rank is a bit funny though because rank encoding is a transformation that maps real numbers onto a discrete sequence of numbers. Fitting a linear regression onto discrete numbers is non-sense (they are discrete), so what is happening is that we re-embedd the sequence into the real numbers again using their natural embedding and fit a regression in that space instead. Seems to work well enough in practice, but I do find it funny.
Instead of using Spearman's rank, it may be better to just commit to the rank encoding and go with Kendall's $\tau$ instead; even though we lose the relationship with Pearson's $\rho$.
Pearson's $\rho$ from Least-Squares
We can start with the desire to fit a linear regression model $Y=X\hat\beta + b$ on our observations using least-squares. Here $X$ is a vector of observations and $Y$ is another vector of matching observations. If we are happy to make the assumption that $X$ and $Y$ had their mean removed ($\mu_X=\mu_Y=0$, easy enough to do) then we can reduce the model to $Y=X\hat\beta$. For this, there exists a closed-form solution $\hat\beta=(X^TX)^{-1}X^TY$.
Under the vector notation $\text{Cov}(X, Y) = E[XY]-E[X]E[Y] = E[XY] = X^TY$ - we removed the means - and similarly $\sigma_X = \text{Var}(X, X) = \text{Cov}(X, X) = X^TX$. If we now rewrite $\hat\beta$ in terms of $\text{Cov}$ and $\sigma_X$ we get $\hat\beta = \sigma_X^{-1}\text{Cov}(X,Y) = \frac{\text{Cov}(X,Y)}{\sigma_X}$.
Plugging this back into the model and normalizing for $\sigma_Y$ resuls in $Y/\sigma_Y = \frac{\text{Cov}(X,Y)}{\sigma_X\sigma_Y}X$, where the slope is exactly Pearson's $\rho$. $Y/\sigma_Y$ is the expected rescaling of $Y$, since we are interested in a variance-normalized coefficient.