CODEWITHSUNDEEP
vbaexceloutlook
Joshua Toliver
Joshua Toliver

Reputation: 13

Excel VBA code generates emails but crashes Outlook

This code below creates (it does not send because they need visual verification) several emails (about 60) with 2 attachments, one is 209KB pptx ( I compressed it down as far as I could) and an .xlsb file (30Kb - 700kb depending). Text is in HTML just cause we require some highlighting. I thought this would be better than calling a template out of outlook but if thats wrong let me know, I cant find any info about that.

The issue is while it will generate all the emails and attach all the files, it freezes my outlook to the point I have to close everything and restart from the task manager. I have waiting on it for over an hour to see if it works but it just generates the emails and then freezes. I can see the emails through my taskbar but i can't select them or my outlook inbox.

Any idea how to over come this?

Sub email()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim MailMessage As String
Dim CusName As String
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Call getemails


MailMessage = "<HTML><BODY> Hello, <br><br>" _
        & "Attached you will find your trailing 12 month (TTM) margin leak 
report which was discussed on the Best Practices call in August. (Deck from 
meeting attached as well)<br><br><br>" _
        & "<li> Tab 1 shows margin leak by item<br><br>" _
        & "<li>Tab 2 shows margin leak by vendor then by item<br><br>" _
        & "<li>Tab 3 is data tab where you can see all the data<br><br>" _
        & "Tab 1 and 2 includes a filter at the top so you can look at or 
exclude specific PCATs.<br><br>" _
        & "<b>Key definitions of fields on data tab:</b><br><br>" _
        & "<li>Base Price, Base Cost, Base Margin – Price, Cost and Margin 
dollars prior to margin leak<br><br>" _
        & "Thank you,<br><br>" _


Lastrow = Range("A" & rows.Count).End(xlUp).Row
For i = 1 To Lastrow
Set olApp = GetObject(Class:="Outlook.Application")

If olApp Is Nothing Then

Set olApp = CreateObject(Class:="outlook.application")

End If

Set olMail = olApp.CreateItem(0)

With olMail
    .To = Cells(i, 2).Value
    .Subject = Format(MonthName(Month(Now))) & " - Margin Leak - " & Cells(i, 
1).Value
    .display
    .HTMLBody = MailMessage
    .Attachments.Add ("C:\Linking_Files\Best Practices Margin Leak.pptx")
    .Attachments.Add ("C:\Desktop\June\" & Cells(i, 1).Value & ".xlsb")

End With

Set olMail = Nothing
Set olApp = Nothing

Next i
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 1395

Answers (1)

Denyo
Denyo

Reputation: 117

Out of memory issue, when it relies on the amount of email you generate. Add a save and a close into your loop to avoid getting out of memory.

For my version of Excel (2010) the following worked fine reducing memory usage:

With olMail
   .To = Cells(i, 2).Value
   .Subject = Format(MonthName(Month(Now))) & " - Margin Leak - " & Cells(i, 1).Value
   .display
   .HTMLBody = MailMessage
   .Attachments.Add ("C:\Users\u\Desktop\test.xls")
   .Save
   .Close 1
End With

Upvotes: 1

Related Questions