What is motivation?
In order to figure out where motivation comes from, we have to figure out what motivation even is. Though I am reluctant to commit to a formal definition of "motivation," this much is certainly true: Motivation is a mixed cognitive-emotional state. The cognitive component involves an appraisal of the desirability of doing or having something. The affective component is felt viscerally, from mere inclination to burning pressing desire.
Indeed, the dictionary definition for "motivation" clearly encompasses both the cognitive and emotional aspects of the phenomenon:
"willingness to do something, or something that causes such willingness"
"enthusiasm for doing something"
"the need or reason for doing something"
"Enthusiasm" is affective; "need" and "reason" are cognitive, "willingness" can be read as either or both.
Bottom line: motivation is sometimes affective, sometimes cognitive, sometimes both. Don't forget!
Where Motivation comes from?
That's enough background to tackle the motivating (har har) question today: Where does motivation come from?
Since motivation is a mixed cognitive-emotional attitude towards some object, it comes from the places that other cognitions and emotions come from. What are those?
Motivation sometimes comes straightforwardly from the body, as a kind of somatic driver of survival- and reproduction-relevant behaviors. The most appropriate way to explain this kind of motivation is in terms of neurotransmitters and such. The clear cases of somatic-effective motivation are towards things like palatable food and attractive mates.
Now, the mind and body are not totally separable. Yet it also seems that, in some meaningful sense, motivation can be born of the mind. For instance, someone thinks, remembers, or learns something and catches a wind of motivation.
You might hear about an opportunity, like a job, and decide to apply for it. Or you might see something for sale and decide to buy it. These are emotional processes too, but they have clear rational, "system 2" components.
But motivation's mixed character makes things really messy. You might learn a new terrifying statistic about the health effects of smoking and feel newly motivated to quit. But that won't make the deep-seated, chemically-mediated motivation to light up disappear immediately.