Emails fetching into Excel and auto generating Dashboard

[santosh v yadav]

I have created the functionality to fetch emails from outlook to ms excel (2010). But when I select 1000 emails and try to fetch to excel from outlook it not fetching the data properly and restarting the outlook.

Here is VBA code:

Sub D2DAcronis() '(MyMail As MailItem)
Dim item As MailItem, x%
Dim r As Object 'As Word.Range
Dim doc As Object 'As Word.Document
Dim xlApp As Object, wkb As Object
Set xlApp = CreateObject("Excel.Application")
Set wkb = xlApp.Workbooks.Open("D:\Backupreport\ServerBackupDasboard.xlsb")
xlApp.Visible = True
Dim wks As Object
Set wks = wkb.Sheets("Data-D2D_Acronis")
wks.Select
Dim rNum As Integer
For Each item In Application.ActiveExplorer.Selection
Set doc = item.GetInspector.WordEditor
wks.Cells(wks.Rows.Count, 1).End(3).Offset(1).Value = item.Body
wks.Cells(wks.Rows.Count, 2).End(3).Offset(1).Value = item.Subject
wks.Cells(wks.Rows.Count, 3).End(3).Offset(1).Value = item.ReceivedTime
wkb.Sheets("Data-D2D_Acronis").Range("A:C").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Next
Set doc = Nothing
wkb.Sheets("CoverPage").Select
wkb.Save
MsgBox "Successfully imported messages to Excel"
End Sub

 

[Diane Poremsky]

Why are you using word? You can get the subject, body, and time without using word.
plus, you are setting it 1000 times in the loop
For Each item In Application.ActiveExplorer.Selection
Set doc = item.GetInspector.WordEditor
either set it before the loop or kill it before the next message.

 

[Michael Bauer]

I guess it's not the entire code else it makes no sense to retrieve the Word.Document.

Also, use a variable for the Selection object instead of calling Application.ActiveExplorer.Selection a thousand times. The same for all of the other references.

I'm not an Excel expert, but wouldn't it be much more faster if you format the entire columns once after all the data is exported instead of doing it per email?