I thought Insurrection was pretty good myself. I just didn't like the people on the planet. What gave them the right to be there over anyone else? I hated that Sona scream that the main character did. What was up with that?
Destroying the Enterprise D was a mistake. That ship WAS TNG. It was easily identifiable, beautiful and just was TNG. Replace it with a souped up ugly warship just didn't fit with TNG. Then you rewrite the characters and have Picard out of character just really ruined the movies. It stopped being TNG and the audiences just weren't interested. Given how popular the series of TNG was it all the movies were a massive failure in my opinion. To many changes.
It was destroyed to make way for a more epic-looking model. I can understand the rationale of that, but the way it was destroyed was not very satisfying, not compared to, let's say, the death of the Enterprise in Search of Spock. The Enterprise-D never really won me over. The wide neck and the U-shaped warp pylons and the stubby rear-end without a true shuttle-bay door is just not iconic. It's just too manta-ray-like, too feminine, and too mid-80s. It was, at best, adequate. The Enterprise-E was more of a back-to-basics design. The plating was overdone on it, but the overall shape is pretty sexy. TOS had a 7 year or so gap between the end of the 5-year-mission and TMP, and it is that time away that advanced the characters to a new phase which was then explored from TMP onward. The characters have to GROW. The TNG movies didn't have the same sense of characters growing into middle age. That is why the Borg PTSD storyline with Picard was played up, to give the character a new wrinkle, plus Data and the emotion-chip. Movies are usually not "bottle" stories. Maybe James Bond movies, but that's about it. The protagonists have to somehow change. Kirk changes over time from one movie to the next. He's a stuffed shirt in TMP, and he learns to feel young again in Khan, and he learns the value of sacrifice for friendship in Search for Spock. Then they all have fun in Voyage Home, and Final Frontier tries to further explore late middle age (and fails) and then you have Kirk work out his prejudice against Klingons with VI. Also along the way you have Sulu eventually gaining command, Checkov moving to the Reliant and back, Spock dying and coming back to life, etc... However, I'm not sure how much most of the TNG characters change between the first TNG movie and the last. Even Picard seems to stay pretty much the same by Nemesis. Geordi gets bionic lenses but doesn't really change much as a character. I think that static nature of the characters hurt the films. In comparison, All Good Things felt more like a movie because it was Q forcing us to review all that the crew had experienced from Farpoint onward. You got some sense that at least within the span of the series that things did change or something important was "proven" about the human condition.
I don't think Insurrection was all that bad, it needed to be longer but I think for the most part it holds up for what is in reality a "B" movie by today's standards.
The discussion on the aesthetic appearance of the Enterprise D vs E is mostly moot. Some people will like/dislike one or the other, some will like both, it's mostly a matter of opinion. I liked the D better, but the E didn't really bother me. There's nothing to prove my position save my personal likes and dislikes and they won't apply to everyone else. I will agree that the Enterprise D's death was very unsatisfying. It definitely could have been done better, but since this is a thread about Insurrection I won't get off into that. Did Insurrection kill the TNG movie franchise? In a word, no. Sure, I'll agree it was the worst of the four TNG movies, but the fact that Nemesis(however you feel about it) was made a few years later, indicates the franchise survived. Honestly I say Insurrection could have been the most kick ass movie ever and Nemesis still would have been the end of TNG's run. Even if it wasn't a flop. The cast was simply getting too old and it was showing. If Insurrection and especially Nemesis did better, Enterprise may have benefited from that with better ratings however.
Lack of interest I'd say, whether or not Insurrection had any part of that is difficult to say. Perhaps the tide was already turning. I don't mind Insurrection but it is too small for the big screen IMO. I don't really know how they justified the story.
Regarding starship destruction and replacement, I absolutely hate that! I actually feel quite distanced from The Final Frontier and The Undiscovered Country with the Enterprise A.
What made Insurrection fail was that the villians were 2-dimensional. They were shown as evil since they dealt with banned weapon systems. It also wasn't clear why were they sick and what had caused it. Were the Sona their own race and how did they build up their own fleet after being kicked out of "Eden"? Also the CGI on the Ent-E didn't look as good as the model did in FC. By Nemesis even the Bridge looked cheesy. In FC the Bridge looked great. My feelings are that Insurrection didn't kill TNG trek. The mistakes done after the series ended was coming out with Generations right after the series ended. It felt like a souped up TV episode with Bipolar uniforms. I believe that they should have waited until FC to come out with their first feature film. having the Ent-D destroyed by the Borg would have justified Picard's behavior in FC (especially his speech to Lilly about not wanting to loose the Enterprise again). In the end there was TNG fatigue with the movies not doing justice for the series. The movies were just not at the epic level of Star Wars.
After "Space Rambo Meets Vampirella" it was nice to see a return to TNG, even if the second half had to degenerate into the same dumbed-down action drivel again to get the suits' support. It's the abomination that came after it that really drove the stake through TNG's heart (apparently Akiraprise with its time-travelling space vampire Nazis wasn't deemed bad enough).
Like skipping Star Trek V in marathon of the original 6 movies, it doesn't hurt the flow of the story.
Would you rather the next movie be about the crew in desk jobs? It's Star trek... they kind of need a spaceship to make it work.