Extract data from outlook emails using excel vba

MarkBickers

New Member
Joined
Nov 3, 2023
Messages
1
Office Version
  1. 2010
Hi Excel Experts,

Hi There,

I am looking for excel vba code to check the emails in a subfolder in Outlook mailbox and get the following details from the body of the email and save down in excel sheet .

Please see below the content of email body.
1699006986846.png

The format of the emails will be same for all in that particular subfolder . I have used "Sheet1" in excel workbook to store Mailbox name and the subfolder name so the code will look for those values in sheet1 and loops through all the emails in the subfolder and save down all the details in Sheet2 . Can anyone please amend the code and make it work as I am getting an error message "Method 'Body' of object '_MailItem" failed on the lines highlighted as red

Code:
Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer
Dim msgtext As String

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("New Apps")

i = 1
lrow = 1

For Each OutlookMail In Folder.Items
MsgBox Folder.Items.Count
    msgtext = OutlookMail.Body
    MsgBox msgtext
     With ActiveSheet
            .Cells(lrow, 1) = OutlookMail.SenderName
            .Cells(lrow, 2) = OutlookMail.Subject
            .Cells(lrow, 3) = OutlookMail.ReceivedTime
            lrow = lrow + 1
        End With
  
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

Thanks,
Mark
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top