Whenever 20th century cavalry comes up, it often gets confused with mounted infantry. So let's clear that up.
Cavalry is trained to fight from horseback using pistol, sabre, carbine, lance, and the horse itself. Its primary purpose is cross open terrain quickly, crash through the enemy line, and cause disarray. If they can do this from an open flank, rather than charging straight into the guns, so much the better.
Mounted infantry are different; they ride their horse to the battle, dismount, and fight as normal leg infantry. The advantage is mobility, being able to move quickly from point A to point B to surprise the enemy or as a mobile reserve to respond to an attack. They're no different than truck, ski, or bicycle troops... except maybe it's a bit more romantic.
There was far more mounted infantry in WWII than there were cavalry. Mounted infantry often gets confused with cavalry.
Why a horse? Most WWII armies were not fully mechanized. With the exception of the US, they did not have the automotive industry to supply their whole army with vehicles. Infantry still walked. Artillery and supplies were horse-drawn, particularly in the German army.
But in WWII, mobility was key. Getting firepower from point A to point B as quickly as possible could win the day, and that includes infantry. If you don't have enough trucks anything else will do: rail being the first choice, but also motorcycles, skis, bicycles, horses, or sitting on a tank. Mounted infantry made a lot of sense.
With that out of the way, on to the actual questions...
Many armies, including the U.S. Army, maintained some horse-mounted cavalry leading into the war.
I will put forward three reasons for this: the US Army likes its traditions; the US Army was woefully underfunded at the start of WWII; the US Army had no experience in modern war. This all adds up to the fiscally convenient delusion that horse cavalry was still a good idea.
The budget for the US military was slashed after WWI and, with the US intending to remain neutral, only rose very slowly after Germany invaded Poland. Tanks were new and expensive and where was a lot of debate as to how they could be used even after Germany showed the world how it's done. As a result, the US was well behind in tank and aircraft development. The M3 Lee, the first US tank that could be said to be worth a damn, was a stopgap design to shove a 75mm gun into a tank and it only started rolling off the production lines in August 1941 two years after the war started.
The rapid build up of the US Army meant that National Guard units were incorporated into the army. These units, supported by individual US states, had wildly varying budgets and readiness. Horses were a very expedient way to give your underfunded weekend warriors some mobility, especially if they lived in an area which already had a lot of horses.
Again, this is my supposition.
Under what tactical situations during World War II were horse-mounted cavalry units still effective? If the opposing side included armor or multiple machine gun teams, would that have discouraged commanders from employing the horse-mounted troops?
Again, let's be careful to not mix up cavalry with horse-mounted troops. Horse mounted infantry (or cavalry) could be used for a wide flanking maneuver around an enemy strong point to attack from an exposed flank or rear. Their increased mobility over leg infantry still served a role.
Something else to consider is most early WWII infantry were still equipped with bolt-action rifles little changed from WWI. The Poles and Germans and many other armies used the ubiquitous Kar-98K. The British had the 1914 Enfield. The Soviets had the Mosin-Nagant. These typically had a 5 round fixed magazine fed with a stripper clip.
The first self-loading rifles were just being adopted. They were expensive, could malfunction, were sensitive to the ammunition quality, and the logistics folks and bean counters wrung their hands about all that ammunition soldiers might fire. The Soviets had the SVT-40, but stopped production in favor of cheap, stamped sub-machine guns. Only the US adopted a self-loading rifle, the M1 Garand, as their service rifle and had enough to put one in the hands of nearly every soldier.
Sub-machine guns were becoming popular, but they fired small pistol cartridges and had a short range. They were designed as a very cheap assault and close-combat weapon to give the average infantry squad some brutal extra firepower, but they were not a replacement for a light machine gun on the defense. The Soviets had the PPSh-41, the Germans had the MP 40, and the US had the breathtakingly expensive Thompson.
Point is, the firepower of your average early WWII infantryman was fairly limited.
Then there's the mobile nature of WWII. On the Western Front of WWI, there was a continuous front from the Channel coast to Switzerland for years. Armies could build up elaborate defenses, multiple trench lines, and concrete bunkers. They could haul in dozens of artillery pieces, and emplace hundreds of machine guns with carefully thought out firing arcs. Because the line was continuous and so lavish, and the pace of WWI warfare so slow, there was no flank for cavalry to outflank.
In contrast, WWII was very mobile. The front moved constantly and was rarely contiguous (despite those nice lines drawn on the map). There were (almost) always gaps and weak points in the line to exploit. Fast moving mounted infantry could exploit those gaps... but probably not as cavalry. Instead they'd dismount and fight as infantry. Why? Because it was simply more effective. To quote Wikipedia, this is what a US horse cavalryman was supposed to do...
Mounted troopers would attack with their pistols; at the command 'charge', troopers would shorten their reins, lean well forward and ride at full speed toward the enemy. Each trooper would select a victim to his immediate front and bear down on him with his pistol extended at arm's length, withholding fire until within 25 yards.
Charge to within 25 yards of the enemy, and try to hit them with a pistol from the back of a charging horse. Good luck with that. Again, the US Army was pretty naive about what WWII was going to be like.
Where horse cavalry were useful was in the "pursuit" role: chasing a retreating and demoralized enemy who probably wasn't going to give much of a fight anyway. If you're a retreating soldier caught in the open by tons of angry horse bearing down on you with their riders firing pistols and carbines in your general direction, you're probably not going to put up much of a fight.
Cavalry could also be used in hit-and-run tactics against an advancing enemy, appearing out of nowhere, closing the distance rapidly, slashing through the enemy column, and then riding off before resistance can be organized. This was the role of the 26th Cavalry Regiment in the Philippines against the advancing Japanese.
That said, charging a machine gun nest was not necessarily suicide. It can be a valid tactic depending on the situation. Cavalry excel at closing the gap with the enemy very quickly. The less time spent getting to the machine gun, the less time spent getting shot at by the machine gun. The aggressive nature of US tactics might accept a few loses now to break the grinding attrition of a stalemate.
That said, while there were many trained cavalry units at the start of WWII, very rarely did they act as cavalry. Instead, they acted as mounted infantry using their horse as a truck. Most cavalry units transitioned to mechanized cavalry, same role, but with armored fighting vehicles instead of horses.
