时间戳

首页 > 解决方案 > Excel VBA 代码生成电子邮件但使 Outlook 崩溃

vba - Excel VBA 代码生成电子邮件但使 Outlook 崩溃

问题描述

下面的代码创建（它不发送，因为它们需要视觉验证）几封带有 2 个附件的电子邮件（大约 60 封），一个是 209KB pptx（我尽可能压缩它）和一个 .xlsb 文件（30Kb - 700kb 取决于）。文本在 HTML 中只是因为我们需要一些突出显示。我认为这比从前景中调用模板要好，但如果那是错误的，请告诉我，我找不到任何相关信息。

问题是虽然它会生成所有电子邮件并附加所有文件，但它会冻结我的前景，以至于我必须关闭所有内容并从任务管理器重新启动。我已经等了一个多小时，看看它是否有效，但它只是生成电子邮件然后冻结。我可以通过任务栏查看电子邮件，但无法选择它们或 Outlook 收件箱。

知道如何解决这个问题吗？

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

标签： vbaexceloutlook

解决方案

内存不足问题，当它依赖于您生成的电子邮件数量时。在循环中添加保存和关闭以避免内存不足。

对于我的 Excel (2010) 版本，以下工作可以很好地减少内存使用：

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

推荐阅读