I do know that being in the preferred emotion for a task will increase the performance for that task, and being in the "Very" mood for that emotion increases it even more. Your sim will definitely increase skill levels faster / finish a task faster / have better results / gain job performance faster. They also affect social and romantic actions.
I think that the game factors in the mood at the beginning of a task. If the sim finishes the task at the same sitting, it's a higher chance to get an excellent result. For instance, a sim with a level 8 writing skill starts writing a children's book while Very Inspired, but when they finish the inspired mood has expired - they seem to keep the faster skill leveling through the entire process & have a much better chance of writing a bestseller. However - if the sim stops writing for some reason, and goes back to finish it later without the Very Inspired mood, the skill leveling will be a little slower and the chances of a bestseller drop a little. So I make sure to get the sim back into the highest mood factor before restarting a task.
Job performance and promotions used to be tied to a particular mood for that job, but they changed that a while ago and now any mood will increase job performance. I will frequently use Very Energized as the preferred mood for a sim before they go to work. It slows down their needs decay and being "Very Energized" increases their job level and promotions just as well as any other. They will normally lose the moodlet halfway through a workday, but the effects of the mood seem to apply until they come home. This is the main reason I think the beginning of the task is the most critical.
There is some great information on emotions and their effects in Carl's Guide to How Moods Work. It was written before the change to job moods, so some of that is no longer relevant, but most of the info is up-to-date. (I highly recommend the entire Carl's Guide website as a resource. It is chock full of information and videos about almost every aspect of TS4.)