I don't smoke in the house, but I'm lucky enough to have a room dedicated to "hobbies," where I store my pipes, in addition to computers I'm building and crap like that.
There's definitely an aroma present, but it's mild and I can't imagine anyone but the most determined tobacco-hater objecting to the smell.