We use almost anything except the really sweet ones (Red & Gold Delicious are awful), mixed with about half tart (Granny Smith is excellent). We've found that for both sauce and butter, the key to a really nice, apple-y flavor is using a mix of types of apples instead of just one. Last year we used all heirlooms, including (as someone else mentioned) a good bit of winesaps.
We made 5 gallons of apple butter last year, so we're set for this year (thank goodness--it was a long, arduous process!), though we'll be making a few other types of butter including pumpkin and peach.
"This isn't life in the fast lane, it's life in oncomming traffic." -Terry Pratchett