When I copy and paste this Excel range
into an Outlook email, it doesn’t look so good.
I thought I would use JoinRange to put some tabs in between the text. I used a function like this
A tab is ASCII code 9. It will show up as squares in Excel
but will turn into real tabs in Outlook
Still looks bad and now there’s quotes around everything. I didn’t know the ASCII code for tabs, so employed my usual technique for finding out. I get some words in a cell that contain the character I want, then use a formula to see what’s what.
The formula in G10 (text is in F10) is =CODE(MID($F$10,ROW()-9,1)). I fill that down and see the ASCII codes for all the characters. Sadly, I didn’t know how to type a tab in an Excel cell. Tabbing takes me to the next cell rather than inserting a tab character. So I resorted to VBA. From the Immediate Window
which is really dumb. As long as I’m in the Immediate Window, I could just use ?asc(vbtab), which returns 9. Is there a way to get a tab into a cell without knowing the ASCII code? I mean other than the way I did it.
While I was putting this post together, I discovered something. I write all my email in plain text rather than HTML. I don’t have Hello Kitty stationary or a need to put an image of my business card in my signature, so plain text works the best for me. I assumed that pasted range would look better in an HTML email.
Format email as HTML, then paste
Paste range, then format email as HTML
And the winner is: Paste range, format email as HTML, change format of email back to plain text
So this is a problem due to composing Plain Text e-mails in Outlook?
FWIW, copying a multiple column range in Excel then pasting as plain text into Lotus Notes produces the equivalent of your final result above. Nice to know Microsoft makes this much harder in Outlook. Just curios: there’s no Paste Special command in Outlook?
The way I stick Tab into a cell is no better. Start > Run > Notepad. Hit the Tab key, Ctrl-A, Ctrl-C, Back to Excel, Paste in the cell.
I too use Outlook, but I don’t use plain text, or HTML. I use Rich Text wherever possible. When I paste a range, it comes out in a grid – beautiful.
Using Excel 2007:
ActiveCell.Value = “this” & Chr(9) & “that” produces thisthat
Entering “this” & Char(9) & “that” produces thisthat
Entering this[tab]that (where [tab] = press tab key) in a text editor and copying and pasting produces this and that in two adjacent cells
Copying a tab character from a text editor and pasting selects two blank cells
Is this an Excel 2007 thing, or is there a setting somewhere?
I would never use this, but thought I’d see what I could do with paste special. The only way I could find was to use SendKeys (yuk!). And this also only works because I use Word as my email editor.
Private Sub MyMailer()
Dim rng As Excel.Range
Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem
Set outApp = New Outlook.Application
Set outMail = outApp.CreateItem(olMailItem)
Set rng = Range(“A1:C10”)
With outMail
.To = “test”
.Subject = “test”
rng.Copy
.Display
Application.Wait Now + TimeValue(“00:00:01”)
SendKeys “%es{UP}” & Application.Rept(“{UP}”, 4) & “{ENTER}”, True
Application.Wait Now + TimeValue(“00:00:01”)
SendKeys “%s”
End With
Finish:
Set outApp = Nothing
Set outMail = Nothing
Set rng = Nothing
End Sub
After a range is pasted into Outlook in both Rich Text or HTML, an option is created from the paste options button that appears in the lower right-hand side of what was pasted. From here you can select the match destination table style and it will create a grid, which can be edited per your preference. Plain text does not recognize tables, so that formatting is lost. As far as tabs are concerned in Excel, these already exist as the grid lines between cells (paste in Notepad to verify this,) so really is a moot consideration. On a side note, The Alt-Enter “space” does not have the same effect when pasted into plain text, but might lead to a solution to the original issue of placing a tab inside a cell.
I want to open an excel file on Excel startup. However, I do not want to save that file in XLSTART folder, nor do I want to open all the files in a specific folder. Is there an alternative? One of the alternative is to paste the shortcut in XLSTART. Are there any other ways?…Looking for some creative answers.