Copying the whole e-mail body into excel

Not open for further replies.


Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
I am currently trying to figure out how to copy an e-mail text (from my newest unread mail) into excel. Manually it works just fine but I would like a code to do it.

I have the following code:

Const olFolderInbox As Integer = 6
Const AttachmentPath As String = "Y:\Do\"

Sub Avis7()

Dim task
Dim olNamespace As Object
Dim olApp As Object
Dim olNs As Object
Dim olFolder As Object
Dim olRecip As Object
Dim oOlItm As Object
Dim oOlAtch As Object
Dim oldsubject As String
Dim strBody As String

Dim y As Workbook
Set y = ThisWorkbook

Dim Str As String
Dim Str1 As String
Dim Str2 As String
Dim j As Integer
Dim i As Integer
Dim k As Integer

Set olApp = GetObject(, "Outlook.application")
Set olNs = olApp.GetNamespace("MAPI")
Set olRecip = olNs.CreateRecipient("")
Set olFolder = olNs.GetSharedDefaultFolder(olRecip, olFolderInbox).Folders("09")

If olFolder.Items.Restrict("[UnRead] = True").Count = 0 Then
MsgBox "no unread mail"
Exit Sub
End If

For Each oOlItm In olFolder.Items.Restrict("[UnRead] = True")

If oOlItm.SenderEmailAddress = "" Then
strBody =oOlItm .body
y.Sheets(1).Range("A1") = strBody
End If


End Sub

Unfortunately, absolutely nothing happens when I execute the code. Not even an error message.
Not open for further replies.
Thread starter Similar threads Forum Replies Date
R VBA for copying sent email to current folder under a shared mailbox Outlook VBA and Custom Forms 17
E Copying data from e-mail attachement to EXCEL file via macro Outlook VBA and Custom Forms 38
T Copying Outlook Account Setup For Desktop App Using Outlook 5
J Outlook - 2013 - Error msg when copying folders from Online Archives to another user's mailbox Using Outlook 0
Thiago Manzano Copying E-mails to a folder on HD Using Outlook 1
Diane Poremsky Mark Sent Items as Read After Copying with a Rule New Articles 0
crazyboy Copying BCM database from backed up mounted image to new drive BCM (Business Contact Manager) 2
oliv- Prevent copying shared contacts Outlook VBA and Custom Forms 5
Paul Butticaz Copying Contacts from SharePoint List (connected to Outlook) to another Conacts folder Using Outlook 1
K Outlook 2010 Not responding after IMAP folder deletion or copying. Using Outlook 2
Mary B Outlook 2013: Rule for copying new email to folder & marking that copy as read Using Outlook 1
S Calendar items are not being uploaded to after copying Using accounts in Outlook 1
P open reminders missing after copying outlook pst file Using Outlook 2
L Copying Emails using drag and drop Using Outlook 2
O New to Outlook 2013, dealing with copying vs. moving sent items Using Outlook 0
S Recover messages that disappeared when copying to folder Using Outlook 1
S Copying multiple messages to folders and keeping date/time detail Using Outlook 1
D Copying POP3 folders from Inbox to IMAP Inbox in Outlook 2003 Using Outlook 0
M Copying setups for mutiple users Using Outlook 2
T Location and simple copying of emails and settings, OL 2010 and Win 7 64bit. Using Outlook 3
A Copying additional contact fields when chosing "Contact from the same company" Using Outlook 5
A Copying cc email addresses to Excel - does not copy the <> Using Outlook 1
S Search Indexing (Copying Folders) Using Outlook 0
I copying messages from inbox to folder outside outlook Using Outlook 2
C Copying an email. Using Outlook 1
B Moving or copying a Hotmail Folder to an external Hard Disk?____ Using Outlook 2
F copying outlook Using Outlook 3
T Copying contacts Using Outlook 3
I Copying mailboxes from Exchange 2003 to Exchange 2010 Exchange Server Administration 3
1 Error message when copying contacts from one group to another Using Outlook 2
R copying or cutting an appointment to paste it to a different date Using Outlook 1
O Copying Outlook Categories to the attachment (MS word) body in bulk. Is this possible? Using Outlook 7
J Random copying of emails in inbox and calendar appointments deleted Using Outlook 1
S How to retain "Date Sent" and "Date Received" data when copying email messages from Outlook to an Ex Using Outlook 2
E Copying multiple folders to PST Outlook VBA and Custom Forms 2
B Copying data between atbs in a custom form Outlook VBA and Custom Forms 4
R copying a custom view from a public folder and distributing programatically Outlook VBA and Custom Forms 3
N error when copying outlook data "can not copy: the path too deep" Using Outlook 6
N Copying outlook macros between pcs Outlook VBA and Custom Forms 1
R Copying Data from Exchange to PST file Outlook VBA and Custom Forms 1
V Copying contents from one field to another field Outlook VBA and Custom Forms 6
V Calendar - Clearing the whole Calendar Using Outlook 1
G .outlook calender,Put in a re occuring event and it blanks out the whole 24hr period. Using Outlook 1
S Outlook mail adressing stops after first match in GAL Using Outlook 0
P Outlook 2013 All imported Mail Rules in error when imported into new profile Using Outlook 5
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
S Outlook Macro to move reply mail based on the key word in the subjectline Outlook VBA and Custom Forms 0
MrMayor SENT mail not reloading to top?? Using Outlook 4
Terry Sullivan Sender Field Displays My E-Mail Address, Not My Name Using Outlook 1
ThinkToday Calculate reply time of outlook mail Using Outlook 1
Similar threads