Players often have items in mind for their characters. And why shouldn't they? It really should come as no surprise that the sword and board fighter in a vampire centred campaign dreams of having a sun blade. However, often times the most memorable and loved items are the ones they never expected to have. The trouble is that many items can end up getting sold off, so how do we make items that players want to keep that they never wanted?
Selling Off Items is Fine
Having the party sell off excess magic items is fine. It makes sense, particularly in a high magic setting, that not all of the items they get from their enemies would find a use in the party. It also gives you a nice way out in case you miss. The worst case scenario is have a quest to sell it off and another small shortfall of money.
Sometimes players like surprises. Finding a really useful magic item they never considered is one of those times. There are a lot of cool magic items already in D&D so I've typically found that new players are often impressed by some of the classics. Veteran players have already seen those items so you need something else to get their sense of wonder going. There are also holes in the list of magic items that you don't always realize until running a campaign. You may want specific items to resist and kill illithid in your campaign, and it makes sense that a group whose goal is to hunt illithids will be trying to develop countermeasures.
Look At Things They Miss
There are a few things that I find my players often don't notice when thinking about useful items. One of which has to do with sight. If you are a human, being able to see at night can be a massive advantage if combat often happens at night. It may also be useful to be able to reveal undead with a magic lantern. Alternatively, building on the existing abilities of a class in ways the player didn't expect often goes over well. I remember one campaign where the fighter got a battleaxe that allowed them to use a modified version of the blink spell. Seeing them dark around the battlefield hitting people was something special and it became one of that player's favourite items.
Non Combat Uses
Players often think about items that give them an edge in combat. Combat is a tense moment where an extra +1 could have made a difference. However, there is more to magic items than that. This is especially true if you have new players that don't have experience with items like bags of holding. Such an item changes inventory management and makes things more convenient for players. However, it also changes the game. The players are no longer concerned about being attacked while they lead a cart of equipment through dangerous terrain. They no longer have to protect the horse pulling the cart, or devote players to push the cart if it died during an attack.
Some items have minor effects or only good for role-play. In one campaign I ran a ring was given to the party. It was a plane band, but it had the continual flame spell cast upon it. It diminished the need for lanterns, left the hand open for use since it could be worn, and when the party wizard learned the spell they made similar items for the rest of the party. Characters could easily stash it away and pull it out later when they needed light, which was very useful since they were often traveling at night. Role-play are similar but are even more minor in effect. Clothes that don't dirty or a candle that never runs out are handy, but they don't drastically change the game. The ring I mentioned earlier was used similarly to how a never ending candle could be used: to read at night. However, it was a favourite item since it saved on minor costs and was commonly used by the character since they would read or write often. They were a wizard after all.