@sallyhinch I found a metaanalysis on "fear appeal theory" in my collection; it's not specifically about bikes/inactivity so probably not the one you were looking for, but it reviews the general issue of health warnings (e.g. drastic images on tobacco packs). Short message: threatening messages have to be combined with a positive way forward.
Peters, G.-J.Y., Ruiter, R.A.C., Kok, G., 2013. Threatening communication: a critical re-analysis and a revised meta-analytic test of fear appeal theory. Health Psychology Review 7, S8–S31. doi:10.1080/17437199.2012.703527
I don't really have time to read it in detail just now, but my vague understanding (as a non-psychologist) is the following.
"Fear appeals" are threatening or fear-arousing persuasive messages. The actual behaviour change should depend not only on the communicated threat level (e.g. very graphic cancer images on cigarette packs) but also on the "efficacy", i.e. if the recipient knows what she/he can realistically do in practice to reduce the threat. Efficacy depends not only on the message, but also the recipient's personality, values and knowledge.
These two factors should in theory interact. If there is high efficacy, then a higher threat level should increase behaviour change. However, if the efficacy is low then a higher threat level does NOT increase behaviour change. Instead, it leads to defensive reactions, e.g. doubting the evidence.
The authors of the meta-analysis write that there isn't a consensus whether this theory is actually correct, as different studies come to different conclusions, but their meta-analysis indicates that some studies have methodological problems and that the better studies indeed show the interaction between threat level and efficacy as predicted by fear appeal theory.
This picture gets a bit more complicated by temporal discounting (the psychological effect that costs/benefits influence our decisions less if they are far in the future).
Tobacco advertising is an example. If the efficacy is high, i.e. there is also information available on how to actually stop smoking, then in theory graphical images of lung cancer (high threat level) should work better than plain word warnings. However, if the recipient does not know how to stop smoking, or if it seems really difficult to do, they will more likely react defensively ("it won't affect me", "you can prove everything with statistics", "Churchill also smoked" etc.). Add temporal discounting (health effects are far in the future, but the hard task of behaviour change is now) and you are in trouble.
Regarding climate change (which is why I was interested in this article), we can push out as much information about the projected risks as we can. But people react defensively if they don't know how they can actually do something about it (What's the point if China/USA don't stop?), or if it seems a complicated and difficult thing to do, like changing a big part of their lifestyle (How can I get along without car? Are we supposed to live in caves again? etc). They might then doubt the evidence for climate change or assume that we will always have the resources to cope with it. And again, temporal discounting: the risks are perceived to be far in the future, the "costs" of changing our lifestyle are now.
Same with inactivity. You can tell people as much as you like about the risks, but if the solution (doing sports) appears (to the individual recipient!) really unappealing then you get defensive reaction, not behaviour change. Utility cycling, in this framework, can be a solution with high efficacy, as it's fairly low effort and brings immediate benefits too. Immediate benefits are good because they counteract the temporal discounting effect. The "weirdness" and perceived road dangers that you mention then reduce the efficacy of this action, in my understanding.