I think the idea of a character using a self-destruct move is just another common RPG trait in Pokemon that wasn't really given much thought. You can trace this particular technique to the Bakudan Iwa (Bomb Rock) enemy in Dragon Quest games. As an enemy, it could just be assumed that they die after using the technique, but things would get a bit complicated in later games when you can actually get monsters (Bakudan Iwa being no exception) to join your party... or not. If I recall, "fainted" characters in Dragon Quest really are portrayed as dead, appearing on the map screen as coffins dragged by the rest of the party and needing to revived by a church (rather than the conventional inn recovery). But then you apply self-destructing moves to other RPGs where "death" isn't quite so literal and... yeah. This is made odder by the fact that the games saying that Pokemon "faint" really isn't just a simplification of wild Pokemon getting slaughtered by the second, Satoshi Tajiri really meant for them to faint as he's against video game violence.
So again, I just chalk this up as being an RPG mechanic that wasn't considered too much. However, the rest of the franchise's media at least tried to explain it by portraying it as some spontaneous unleashing of all the body's energy, or something to that extent.
Besides the fact that they faint, it isn't really known. Explosion isn't so much to mean that the Pokemon literally explodes, but just lets off a massive amount of energy, creating an Explosion, as opposed to literally exploding. This massive energy discharge then leaves the user too weak to much of anything. In most cases the user is left in a large crater and found completely out cold, but in the case of the Voltorb's, it may have been blown away by the Explosion.