Body of message to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to copy the body of a selected message to excel via a macro
(custom button). I received help a week or so ago and thought I had this
done, or so it seemed.

I have the following code:

Sub email_to_excel()
Dim xlApp As Excel.Application
Dim oWBook As Workbooks
Set xlApp = New Excel.Application
Set oWBook = xlApp.Workbooks
oWBook.Open ("SomeLongPath\exportfromOutlook.xls")
ActiveSheet.Cells(1, 1).Value =
Application.ActiveExplorer.Selection(1).Body
End Sub

which seems to run ok, except that
1) when I open the "exportfromOutlook" file I get the message that the file
is locked for editing (Excel was not previously opened) even after closing
Outlook and
2) when I choose read only, the body of the message is not there.

What is going on here?

Thanks
 
Does it work better this way:

Set oWBook = xlApp.Workbooks.Open _
("SomeLongPath\exportfromOutlook.xls")
oWBook.Sheets(1).Cells(1, 1).Value = _
Application.ActiveExplorer.Selection(1).Body
oWBook.Close True
Set oWBook = Nothing

Note that Excel isn't friendly to carriage returns, tabs, or long text
blocks. You can use this function to do some cleanup:

Function TextToExcel(MyString)
MyString = Replace(MyString, vbCr, " " )
MyString = Replace(MyString, vbTab, " ")
MyString = Left(MyString, 32767)
End Function
 
Take a look at your Task Manager and see how many instances of Excel you
have running. I'd venture to guess there is one for every time you ran your
macro without rebooting your computer. You need to save/close the workbook
and quit the excel instance and set all of your objects to nothing. The code
you have now is not doing any cleanup at all.
 
Hi Kabaka,

you also need to save and close the Workbook, for that in turn you need
a reference on it:

Dim oWb As Excel.Workbook
....
Set oWb=xlApp.Workbooks.Open ("SomeLongPath\exportfromOutlook.xls")
....
' Close and save the Workbook
oWb.Close true

' Quit Excel
xlApp.Quit

Please note: This is an Outlook newsgroup. I´m sure you will have more
questions about automating Excel. If so please visit an Excel VBA
newsgroup for more help.
 
Thanks guys, checking the task manager was one of the first things I had
done. I'm not too knowledgable in that area but what struck me as odd was
that Excel was not one of the programs running under the "Applications" tab,
but was listed several times in the "Processes" tab. After "ending" those
processes and making the code suggestions Michael provided, everything worked.

Thanks again!
 
I must say that I am impressed at how quickly so many knowledgeable people
came to my aid. Your function really cleans things up - Thanks!
 
Back
Top