After the Reformation in the mid 1500s, the pope lost any kind of official control of a large part of Christian Europe. So when the pope excommunicated a country's leader, the leader could basically say "whatever dude, I'm a Protestant now," and continue to be seen as a legitimate ruler.

As you can imagine, this resulted in a fair amount of strife throughout the continent. Which eventually came to a head when some Catholic lords of the Holy Roman Empire were thrown from a window by some Protestant dignitaries after a diplomatic meeting went sour (Defenestration of Prague, 1618). That was the spark that essentially started the 30 Years War, which is widely regarded as the last religious war in Europe. It ended with the Peace of Westphalia in 1648.

The Peace guaranteed religious sovereignty of states (which were created based on nationality and culture, as opposed to the chosen religion of the Holy Roman Emperor). The princes who ruled these smaller states were now able to choose the religion of the region they ruled. This is in stark contrast to the previous laws; which entitled the Holy Roman Emperor (a Catholic Hapsburg) to decide the official religion of the Germanic states (even though the populus was mostly protestant). It also set rules for respecting the boundaries of sovereign states and helped stop unwanted intervention from outside entities like the Vatican or other states' rulers.

Naturally, the Vatican was not a huge fan of the Peace. Pope Innocent called it, "null, void, invalid, iniquitous, unjust, damnable, reprobate, inane, empty of meaning and effect for all time". One could argue that his contempt was largely based on the immense loss of power that he suffered because of Westphalia.

Overall, The 30 Years War and the Peace of Westphalia all but ended religious warfare in Europe (Spain and France went at it a couple more times over the next half century or so.), and laid the groundwork for state/nation building based on more localized cultural differences (nationally), which eventually led to the rise of Nationalism.

Sources:

http://en.m.wikipedia.org/wiki/Peace_of_Westphalia

Westphalia: The Last Christian Peace by Derek Croxton

I would make an argument, that it never was that much of a big deal to start with. Most competent Holy Roman Emperors got excommunicated at some point (one led a successful crusade WHILE excommunicated) some waged direct wars against the popes.
IIRC not one and two of the French Kings were also excommunicated. English kings also had their fair share.
It was often lifted ether after the dispute ended or after the pope was changed. And when the Reformation kicked in it lost all relevance.