Yes, the Prophet Muhammad PBUH at one point in his life was affected by magic. It is clear that the hadith is authentic, however, it does not detract anything from the status of his Prophethood. Allah, may He be glorified and exalted, protected His Prophet PBUH and made him infallible before this spell was cast, during it and afterwards. From the ahadiths, it is known that the spell did no more than make the Prophet PBUH think that he had had intercourse with his wives when he had not done so and only other purely worldly matter, and had nothing to do with his conveying of the message at all nor morality/ethical issues. In fact, there would be no reason for Muslim scholars to authenticate this hadith if they had anything to hide. Rather, the hadith has been taught and well known by Muslims themselves and this is not something that was found out recently. Whoever wants to know more may refer to Fath al-Baari and Zaad al-Ma’aad.
However, one might ask, “Why would Allah allow this to happen to His Prophet?” There are many wisdoms that may be derived over why Allah would let His Prophet be afflicted by magic. First of all, it shows the Prophet PBUH was just a human and he shouldn’t be glorified or worshipped, and that he could fall sick just like anyone else could fall sick. Secondly, it shows Muslims that black magic is a real thing to watch out for. Thirdly, after this incident, the last two chapters of the Quran (Chapters 113 and 114) were revealed as one of, if not the greatest means of protection from black magic and evil eye. Nevertheless, it is clear that the black magic that was done on the Prophet PBUH was over his body and physical faculties, not over his conveying of the message and beliefs, as derived from the ahadiths.
And Allah knows best.