The chord progressions you want will depend on the sound you're after. You can do quite a lot of creating songwriting without ever straying from the basic "in-key" chords: I-ii-iii-IV-V-vi-[vii], and if the Top 40 charts are any indication, you can make a pantload of money with very "safe" chord progressions. However, if you don't want to be safe, and want to create new sounds,
Beyond this basic framework, there are some old standbys which work very well in particular places:
- Suspensions. These work very well as variations of the naturally-major I, IV and V chords of a key. In any of these, a suspension will want to resolve back to the major chord it's based on, and so these can provide needed movement within a "block" of this chord; suspend it, then resolve back to the major. A suspended I chord, with the fourth and octave, will naturally want to resolve to the IV chord which also has these notes, while the suspended V chord, with the fifth, octave and second, will strongly resolve to the I chord.
- Sus2 or Add9 chords. Similar to sus4 chords, sus2s work well as variations of I, IV and V, and resolve similarly to their upward-suspension counterparts. They have gradually become accepted as a minor source of "leading dissonance" in chord progressions; this dissonant major second becomes a more consonant note of the next chord, usually either the root or third. The IV chord works very well as a sus2/add9 variation, because the second of this chord is the fifth of the native key, and so can resolve very naturally either to the V or the I chord. Guitar players will commonly use a Cadd9 chord fingering instead of the open-position C because the transition is very easily done from the root G chord.
- Dominant 7th chords. These work very well as a variation on the V chord, because the dominant seventh of that chord is the perfect fourth of the overall key, so the entire chord will naturally resolve to the tonic. For instance, A7 in the key of D has the notes A-C#-E-G, and with the seventh, second, fourth and fifth of the key in one chord, every note wants to resolve toward the tonic chord. Dominant sevenths also work well in the vi position; in the key of G, an Am7 has the notes A-C-E-G, and so will resolve handily to the IV chord of C-E-G. It's less often seen as a replacement for the IV chord, because the dominant seventh of the chord ends up being a minor third from the tonic (C7 = C-E-G-A#), but for that reason, if you want to transition from major key to minor key without changing your root note, V-IV7-i is a good way to do it.
- Major II and III chords. The major II chord is theory-based as the "V of V". In the key of G, D is the V chord, then in the key of D, A is the V chord. Progressing I-IV-V-II, then using the II as the new tonic chord from then on, is an easy way to change keys. The major III chord is a definite rule-breaker because it augments the fifth of the native key, and you generally try to avoid modifying the root, fourth and fifth of a key in your progression. However, III-IV-V (for instance F-G-A) forms a powerful progression leading back to the I chord (D).
Beyond these basic tricks, look at the chords as a movement of voices. Figure out where you want to start and end up, write the notes on a staff (it does help to be able to read music, even for instruments like guitar and bass that rarely use it), then see where each note of that chord can move to transition from one to the other. Most times, your "transition" chord in the middle will only involve one voice moving half a step in either direction, then the chord will resolve as one or both other voices in the triad make a similar move. Don't forget your inversions; if the movements seem too large, you're probably trying to move to the wrong inversion of the chord; drop the top note of one chord by an octave until the movements work better.
EDIT FROM COMMENTS: OK, so you want to know a little more about chords that just don't seem to belong at all. Your specific example is Cmaj7-G7-A7-Fmaj7.
As I stated, try to look at chords as a movement of voices. Let's take these four chords (each one is displayed in a column with the root on the bottom):
B - F - G - E
G - D - E - C
E - B - C - A
C - G - A - F
First off, you'll notice that these four chords all share a lot of notes in common. The key isn't obvious as there's no obvious tonic triad, but C major is a good guess, because both the V7 and vi7 chords are in the progression, and as I've said these two resolve strongly to more consonant chords (V7 to I, vi7 to IV) which also happen to be present in this progression (in their maj7 variants).
Let's rearrange these chords a bit:
G - F - G - F
E - D - E - E
C - B - C - C
B - G - A - A
All four chords have the same notes, but I've inverted the first and last chords. The Cmaj7 chord is now in its first inversion; the top note, the major 7th, has been dropped into the bass. Now, the movement of each of the four "voices" is reduced; The G moves one whole step down to F, the E moves one whole stepdown to D, the C moves one half step down to B, and the B moves a major third down to G. The second and third chords involve similarly small movements, all a half or whole step up. Then, the move from A7 to Fmaj7 is made easier by writing the Fmaj7 in its "third inversion"; the A, C and E are all dropped into the bass putting the root note on top. Now the only move from A7 to Fmaj7 is for the top "voice" to drop a whole step from G to F.
If we wanted, we could make the progression from first to second even closer:
G - G - G - F
E - F - E - E
C - D - C - C
B - B - A - A
Now we've put the G7 into its third inversion as well; as a result, when moving from the Cmaj7, every voice moves only a half step, and in addition, the progression moves upward, not downward; if you want the progression to sound happier, you generally want the notes in it to be moving upward.
Let's make some more changes:
G - G - A - A
E - F - G - F
C - D - E - E
B - B - C - C
Now every chord is in some inverted form; the third chord is in third inversion, and we've shifted the last chord to the second inversion. No voice moves more than one whole step at a time, and except for the last chord, whenever there's a movement, it moves upward. This form also shows one more interesting thing; if you look at the third chord, the A7, you'll notice that in this inversion, you have the tonic, third, fifth and sixth in the key of C major, which is what I'm supposing the overall key of this progression to be. So, the vi7 chord is also the I6 chord, which is part of why it works so well.
Last change:
B - B - C - C
G - G - A - A
E - F - G - F
C - D - E - E
Now we start with a definite tonic chord, with the Cmaj7 back in its basic configuration with the C in the bass. G7 and A7 are in their second inversions, and Fmaj7 in its first inversion. This variant of the progression keeps the upward motion, but begins on the tonic instead of ending on the tonic as in the previous variant. Which one's better? Depends on what you want the progression to do.
Try plunking all of these out on a piano and pick out the differences in sound. Also, try other combinations that move the voices in opposing directions, so the chord "opens" or "closes" as it progresses.
As for your question about "resolving", understand that the I chord, the tonic, is the strongest, "most-resolved" chord in any major key. It doesn't get any more fundamental than the tonic, at least to Western ears. The two "second level" chords are the IV and V chords. The IV chord has the root note in it, and the 4 and 6 naturally want to move downwards, "resolving" to the I chord's third and fifth. The V chord is built on the fifth, and has the major seventh which is only a half step from the tonic and so desperately wants to resolve upwards to that root note (which is why the suspended V chord works; it gives you that tonic note), while the 9th, aka the second, can either resolve downwards back to the root, or upwards to the third, either way you end up with a perfect fifth based on the tonic, or with the tonic triad.
Beyond that, most other chords will naturally want to resolve towards one of these three major chords. The ii chord has the 2, 4 and 6, and so can easily resolve to the IV chord (4,6,8). The vi chord (6,8,3) will resolve to the tonic chord, as it has the root and major third, and just needs the sixth to move down. The iii chord (3,5,7) will easily resolve to the tonic as well, especially if inverted so that the chord's fifth (the key's seventh) is placed in the bass.
The seventh chord is an oddity in the harmonized major scale; it contains both of the "minor third" intervals that are possible in the major scale, and so forms a diminished triad. Add the sixth of the key and it becomes a dominant seventh in the chord, making the full chord half-diminished, a little less dissonant to Western ears by giving it the ii triad's elements, so now the chord can resolve easily to the ii chord by removing the root (the seventh of the key), or directly to the IV chord by converging the seventh and the second into the tonic. The diminished triad itself, though it has that major seventh of the key, actually doesn't resolve well to the tonic directly, because it would require all three notes to move upward; the fewer the notes that have to move to go from one chord to another, the more easily those two chords resolve to each other. However, this progression can work as part of a three-chord progression.