We help IT Professionals succeed at work.

Excel VBA - Import Mail Headers in Excel from Specific Folder

406 Views
Last Modified: 2017-05-08
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]
Comment
Watch Question

Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sanjay GandhiFounder, Kenhal

Author

Commented:
Thanks, it works perfect. :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.