This is not, in general, true. Modules $N$ for which the resulting sequence is always exact were given the adjective flat by Serre, and this property has interesting geometric implications. The standard counterexample is tensoring the injection \\[ 0 \to \mathbf Z \stackrel{\times 2}{\longrightarrow} \mathbf Z \\] with $\mathbf Z/2\mathbf Z$. However, tensoring _is_ right exact, i.e. \\[ M' \otimes N \to M \otimes N \to M'' \otimes N \to 0 \\] must be an exact sequence.