The biggest reason horses go isn't vulnerability - a horse and rider aren't really more vulnerable to shellfire or artillery than infantry, if anything, they can cross a beaten area more quickly.
The biggest reasons that push the cavalry out of service are training, mobility, and cost.
Training cavalryman is a difficult, time-consuming, and expensive task.
Men needed to learn how to take care of their horses. Troops spent hours every day doing grooming, basic veterinary care, and inspecting their horses for common ailments. For men who hadn't been around horses much, that could be a daunting task
Obviously, troopers had to learn to ride. Now, some men had experience riding horses - the Kalmyks of the Red Army, for example prided themselves for being "born in the saddle." But a great many men hadn't been a horse once in their lives. They had to learn from scratch. Once they could ride, they had to learn to ride in formation with other riders. After that, troopers needed to learn tactical maneuvers like transitioning smoothly from a column to a line. They had to be recognized and respond to bugle calls.
When it comes to weapons, a great deal of time and effort went into training a trooper. Into WWI, armies trained their cavalrymen to use the traditional *armee blanche* of saber and/or lance while mounted. These are heavy, unnatural weapons and it takes a great deal of intensive drill to get good at using them from the back of a charging horse. By the late 1800s and early 1900s, Cavalrymen fought dismounted more and more often - that meant they also had to master the use of the carbine or rifle. In many cases, cavalrymen also learned to use pistols while mounted and dismounted.
As for mobility, trucks and cars could go further, faster, and longer. A group of horseman can go about 50-60 km a day. A truck doing 30 km over decent roads can do that in two hours. After a hard day's riding, the horses will need rest, fodder, and water. Meanwhile, the truck just needs a full tank of gas.
In 1931 Army Chief of Staff, General Douglas MacArthur expressed a view shared by more and more officers:
"The horse has no higher degree of mobility today than he had a thousand years ago. The time has therefore arrived when the Cavalry arm must either replace or assist the horse as a means of transportation, or else pass into the limbo of discarded military formations.”
Although horses were better than motor vehicles in some ways (didn't consume precious fuel, better cross-country mobility, less reliant on roads), they were also uniquely worse in others (can die of exhaustion if pushed too hard, require large amounts of water and fodder - hard to get in winter time, deserts, etc). Horses might sometimes have better tactical mobility than trucks, but their operational and strategic mobility were far worse.
You can see these tradeoffs illustrated in the Soviet experience and their evolving view of cavalry.
In 1935, the Red Army had 16 cavalry divisions. In fact, the Red Army's cavalry branch had been expanding substantially since 1925.
The Red Army placed enormous faith in its cavalry units. In 1934, People's Commissar for War Kliment Voroshilov even suggested it was treasonous "wrecking" to question the continued utility of cavalry:
"First and foremost it is necessary to put an end once and for all to the "wrecking" theories on the substitution of men for horses, on the "withering away" of the horse."
Although it seems anachronistic to suggest horses had unquestionable value on a modern battlefield, Red Army cavalry advocates had a point. Cavalry had enjoyed considerable success in the Russian Civil War. Furthermore, Russia's sparse network of rough roads and rough terrain meant trucks and tanks were often less mobile than tanks. Gazing horses could also partially sustain themselves, while gas-guzzling vehicles could not. As John Ellis writes:
Terrain and climate also enhanced [the Soviet cavalry's] value. The horses, shaggy little Kirkhil ponies from Siberia, could withstand temperature of 30 degrees below zero and they could easily negotiate the dense forests and muddy roads that often ground tanks to a halt.
German general Eric von Manstein wrote admiringly:
"A Soviet cavalry division can move, in its entirety, a hundred kilometers in a night – and that at a tangent to the axis of communication."
After Operation Barbarossa smashed huge portions of the Red Army's motorized units, the Soviets expanded their cavalry branch substantially. The Soviets didn't have enough trucks to rapidly rebuild their motorized forces, so cavalry were seen as a replacement with similar mobility. By early 1942, the Red Army's cavalry branch added 87 new cavalry divisions. These divisions mounted several large-scale raids behind German lines during the early years of the war.
The raid of the 8th Cavalry Corps on Debalzevo in February 1943 produced this memorable tally of destruction:
"The raid into the rear area of the German Voroshilovgrad grouping cost the enemy a total of more than 12,000 soldiers and officers lost and 28 tanks, 70 motorcycles, 50 guns, 35 mortars, 54 machine guns, 2 armored trains, 1 fuel train, 20 locomotives, 1 train with tanks, 3 trains with vehicles, and 1 train with aircraft destroyed. In addition, 6 communications centers were destroyed, 3 railroad bridges were blown up, up to 30 warehouses with ammunition and foodstuffs were burned, and the main rail lines leading to Debaltsevo were blown up in 56 places."
Only once Soviet industry produced more tanks and trucks and Lend-Lease supplied the much-loved Jeeps and Studebaker trucks would most of these cavalry units be dismounted and mechanized. By late 1945, the Soviets only had 26 cavalry divions left, all of which is disbanded or mechanized after the war.
Other armies tried to have the best of both worlds by combining cavalry with trucks. In the late 1920s, the U.S. Army experimented with giving cavalry units trucks with horse trailers in an effort give their cavalry better strategic mobility. The horses and men would ride trucks to the theater of war, then disembark and ride into combat, so the theory went.
After initial tests, the U.S. Army's 4th and 6th Cavalry Regiments go truck-towed horse trailers to give them better strategic mobility. All U.S. Army cavalry units also gave up most of their horse-drawn wagons for trucks. However, the experiment with horse-trailers didn't last too long. The Army's cavalry units were all turned into infantry or mechanized units by the early 1940w.