VBA发送批量电子邮件的性能问题 [英] VBA Send bulk emails performance concern

问题描述

我有以下宏，该宏将扫描B列中带有经理电子邮件的excel文件.对于每位经理，将起草一封电子邮件/附加一个excel文件并自动发送.我已经能够对此进行测试，并且在起草50-100时效果很好.

I have the below macro which will scan an excel file with manager emails down Col B. For each manager, an email will be drafted/an excel file attached/ and sent automatically. I have been able to test this and it is working fine when drafting 50 - 100.

我担心的是， 50-100 电子邮件似乎不是很好的指示，它知道在发送 5,000 电子邮件时是否可以正常工作.

My concern is, 50 - 100 emails does not seem like a good indicator of knowing if this will work fine when sending 5,000 emails.

在包含5,000封电子邮件的实际文件上运行此代码时，我是否有冻结或其他问题的风险?

Sub CorpCard()

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup

For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And LCase(Cells(cell.Row, "C").Value) = "yes" Then
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .SentOnBehalfOfName = "[e-mail]"
            .to = cell.Value
                .Subject = "Your Employees With A Corporate Credit Card - EID - " & Cells(cell.Row, "D").Value
                .Body = "Hi " & Cells(cell.Row, "A").Value & "," _

                'Body to be patsed here

            strLocation = "C:\Users\urdearboy\Desktop\File Name " & Cells(cell.Row, "D").Value & ".xlsx"
            .Attachments.Add (strLocation)
        .Send
        End With

        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

将我的For Each循环容纳在另一个类似于For i = 1 to 5000 Step 50的循环中是一个好主意，然后在开始实际循环之前添加Do Events，让我的计算机有一定的时间赶上下一个50电子邮件吗?我不确定这是否在Do Events的范围内.如有必要，我还可以提供计算机规格.

Would it be a good idea to house my For Each loop inside another loop something like For i = 1 to 5000 Step 50 and then add Do Events before starting the actual loop to give my computer some time to catch up before proceeding with the next 50 emails? I'm not exactly sure if this is in the scope of Do Events though. I can also provide computer specs if necessary.

推荐答案

这对于较大的文件应该可以正常工作.有了那么多的电子邮件，您的运行时间很容易超过一个小时.一个好主意可能是在错误处理程序中引发一些标志，以防遇到问题.也许像这样:

This should work fine for larger files. With that number of emails to send though, your run time could easily be over an hour. A good idea might be to raise some flag in the error handler in case it does encounter an issue. Maybe something like:

    if Err then
         Msgbox "Error Encountered at Row " & cell.row
    end if 

with-block的正下方.

right underneath the with-block.

这篇关于VBA发送批量电子邮件的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！