Metacafe is one popular video sharing site that has implemented this functionality. All videos in its Producer Rewards section can be downloaded directly to Nokia Video Manager. I have also implemented this functionality on All About Symbian.
There is now a Nokia Video link for each of the videos in our media section. Following / clicking on these links will launch Nokia Video Manager and automatically download the video in question ready for transfer to your phone. In order for this to work you must have the most recent version of Nokia Video Manager installed.

Example screenshot from our media section.
Add your own links
If you want have your own videos (e.g. those you have published on a blog) you can add similar links in the from of hyperlinks with the format nvd://www.url.com/video.mp4?itemID=124&itemTitle=Title. The format should be playable by modern S60 phones (e.g. H.263 or H.264)