• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extract Emails from OST File|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Extract Emails from OST File|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosExtract Emails from OST File
sp_PrintTopic sp_TopicIcon
Extract Emails from OST File
Page: 12Jump to page
Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
1
June 5, 2020 - 10:31 am
sp_Permalink sp_Print

When try this Macro, I got same problem, pls advise how to fix Annotation-2020-06-05-082455.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Annotation-2020-06-05-082455.png (133 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
June 5, 2020 - 10:33 am
sp_Permalink sp_Print

Hi David,

What does that error say?

Have you created the reference to the Outlook library in the VBA editor?

Phil

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
3
June 5, 2020 - 11:20 am
sp_Permalink sp_Print

Error Code meaning: Missing Microsoft Outlook Object Library 16.0

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
4
June 5, 2020 - 11:53 am
sp_Permalink sp_Print

Thanks millions  Philip, will try on this.  

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
5
June 5, 2020 - 12:16 pm
sp_Permalink sp_Print

I tried but the extract data seems from my old Outlook.pst Inbox Folder.

Can we run the same macro but extract from Outlook.ost file Inbox Folder;  or other folder, eg Send Items Folder/ self created private folder in Outlook 365.ost

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
June 5, 2020 - 12:21 pm
sp_Permalink sp_Print

Hi David,

Yes you can.  Have you changed the Email Account Name in the VBA to look at the folders for the account you are referring to?

To help any further I need to know the structure of your email folders, a screeenshot will do, and the name of the account that you want to search.

Phil

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
7
June 5, 2020 - 12:51 pm
sp_Permalink sp_Print

Thanks Philip, pls refer attach Screen shot of my PC

Pls help modify macro for use

BTW , my 365 Outlook.Ost kept in : D:\GKJ00OH004\Email\….OST

sp_PlupAttachments Attachments
  • sp_PlupImage Annotation-2020-06-05-082455-1.png (133 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
June 5, 2020 - 12:59 pm
sp_Permalink sp_Print

Hi David,

Did you try changing the email account name in the VBA as I asked?

I also asked you to let me know the name of that account.

And that screenshot is not of Outlook 🙂

Phil

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
9
June 5, 2020 - 1:32 pm
sp_Permalink sp_Print

Sorry pls refer this attach.

sp_PlupAttachments Attachments
  • sp_PlupImage OST.png (108 KB)
Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
10
June 5, 2020 - 1:48 pm
sp_Permalink sp_Print

Account Name : GKJ00OH004

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
11
June 5, 2020 - 3:45 pm
sp_Permalink sp_Print

Hi David,

I'll try asking again : Have you tried changing the name in the VBA code to your own email account name?

Phil

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
12
June 5, 2020 - 3:56 pm
sp_Permalink sp_Print

So sorry , but how to find Account name in the Outlook.ost, then update to the VBA?

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
13
June 5, 2020 - 4:43 pm
sp_Permalink sp_Print

Hi David,

In Reply #4 of this thread I explained how to change the VBA.  Open the file I created, press ALT+F11 to open the VBA editor then make the change.

Can I just clarify something.  The code I've written will read emails from an email account that is setup up inside Outlook.  It will not go and read emails from inside an OST that is disconnected from Outlook.

As you've supplied a screenshot of an OST in a folder, rather than the folders of an account in Outlook, I'm suspecting that this is what you are trying to do?  Please confirm.

Just to reiterate, to read info from emails, the account you are trying to read those emails from must be set up and working inside Outlook.

Phil

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
14
June 5, 2020 - 6:20 pm
sp_Permalink sp_Print

So sorry , where to find email account name?

As far as I know the Email account name is:  GKJ00OH004

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
15
June 8, 2020 - 9:48 am
sp_Permalink sp_Print

Yes.

But still not working

 

Dim Folder As Object
Dim Email As Variant
Dim i As Integer

Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookNS = OutlookApp.GetNamespace("MAPI")

' Insert the NAME of your email account - NOT the email address
Set Folder = OutlookNS.Folders("GKJ00OH004").Folders("Inbox")

  
i = 1

For Each Email In Folder.Items
       
        Range("A1").Offset(i, 0).Value = Email.SenderName
        Range("B1").Offset(i, 0).Value = Email.Subject
        Range("C1").Offset(i, 0).Value = Email.ReceivedTime
        Range("D1").Offset(i, 0).Value = Email.To
        Range("E1").Offset(i, 0).Value = Email.CC
       
   ''     Range("F1").Offset(i, 0).Value = Email.Location
       
        i = i + 1

Next Email

Set Folder = Nothing
Set OutlookNS = Nothing
Set OutlookApp = Nothing

End Sub

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
16
June 8, 2020 - 10:56 am
sp_Permalink sp_Print

David,

Is your OST file attached to Outlook and in use with an email address?

Or is it just a file in a folder?

Phil

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
17
June 8, 2020 - 11:23 am
sp_Permalink sp_Print

just the file in the FolderDavid.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage David.png (107 KB)
Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
18
June 8, 2020 - 11:25 am
sp_Permalink sp_Print

in use with email address :ng.david.wl@semhk.sharp-world.com

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
19
June 8, 2020 - 2:04 pm
sp_Permalink sp_Print sp_EditHistory

Hi David,

There seems to be some confusion which isn't helped by you not answering my questions.

The code I've written is to read emails from a folder from an account attached to Outlook.  That account must be configured as a working account in Outlook.

I asked for a screenshot of your Outlook folders from inside Outlook but you sent a screenshot of folders in Windows Explorer on your hard drive.

The name of the folder in that screenshot was GKJ00OH004 - this is not the account name I am looking for.  If you referred to the screenshots Marsil posted in the original thread this started in, you would see what I mean.

I have attached some samples below.  You can see that the account name is John Smith and the email address is John.Smith@email.com.

If your OST file is not connected to Outlook and in use by an account then my VBA code will not work for you.  OST files are offline stores and I do not know any way to open them from VBA.  They are not the same as PST files.  If you are trying to recover the emails because the OST file got disconnected from Outlook, you will probably need specialist recovery software to get them.  I don't know any other way.

So you need to check in your Outlook what the name of the account is and that it is set up and working and that it is connected to the OST file pictured in your screenshot. 

If you just want to read the Inbox then don't worry about a screenshot of the Outlook folders.

Regards

Phil

sp_PlupAttachments Attachments
  • sp_PlupImage outlook-folders.png (7 KB)
  • sp_PlupImage sample-email-config.png (31 KB)
Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
20
June 8, 2020 - 2:12 pm
sp_Permalink sp_Print

But still not sure where to check the name of the Account in outlook ?

Would it be ng.david.wl

 

David-1.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage David-1.png (45 KB)
Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Bhuwan Devkota
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.