Copy email to the clipboard with Outlook VBA

Question

How do I copy an email to the clipboard and then paste it into excel with the tables intact?

I am using Outlook 2007 and I want to do the equivalent of

"Click on email > Select All > Copy > Switch to Excel > Select Cell > Paste". 

I have the Excel Object Model pretty well figured out, but have no experience in Outlook other than the following code.

Dim mapi As NameSpace
Dim msg As Outlook.MailItem
Set mapi = Outlook.Application.GetNamespace("MAPI")
Set msg = mapi.Folders.Item(1).Folders.Item("Posteingang").Folders.Item(1).Folders.Item(7).Items.Item(526)

Solution 1

I must admit I use this in Outlook 2003, but please see if it works in 2007 as well:

you can use the MSForms.DataObject to exchange data with the clipboard. In Outlook VBA, create a reference to "Microsoft Forms 2.0 Object Library", and try this code (you can of course attach the Sub() to a button, etc.):

Sub Test()
Dim M As MailItem, Buf As MSForms.DataObject

    Set M = ActiveExplorer().Selection.Item(1)
    Set Buf = New MSForms.DataObject
    Buf.SetText M.HTMLBody
    Buf.PutInClipboard

End Sub

After that, switch to Excel and press Ctrl-V - there we go! If you also want to find the currently running Excel Application and automate even this, let me know.

There's always a valid HTMLBody, even when the mail was sent in Plain Text or RTF, and Excel will display all text attributes conveyed within HTMLBody incl. columns, colors, fonts, hyperlinks, indents etc. However, embedded images are not copied.

This code demonstrates the essentials, but doesn't check if really a MailItem is selected. This would require more coding, if you want to make it work for calendar entries, contacts, etc. as well.

It's enough if you have selected the mail in the list view, you don't even need to open it.

Solution 2

I finally picked it up again and completely automated it. Here are the basics of what I did to automate it.

Dim appExcel As Excel.Application
Dim Buf As MSForms.DataObject
Dim Shape As Excel.Shape
Dim mitm As MailItem
Dim itm As Object
Dim rws As Excel.Worksheet
'code to open excel
Set appExcel = VBA.GetObject(, "Excel.Application") 
'...
'code to loop through emails here       
Set mitm = itm
body = Replace(mitm.HTMLBody, "http://example.com/images/logo.jpg", "")
Call Buf.SetText(body)
Call Buf.PutInClipboard
Call rws.Cells(i, 1).PasteSpecial
For Each Shape In rws.Shapes
    Shape.Delete 'this deletes the empty shapes
Next Shape
'next itm

I removed the logo urls to save time, and when you're dealing with 300 emails, that translates into at least ten minutes saved.

I got the code I needed from a TechRepublic article, and then changed it to suit my needs. Many thanks to the accepted answerer of this question for the clipboard code.

Comments

[Arlen Beiler]

That doesn't say how to copy it, which is part of what I was wondering, hence the downvote.

[shen]

Well ok fine, but putting text into a dataobject is trivial. i see that I missed that part of the question. bad assumption on my part, however the interesting code is getting the text to begin with.