How to schedule extract Outlook Mailbox subject into excel?

Question

I have few shared mailbox in outlook. I created VBA in excel to extract out the email subject. But I have to click "Get Outlook" button in excel follow by click selected Inbox one by one. Is there any way that I able to schedule auto extract every each inbox instead of clicking it manually one by one?

You can use Windows Scheduler to open a workbook automatically. An on open event within the workbook will start your macro automatically. Why do you need to click for each Inbox? A macro can check as many Inboxes as required without human intervention providing it does nothing to trigger Outlook's security system. Reading down a folder extracting subjects will not trigger Outlook's security system so you should be OK. — Tony Dallimore
@TonyDallimore thanks for the suggestion. Any reference that I can refer on macro trigger without human intervention clicking it manually? — user3959104
What help do you need? I have never used Windows Scheduler but the online documentation makes it look easy to use. Set it up to start a workbook at 11:00 or whenever. Do you know how to use an Open Workbook event? I can provide an example if necessary. A macro started by an Open Workbook event can look at as many Inboxes as required. — Tony Dallimore
@TonyDallimore sure, Open Workbook event sample will be great to explore. — user3959104

Tony Dallimore Tony Dallimore · Accepted Answer · 2020-03-16T14:34:09

Create a macro enabled workbook unless you have a suitable one available.

From the VBA Editor, click [Tools] then [References]. Check that "Microsoft Outlook nn.n Object Library" is listed near the top and ticked. If listed but not ticked, click the box to tick it. If not listed near the top, scroll down (the list is in alphabetic order) until you find this library and tick it.

Copy the code below to ThisWorkbook. Save the workbook and close it.

Open the workbook by clicking it or via Windows Scheduler. A list of the accessible stores is displayed as a demonstration. Click [OK] to exit Excel or [Cancel] to terminate the macro so you can work on the code. Warning 1: if you click [OK] all open workbooks will be closed without saving any changes. Warning 2: If you do not have a Cancel or equivalent option so the macro closes the workbook automatically, you will never be able to amend the macro.

Note: this code is a demonstration of using the Workbook Open event to run a macro and a demonstration of accessing Outlook from Excel. However, it does nothing with Outlook except list the accessible stores. If you need a further demonstration, I will try to help.

Option Explicit
Sub Workbook_Open()

  ' Needs reference to "Microsoft Outlook nn.n Object Library" where
  ' nn.n depends on the version of Outlook being used.

  Dim Answer As Long
  Dim Dspl As String
  Dim InxS As Long
  Dim OutApp As New Outlook.Application
  Dim OutNs As Outlook.Namespace

  Set OutNs = OutApp.Session

  ' Build list of all accessible stores
  With OutNs
    Dspl = .Folders(1).Name
    For InxS = 2 To .Folders.Count
      Dspl = Dspl & vbLf & .Folders(InxS).Name
    Next
  End With

  Answer = MsgBox(Dspl, vbOKCancel)

  If Answer = vbCancel Then
    Exit Sub
  End If

  Application.DisplayAlerts = False
  Application.Quit

End Sub

How to schedule extract Outlook Mailbox subject into excel?

1 Answers