Paste Special from Outlook

[Grit]

Using Outlook 2003 & Excel 2003 on Windows XP Professional

I want to take the text from an email, copy it into Excel, into one cell per
line, displayed in full, with no delimiters.

I am using 'Paste Special - Text' to accomplish this, however, Excel is
converting all kinds of things (spaces, EOL, etc.) to boxes. All of the test
is making it into the sheet, but with all of these boxes (look like special
characters) stuck all over the place.

Why is this happening and how do I get rid of them? I have also tried
'Paste Special - Unicode Text' with the same results.

 

[Steve Yandl]

You could use the subroutine below to possibly get what you want. It works
on the last received message in the Inbox but could be modified to select
specific messages. You will get a security warning about a program trying
to read Outlook message; you just need to approve the action for a minute or
longer. The sub could be expanded to drop the header lines and blank lines.

The sub basically causes the outlook message to be saved as a temporary text
file. The text file is opened and read line by line with each line being
placed in the next available cell in column A:A. When the reading is
complete, the temp file is closed and deleted.

______________________________________

Sub TextFromOLmsg()

Const olFolderInbox = 6
Const olTxt = 0
Const ForReading = 1

Dim R As Integer

' Determine row of first available cell in "A:A"
R = Range("A65536").End(xlUp).Row + 1

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

Set colMailItems = objFolder.Items

Set FSO = CreateObject("Scripting.FileSystemObject")
strFileName = FSO.GetSpecialFolder(2) & "\TempMsg.txt"

Set objItem = colMailItems.GetLast()
objItem.SaveAs strFileName, olTxt

Set objFile = FSO.OpenTextFile(strFileName, ForReading)

Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
Cells(R, 1).Value = strLine
R = R + 1
Loop

objFile.Close
FSO.DeleteFile strFileName

Set objFolder = Nothing
Set objNamespace = Nothing
Set objOutlook = Nothing
Set FSO = Nothing


______________________________________

Steve Yandl

 

[Ed Cones]

Grit

I've tried exactly what you've described, and it works well.

I open the email, Ctrl-A to highlight all the text in the body, Ctrl-C to
copy to the clipboard, then Paste-Special Text to copy it to Excel. It
creates a duplicate of the email skipping one cell down with each carriage
return/line feed. The only issue I'd have is that a long paragraph goes on
one line.

I've tried it on emails sent in both HTML and Rich Text with good results.
I am using Outlook 2003 and Excel 2003.

I wonder if you may be using a font in Excel that is causing trouble. FWIW,
I'm using Arial 10.

This post may be of no value to you at all, but I just wanted you to know
that what you're trying works for someone.