Excel电子邮件宏大小限制 [英] Excel Email Macro Size Limits
问题描述
我创建了一个宏，用于使用每个客户的个性化信息向客户发送一批电子邮件.它的运行状况很好，但提醒我注意的是，在运行宏时，该宏不会在Outlook中填充的第67个草稿后创建电子邮件.我试图进行研究，但没有发现Outlook可以让您一次打开多少电子邮件草稿的约束条件.我在下面发布了我的代码，以防任何人看到我是否无意中设置了限制.
I have a macro I built to send out batches of emails to clients, using personalized info for each client. It's been working great, but it's just been brought to my attention that the macro doesn't create emails after about the 67th draft that populates in Outlook when you run the macro. I've tried to research and haven't found a constraint restriction as to how many email drafts Outlook will let you have open at once. I've posted my code below in case anyone can see if I inadvertently set up a limit.
宏的工作方式:员工每月需要列出约500个帐户，以通过电子邮件与他们续订与我们续签的合同.所有信息都在Excel文件中，并且此宏提取每个客户的电子邮件地址，客户名称，客户联系方式，续订日期等，并在每封电子邮件中使用该信息来获得更个性化的电子邮件.一旦员工单击按钮运行宏，它将为列表中的每个帐户创建一封电子邮件，并填充到Outlook中.我让电子邮件填充在Outlook中，而不是自动发送，以防员工要根据客户的需要编辑或向电子邮件中添加更多信息.大多数情况下，他们按原样发送电子邮件，但是员工确实会编辑一些电子邮件.因此，尽管一次在Outlook中填充如此多的草稿似乎令人生畏，但对于员工而言，以这种方式发送电子邮件而不是单独键入每个电子邮件要快得多.
How the macro works: Employees have a list of about 500 accounts that they need to contact via email every month about renewing their contract with us. All of the info is in an Excel file, and this macro pulls each clients email address, customer name, customer contact, renewal date, etc. and uses that info in each email for a more personalized email. Once the employee hits the button to run the macro, it will create an email for each account in the list, populating in Outlook. I have the emails populate in Outlook instead of automatically sending in case the employee wants to edit or add more information to an email depending on the client. Most cases they send the email as is, but the employees do edit a few emails. So while it seems daunting to have so many drafts populate in Outlook at once, it's much faster for the employees to send out emails this way instead of typing each one out individually.
因此，当宏应遍历最后一行信息时，为什么我一次不能发送67封以上电子邮件的任何建议或见解，请告诉我.如果不是这样，我只需要告诉员工仅按我想的每60个批次使用一次宏即可.
So any advice or insight on why I can't send more than 67 emails at once when the macro should run through the last row of information, please let me know. If not I will just have to tell the employees to only use the macro in batches of 60 I suppose.
Sub SendEMail()
Dim Email As String
Dim Subj As String
Dim Msg As String
Dim URL As String
Dim r As Integer
Dim x As Double
Dim OApp As Object
Dim OMail As Variant
Dim Signature As String
Dim strbody As String
'for formatting reasons
strbody = "<html><body>"
'for looping
With Sheets("List").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
End With
For r = 2 To lastrow
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
' Get the email address
Sheets("List").Select
Email = Cells(r, "K")
' Message subject
Sheets("List").Select
Subj = "Renewal for " & Cells(r, "B").Text & " Client # " & Cells(r, "A").Text & " Effective " & Cells(r, "D").Text
' Message body
Sheets("List").Select
strbody = "<p>Dear " & Cells(r, "J").Text & ", </p>" & _
"I am contacting you regarding the upcoming renewal for " & Cells(r, "B").Text & ", account number " & Cells(r, "A").Text & ", which is effective " & Cells(r, "D").Text & ". We have reviewed the account and determined that we have the information we need on file in order to offer renewal terms. & _
"Should you have any questions or if we can be of futher assistance, please don't hesitate to contact " & Cells(r, "O").Text & " at " & Cells(r, "M").Text & " or " & Cells(r, "N").Text & _
" or respond to this email. If you are aware of changes to the contact on this account, please let us know, so we can be sure to get future correspondence to the proper person.<br><br>" & _
"As always, we would like to thank you for your business.<br><br>" & _
"Sincerely,"
On Error Resume Next
Sheets("List").Select
With OMail
.Display
.To = Email
.Subject = Subj
.HTMLBody = strbody & vbNewLine & .HTMLBody
End With
Next r
On Error GoTo 0
Set OMail = Nothing
Set OApp = Nothing
End Sub
推荐答案
因此，这里有一些代码可以通过两种方式处理您的问题.一种是限制批处理数量...另一种是将邮件对象保存在每个for循环的我的文档"文件夹中，将邮件对象设置为空.这应该使用户能够一次运行所有帐户，但是可以在发送之前打开并编辑...，这使我有了真正的解决方案.
So here is some code that could handle your problem two ways. One is to limit the batch amount... The other is to save the mailobject in the my documents folder in each for loop, the set the mail objects as nothing. This should give the users the ability to run all the accounts at once, but open and edit before they send... which brings me to my real solution.
找出这些用户正在编辑电子邮件的哪种情况，然后为其编码.另外，如果用户在发送之前手动查看每封电子邮件.也许完全摆脱了循环，当他们想要电子表格上的下一个可用帐户时，让他们单击一个按钮.这可以通过在其上具有已发送状态"的列来完成.
Find out what scenarios these users are editing emails, then code for them. Also, if the users are manually looking at each email before it is sent. Maybe get rid of the loop entirely and have them click a button when they want the next available account on the spreadsheet. This could be accomplished by having a column with "sent status" on it.
Option Explicit
Sub SendEMail()
Dim Email As String
Dim Subj As String
Dim Msg As String
Dim URL As String
Dim r As Integer
Dim x As Double
Dim OApp As Object
Dim OMail As Variant
Dim Signature As String
Dim strbody As String
'for formatting reasons
strbody = "<html><body>"
'for looping
With Sheets("List").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
End With
For r = 2 To lastrow
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
' Get the email address
Sheets("List").Select
Email = Cells(r, "K")
' Message subject
Sheets("List").Select
Subj = "Renewal for " & Cells(r, "B").Text & " Client # " & Cells(r, "A").Text & " Effective " & Cells(r, "D").Text
' Message body
Sheets("List").Select
strbody = "This was a syntax error, changing for simplicity."
On Error Resume Next
Sheets("List").Select
With OMail
.display
.To = Email
.Subject = Subj
.HTMLBody = strbody & vbNewLine & .HTMLBody
''New
.SaveAs ("C:\Users\" & Environ$("Username") & "\Documents\" & Environ$("Username") & r & ".msg")
End With
Set OMail = Nothing
Set OApp = Nothing
''' 'If you decide to limit your batches do it here
''' If r = 62 Then
''' MsgBox "Could do an assortment of things here...anyways, its not a very good solution. "
''' End If
Next r
On Error GoTo 0
Set OMail = Nothing
Set OApp = Nothing
End Sub
而且，这完全取决于您和您的业务需求.我真的会考虑将strbody从硬代码中移出，并移到新的工作表/文本文件或电子表格中的可用单元格中.使支持变得非常容易.
Also, and this is completely up to you and your business needs. I would really consider moving the strbody out of the hardcode and into a new sheet/text file or available cell in the spreadsheet. Makes support so much easier down the line.
阅读上面关于内存的评论之后.问题是所有这些打开的电子邮件.您可能要使用上面的save方法，然后编写一个新的宏，该宏将打开该目录中具有最旧日期修改的文件，在他们单击发送"后，将文件删除或存档.
After reading your comment above about memory.. the problem is all of those open email messages. You might want to use the save method above, then write a new macro that will open a file with the oldest date modified from that directory, after they click send have the file delete or archive... Something like that.
这篇关于Excel电子邮件宏大小限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
