Will the version of Diablo 2 included with the Collectors Edition of Diablo 3 run on modern Mac OS X?

As of 2017, yes, Blizzard supports D2 on the latest macOS (10.12).

At the time of release, no, the included Diablo II + LoD did not run on OS X Lion (10.7). The soulstone drive edition was no different than Blizzard's latest digital download, which worked with Mac OS 10.3.9 through 10.6 Snow Leopard (and Windows 2000 through Windows 7) but not 10.7 Lion.¹ They also had no immediate plans to support it.^{2 3}

So, yes, the Collector's Edition USB soulstone shipped without modern support for D2, though it runs just fine in Parallels and Boot Camp on a MacBook Air, and now runs on the latest Macs.

According to Blizzard, they do not support the "Classic" version of the game anymore. This is the version that is found on the CD and would be the ones that run under Rosetta. As a solution, they provide a downloadable Intel based client to replace the CD version. My feeling is the version you will get on the USB stick will be that Intel client and not the older "classic" one.

http://us.battle.net/support/en/article/mac-error-classic-environment-not-supported