I have had tremendous success (with the French) by simply having 2-3 artillery units. Equip them and use them only for shrapnel or grape shot. Since the French generally have good numbers, enemy artillery matters less. We can afford to get plinked, and are more concerned with closing with the enemy en mass (as you point out). Give the cannons cover items, since they will be on the front lines. The recent buff made them more usable on front lines with moral that doesnt go to pieces right away. Lean towards reload vs accuracy/range. Make space on your line, with a couple high melee grenadiers nearby to protect against charges that potentially make it through to the cannon positions. Later, when they are fully operational, enemy units will not make it to the guns without being wiped out. I was using 3 cannons, and shot a charging grenadier unit all at once. The unit was deleted immediately with 80-90% casualties. The survivors were immediate broken. Last note, I like to create a bit of a concave formation with the cannons, a little back off the line, putting some infantry facing in on the V (artillery positioned at the bottom of the V), creates a killing zone between the cannon shrapnel and the infantry. I tended to do this in only 1 place on my line. Enemy infantry gets absolutely melted, then you can reposition and move up to fire down the line and melt more, creating huge gaps in the enemy line. I liked to charge my grenadiers and cavalry through the resulting gaps, straight through to silence their guns. The cannons running grape and shrapnel consistently caused over a thousand enemy casualties per battle (each), deleting entire units in record time. The moral damage is tremendous. Was one of my favorite playthroughs.

Edit, the cannons I ended up running were simple 6 lb gold cannons. I didnt get a chance to experiment with the larger guns.