Question: This might turn out to be a very stupid question, so I apologize in advance, but it is confusing me a little bit.

I know in general that if $$M'\rightarrow M\rightarrow M''\rightarrow 0$$ is an exact sequence of $A-$modules, then $$M'\otimes\_AN\rightarrow M\otimes\_AN \rightarrow M''\otimes\_AN \rightarrow 0$$ is an exact sequence for any $A-$module $N$.

If instead we are given an exact sequence of the form: 
$$M'''\rightarrow M'\rightarrow M\rightarrow M''\rightarrow 0$$ where $M'''$ is not the zero module (because I know that tensoring with a module doesn't necessarily take injective maps to injective maps), then I guess my question is why isn't the following an exact sequence as well?
$$M'''\otimes\_A N\rightarrow M'\otimes\_AN\rightarrow M\otimes\_AN \rightarrow M''\otimes\_AN \rightarrow 0$$ 

What is going morally wrong here? I guess i'm wondering where exactly is the proof of the first case failing to show that the second sequence is exact at $M'\otimes\_AN$?

I would appreciate very much if anyone could clarify my confusion. I am probably just being stupid about this :/

Answer: 
Ok so we have an exact sequence 

$$
M''' \to M' \to M \to M'' \to 0
$$

Let's break it up a bit. I'm going to denote by $\alpha$ the map $M' \to M$ and by $\beta$ the map $M \to M''$. Then we can break up the exact sequence above as two exact sequences

$$
M''' \to M' \to \operatorname{im} \alpha \to 0
$$

and 

$$
0 \to \ker \beta \to M \to M'' \to 0.
$$

Tensoring, we see that the first becomes the exact sequence

$$
M''' \otimes N \to M'\otimes N \to \operatorname{im} \alpha \otimes N \to 0 \enspace \enspace \enspace (\*)
$$

and the second becomes the exact sequence

$$
\ker \beta \otimes N \to M \otimes N \to M'' \otimes N \to 0 \enspace \enspace \enspace (\*\*).
$$

From exactness of the original sequence we know that $\operatorname{im} \alpha = \ker \beta$ so that $\operatorname{im} \alpha \otimes N = \ker \beta \otimes N$. However, it is no longer necessarily true that this is a submodule of $M\otimes N$ as the tensor product with $N$ is not necessarily left exact. 

By exactness of $(\*)$, $\operatorname{im}(M''' \otimes N \to M' \otimes N) = \ker(M'\otimes N \to \operatorname{im}\alpha \otimes N = \ker \beta \otimes N)$. However, now the map $\ker \beta \otimes N \to M \otimes N$ might not be an inclusion, there might be a nontrivial kernel. Thus, $\ker(M'\otimes N \to \operatorname{im}\alpha \otimes N = \ker \beta \otimes N)$ might not necessarily be equal to the kernel of the composition to $M \otimes N$, $\ker(M' \otimes N \to M \otimes N)$. Therefore, the longer sequence might not be exact even though the two short exact sequences are. 

This is the general idea even if we try to extend it. A long exact sequence is really just a bunch of short exact sequences at each step and tensoring won't always preserve the inclusion of that kernel and so it won't always preserve the long exact sequences. 

A more concise way to say this is that tensoring does not preserve kernels, $\ker \beta \otimes N \neq \ker (\beta \otimes \operatorname{id}\_N)$ in general so in general it can't preserve longer exact sequences where we need something mapping to a kernel to still stay exact.
If I understand the question correctly, you already knew that, when 
$$
M'''\to M'\to M\to M''\to 0
$$
is exact,
$$
M'''\otimes\_AN\to M'\otimes\_AN\to M\otimes\_AN\to M''\otimes\_AN\to 0
$$
could fail to be exact when $M'''=0$, and you tried to avoid that difficulty by assuming that $M'''\neq0$. Unfortunately, that doesn't really avoid the problem. If you have a counterexample with $M'''=0$, then you can manufacture a counterexample another with any non-zero $M'''$ that you want. Just put your desired $M'''$ in place of the $0$ and define the map $M'''\to M'$ to be the zero map. 

OK, so probably the next thing to try is to assume that not only the module $M'''$ but also the map $M'''\to M$ has to be non-zero. That avoids the construction I just described, but it still doesn't avoid the problem. Take your counterexample for the case where $M'''=0$, and modify it as follows. Put any desired non-zero $M'''$ in place of the $0$, and also replace $M'$ with $M'\oplus M'''$. Then define the map from $M'''$ to (the new) $M'$ to be the inclusion of the second summand. EDIT: I should have also said that the map from the new $M'$ to $M$ is the composition of the projection from the new $M'$ to the old with the original map from the old $M'$ to $M$.

In effect, what I've done here is to splice together your original short exact sequence with the (even shorter) exact sequence $M'''\to M'''\to 0$. That's the simplest case of (the reverse of) what Dori Bejleri explained about breaking longer exact sequences into several short exact sequences.