Sanjay Gandhi
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.
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
[/code]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER