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
 
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.
 
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
H Move Selected emails to Local Drive Outlook VBA and Custom Forms 0
D Delete Outlook emails from MS server Using Outlook 9
G Creating Macro to scrape emails from calendar invite body Outlook VBA and Custom Forms 6
A Flagged Emails highlighted in yellow Using Outlook 2
P Search folder: all emails sent to or from a domain Using Outlook 1
J Macro to Reply to Emails w/ Template Outlook VBA and Custom Forms 3
G Save emails as msg file from Outlook Web AddIn (Office JS) Outlook VBA and Custom Forms 0
T Outlook 2010 Sub accounts not showing new emails in Inbox Using Outlook 4
Nufc1980 Outlook "Please treat this as private label" auto added to some emails - Help. Using Outlook 3
humility36 Cannot move emails to archive - 440 error Outlook VBA and Custom Forms 1
E Edit incoming emails to remove a certain sentence added by the "system" Using Outlook 1
R Saving Emails and Attachments as .msg file Using Outlook 3
F Color code certain INBOX emails Using Outlook 2
J gmail and deleted emails. Using Outlook 0
Z Outlook 2021 Outlook new emails notification not working Using Outlook 4
J Outlook 365 Outlook Macro to Sort emails by column "Received" to view the latest email received Outlook VBA and Custom Forms 0
C Outlook 365 Copy/Save Emails in Folder Outside Outlook to Show Date Sender Recipient Subject in Header Using Outlook 0
U Outlook not responding when trying to print Emails Using Outlook 6
K mark emails with colour manually (like in thunderbird) Using Outlook 1
richardwing Outlook 365 VBA to access "Other Actions" menu for incoming emails in outlook Outlook VBA and Custom Forms 0
U Outlook on the iPhone cannot approve filtered Emails Using Outlook 0
K Outlook 365 After migrating to Outlook 365, some contacts display in emails with prefixes Using Outlook 0
B Move emails from one account to another Outlook VBA and Custom Forms 2
D Unable to view older emails in desktop app Using Outlook 0
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 1
R Outlook 2021 Having problem setting up outlook 2021 with windows 11. I have 3 gmail accounts and I want the 3 gmail, emails to merge into the same outlook input. Using Outlook.com accounts in Outlook 0
S Outlook 2021 Can you make emails from 3 word domains "safe" by entering top 2 word domain into Safe List in Outlook? Using Outlook 1
J Outlook 365 Emails showing as links and text only Using Outlook 4
G Removing old emails when adding accounts Using Outlook 3
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 4
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

Similar threads

Back
Top