Copy Outlook email body to Excel


A

AlexCS

Hi everyone,

I have been trying to write a macro in Excel to achieve the following:
- search for an item with a particular subject in the Outlook inbo
(there will always be only one item to match the search criteria sinc
the items are deleted after being processed)
- open that particular item and copy its body to a designated Exce
spreadsheet
-delete the item

So far, the closest I have come to my objective is with the below code
My problem is that it ends up pasting the entire body of the email i
one single cell in Excel (it would not accept pasting in a range) an
there is no possibility for me to format the data afterwards.

Please help if you have a minute, I honestly don't know what else t
try.

Many thanks,

Alex

Sub Outlook_Final()

Set olApp = CreateObject("Outlook.Application")

Dim olNs As Outlook.Namespace
Dim Fldr As Outlook.MAPIFolder

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)

For Each Item In Fldr.Items
If InStr(Item.Subject, "MACRO VAX 48634 REPORT") 0 Then
Item.Display
vBody = Item.Body
ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = vBody
Item.Delete
End If
Next Item

End Su
 
Ad

Advertisements

M

Michael Bednarek

I have been trying to write a macro in Excel to achieve the following:
- search for an item with a particular subject in the Outlook inbox
(there will always be only one item to match the search criteria since
the items are deleted after being processed)
- open that particular item and copy its body to a designated Excel
spreadsheet
-delete the item

So far, the closest I have come to my objective is with the below code.
My problem is that it ends up pasting the entire body of the email in
one single cell in Excel (it would not accept pasting in a range) and
there is no possibility for me to format the data afterwards.
[snip]

Where else do you want the macro to paste the email body text?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top