Emails fetching into Excel and auto generating Dashboard

How to solve the load and memory issues in VBA?


  • Total voters
    1
Status
Not open for further replies.

santosh v yadav

New Member
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server 2010
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

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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?
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
N VBA Macro To Save Emails Outlook VBA and Custom Forms 1
Horsepower Lost emails Using Outlook 4
P Emails assigned with a certain category (within a shared inbox) to be copied to a specific folder. Outlook VBA and Custom Forms 2
M Saving emails using Visual Basic - Selecting folder with msoFileDialogFolderPicker Outlook VBA and Custom Forms 6
L Emails being archived when I press the A key accidentally - how do I stop this? Using Outlook 0
N Save emails within a certain date range to network drive Outlook VBA and Custom Forms 0
M I cant send emails via Outlook in my W10 PC. Using Outlook 3
e_a_g_l_e_p_i Can emails from Gmail be deleted when they are downloaded to Outlook 2010 Using Outlook 1
D Wrong email address in Outlook 2003 "From" tab in new outgoing emails Using Outlook 4
witzker How to find all emails from and to a contact in OL 2019 Using Outlook 6
S How to find emails that I sent that have not received a reply? Using Outlook 7
M Extract "Date sent" from emails (saved to folder using drag and drop) Outlook VBA and Custom Forms 1
R Sent emails show iCloud mail account not the alias Using Outlook 2
K Run a script rule to auto 'send again' on undeliverable emails? Outlook VBA and Custom Forms 1
J how to stop junk emails from *.onmicrosoft.com ? Using Outlook 2
FryW Need help modifying a VBA script for in coming emails to auto set custom reminder time Outlook VBA and Custom Forms 0
S Auto forward for multiple emails Outlook VBA and Custom Forms 0
M All fonts in Outlook emails display with exaggerated character spacing Using Outlook 3
L Images not displaying in emails sent to self Using Outlook 2
C Icons (arrows) not showing on emails forwarded or replied to Using Outlook 8
R Moved 6 months worth (approx 1500 emails) lost from moving from TPG inbox to Icloud inbox (folders) Using Outlook 3
U Approving Emails from iPhone Using Outlook 0
D cannot view emails in View pane (in the right pane), I only see one line or nothing Using Outlook 21
A Macro to file emails into subfolder based on subject line Outlook VBA and Custom Forms 1
N Help creating a VBA macro with conditional formatting to change the font color of all external emails to red Outlook VBA and Custom Forms 5
B Outlook 2016 Unable to view images or logos on the outlook 2016 emails the same html code works well when i use outlook 2010 Using Outlook 0
A Record opened emails for reopening later Using Outlook 2
Y Filter unread emails in a search folder vba help Outlook VBA and Custom Forms 0
S Outlook 2016 Change how Outlook shows me contacts in emails Using Outlook 0
T Outlook creating unwanted tasks in Tasks and Todo from emails Using Outlook 1
Horsepower Moving emails between folder Outlook for Mac Outlook Wishlist 8
B Outlook 2016 Outlook crashes when trying to print certain emails Using Outlook 5
L IMAP Emails Disappear Using Outlook 0
L Need help modifying a VBA script for emails stuck in Outbox Outlook VBA and Custom Forms 6
S Macro to extract and modify links from emails Outlook VBA and Custom Forms 3
D Duplicate Emails on Phone and Tablet Using Outlook 0
S Macro for Loop through outlook unread emails Outlook VBA and Custom Forms 2
Globalforester ItemAdd Macro - multiple emails Outlook VBA and Custom Forms 3
R Auto Assign Category colours to Incoming Emails based on whom the email is addressed Outlook VBA and Custom Forms 3
P Forwarding emails issue with special characters replacing text body Using Outlook 1
B Emails get stuck in Outbox of Gmail IMAP in Outlook for Windows Using Outlook 0
C Multiple emails Using Outlook 8
Phillip Bit.ly emails Using Outlook 1
E Work uses live accounts for emails for all employees. Can we use the outlook calendar to give the employees an universal work schedule calendar? Using Outlook 1
G Alerting on missed emails in conversation Outlook VBA and Custom Forms 1
C Macro to extract sender name & subject line of incoming emails to single txt file Outlook VBA and Custom Forms 3
M Sync 'On my computer' emails Using Outlook 1
A Apply Selected Emails to outlook rules and Run Rules Using Outlook 5
A Run-time error '430' on certain emails when trying to set "Outlook.mailitem" as "ActiveExplorer.Selection.Item" Outlook VBA and Custom Forms 2
J Moved many emails to Outlook external folder, need to delete on Gmail server Using Outlook 14

Similar threads

Top