Answer: People have been having trouble coming up with great melodies for a long time. It's not every day that anyone comes up with a memorable tune, so don't feel bad about that.

The rules are just style suggestions - they can be very useful in helping you to get certain good effects while avoiding bad ones. And one of those helpful rules is that an accented passing tone is best resolved by moving stepwise to a chord tone. And it's OK to use it frequently, especially if it resolves downward. For some reason downward resolution of the dissonance feels satisfying.

A closely related and emotionally satisfying melodic effect is the suspension, in which a note becomes dissonant only because the harmony shifts beneath it. Then the note moves downward to agree with the harmony, after having had that moment of disagreement right on the strong beat. The key in both of these is that the dissonance comes in a rhythmically strong place and resolves to the prevailing harmony on the weak part of the beat.

Another important thing is repetition - not necessarily literal repetition, but varied - repeated use in a melody of a certain rhythmic or melodic idea, varied in some way. A good example is the downward second at the end of the first three phrases of Paul McCartney's "Yesterday" (yes, I know, but hey, it was a hit, and it does show a lot of technique):

You'll see that each of those phrase endings also features a non-chordal tone resolving downward by step, the most important of which is the one that comes after the upward scale on the E major chord. No one will accuse McCartney of knowing anything about counterpoint or resolution of dissonances, but this came naturally to him. He had good melodic instincts. Understanding how it all works is good for the rest of us.