Link to home
Start Free TrialLog in
Avatar of Sanjay Gandhi
Sanjay GandhiFlag for India

asked on

Excel VBA - Import Mail Headers in Excel from Specific Folder

Hello,

I have a code that works fine for importing mails headers from Outlook in Excel. But the problem is I want it to import from specific folder. It gets the mails from default (first) folder. How to write the code so that it can fetch mail items from specific folder.

I am sending the code also.

Thanks,
San.
[code]
Sub OutlookMails_InExcel()
   ' Find and set 'Microsoft Outlook 12.0 or 16.0 Object Library' to checked
   
   ' Write SenderName, To, Subject
   Dim OutApp As Outlook.Application, OutFolder As Object
   Dim NextMail%, NewMail As Object
   Dim OutNs As Outlook.Namespace
   Dim NewFolder As Outlook.MAPIFolder
   Dim StartDate As Date, EndDate As Date
   
   ' Create Outlook virtual object
   Set OutApp = CreateObject("Outlook.Application")
   
   ' Set Classes to Variables
   Set OutApp = New Outlook.Application
   Set OutNs = OutApp.GetNamespace("MAPI")
   Set NewFolder = OutNs.GetDefaultFolder(olFolderInbox)
   Set OutFolder = NewFolder.Items
   
   ' Working
   Sheets("Mails").Select
   StartDate = #4/20/2017#     'StartDate = VBA.Format(Now - 10, "Short Date")
   EndDate = #4/23/2017#       'EndDate = VBA.Format(Now, "Short Date")
   'MsgBox "Writing " & NewFolder.Parent & " " & NewFolder.Name & " " & NewFolder.Items.Count
   ' Write in Excel
   NextMail = 7
   For Each NewMail In OutFolder
      If NewMail.SentOn >= StartDate And NewMail.SentOn <= EndDate Then
         NextMail = NextMail + 1
         Cells(NextMail, 2) = NewMail.SenderName
         Cells(NextMail, 3) = NewMail.To
         Cells(NextMail, 4) = NewMail.Subject
         Cells(NextMail, 5) = NewMail.SentOn
      End If
   Next
   ' Set Column width
   If NextMail > 0 Then
      MsgBox NextMail & " mails in specified range", vbInformation, "Inbox"
      Columns("B").ColumnWidth = 30
      Columns("C").ColumnWidth = 25
      Columns("D").ColumnWidth = 60
      Columns("E").ColumnWidth = 15
   Else
      MsgBox "No mails in specified range", vbCritical, "Inbox"
   End If
End Sub

Open in new window

[/code]
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sanjay Gandhi

ASKER

Thanks, it works perfect. :)