Pasting Excel Ranges in Outlook

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

=PERSONAL.XLS!joinrange(A2:D2,CHAR(9))

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

activecell.Value = “this” & vbtab & “that”

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

Posted in Uncategorized

6 thoughts on “Pasting Excel Ranges in Outlook

  1. 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?

  2. 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.

  3. 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?

  4. 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.

    Option Explicit

    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

  5. 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.

  6. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.