I think the best use of artillery (and bombers to some degree) is eliminating the AI's military forces. There's a twofold issue going on here:
If you are powerful enough relatively to roll through the AI's cities using fast unit alone, and taking minimual losses, then there probably isn't much need for artillery. I'd tend to agree that you're playing at too low a level if that's the case then.
If you aren't. Then artillery is a huge asset, no matter how much you think you need it, or how big the difference is.
Unless you are specifically fighting a fast war (built up units ahead of time, start war, hit objectives, call for peace), you have to really consider time to build units in the equation. Artillery is a force multiplier. Sure. It make take me 2 more turns to get that artillery into place, but if it means that I lose 0 or 1 unit taking the city instead of 10 or 12, I can move on to the next one that much faster. I'm happy with moving at an average of one square a turn through my opponents territory. It still means that I'll get taking 2-3 cities a turn given the average distribution of the AI's cities and assuming I've got multiple fronts moving. It also allows me to maintain a steady progress through his area. I can rotate my attacking units out as needed to repair them. If you get the balance of forces just right, you will have a continually moving force that is very fluid. As your units get worn down, previous ones you've sent back to get healed up (or fortified in place), will come back at full strength. IMO, that's the most optimal way to use your forces.
I don't like to throw away units. It's just that simple. When you come right down to it, the entire game of Civ3 is about attrition. How much does it cost you to achieve something in relation to your opponents? It's all about making any action cost them more then it cost you. The payment isn't in gold, but in turns of civilization productivity. Thus, making your civilization more productive is equivalent to using your units more efficiently. More importantly, the reverse is true. If I lose 10 units that each took 5 turns to build on average, and I've got 20 cities in my civilization, then that combat just cost me 2 and a half total turns worth of my entire civilizations output. How many artillery can you build with that alone? And that's just one hard battle. How many will you fight during your wars? Every unit that you build and lose in war is a quantity of production that could have been used to build a city improvement, or a wonder, or wealth, or science, or any of a number of other things that help you win the game.
In addition to taking cities, artillery is fantastic for whacking AI units in kill zones. The AI is pretty brain dead sometimes. He will always counter attack if possible, but isn't very smart about how he does it. I once had a war where I had beacheads on two parts of the AI's continent (north and south). For some reason he got fixated on my southern area, even though it was much farther from his core area (maybe he detected me there first? I have no idea...). In any case, he'd send stacks (and by stacks I mean 20-40 units in a stack) at me. Moronically, he'd send them through the mountains and into my area.
What you do is reserve some defensive units to hold your rail spots, but otherwise, let his units into your area. Keep workers with your defending units to keep building rails to where you need them. Then you block off their advance. The AI just wont attack even small stacks if you've got a better defending unit then his attacking unit (which worked great for me since I deprived him completely of oil in this game). So he'd kind of mill around trying to work around my stacks to get to my cities. Meanwhile I'd move my artillery on rails and target just one or two stacks a turn. Then I'd use my MA along the same rails to move and attack twice with each MA. End result was that I could eliminate 20+ infantry and marines and usually not lose a single MA in the process. Meanwhile, my northern forces just rolled through his core area, but that's a different story...
The moral is that if you can figure out where the AI will counterattack you, you can use your artillery to maximum effect eliminating huge numbers of his units with little or not cost to yourself. So even if you're engaging a fast war with your offensive units, artillery is still incredibly useful for just generally weakening him. The AI's strategy pretty much always seems to be to put just a few units in defense and throw the rest at your cities during war. He takes very little effort to attack your units in the field. Use this to your advantage. Let him come right into your guns if you can. And even on attack, remember that all goals in Civ are long term. If you can afford to throw away units on attack, then the game you are playing probably isn't that challenging anyway. In a tough, hard game, every single unit matters. Every single bit of production matters. In those games, you'll find that units like artillery literally make or break the game.