excel VBA 从宏发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14368875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel VBA to send e-mail from macro
提问by Eric_S
I am writing a macro to send an e-mail from an excel sheet. The macro prepares a few reports and then has a function to prepare the e-mail for the report. Everything works fine except when it gets to the .Send line it gives me a run time error -2147467259. Not sure what this means, but would appreciate the help.
我正在编写一个宏来从 Excel 工作表发送电子邮件。该宏准备了一些报告，然后具有为报告准备电子邮件的功能。一切正常，除了当它到达 .Send 行时，它给我一个运行时错误 -2147467259。不确定这意味着什么，但希望得到帮助。
Here is the code for the function:
下面是函数的代码：
Function Mail_Reports(ByRef wkDate2 As String, fileDate2 As String, wkNumber2 As String, thisYear2 As String)
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object
Dim mailList As String
Dim i As Long, lstRow As Long, p As Long, addressNum As Long, begRow As Long
Dim proNam2 As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'On Error Resume Next
' Change the mail address and subject in the macro before you run it.
For i = 1 To 5 Step 1
mailList = ""
lstRow = Sheets("Data").Cells(Rows.Count, i).End(xlUp).Row
addressNum = lstRow - 16
begRow = lstRow - addressNum
proNam2 = Sheets("Data").Cells(16, i)
proNam2 = Replace(proNam2, " ", "")
For p = 1 To addressNum Step 1
mailList = Sheets("Data").Cells(begRow + p, i) & " ; " & mailList
Next p
With OutMail
.To = mailList
'.CC = "" remove comma and use this if you want to cc anyone, can be string or variable
'.BCC = "" remove comma and use this if you want to cc anyone, can be string or variable
.Subject = "Test"
.HTMLBody = "<HTML><BODY><Font Face=Calibri(Body)><p>Hi All,</p><p2>Attached to this e-mail is the test file.<p2/><br><br><p3>Best,<p3/></font></BODY></HTML>"
.attachments.Remove 1
.attachments.Add "C:\Documents and Settings\test.xlsx"
.Display
.Send
Next i
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
回答by bonCodigo
Can you please try with just,
你能试试吗，
- Save the report file into a local drive
- use one email address first, so remove the for loop
- send it with just one file/range/workbook.
- remove html tags for signature or etc..
- 将报告文件保存到本地驱动器
- 首先使用一个电子邮件地址，因此删除 for 循环
- 只用一个文件/范围/工作簿发送它。
- 删除用于签名等的 html 标签。
Code:
代码：
With WB '-- workbook
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Here is a Report on My VBA analysis"
.Attachments.Add Dest.FullName
'You can add other files also like this
'.Attachments.Add ("C:\testmail.txt") '-- .xls
.Send 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
- Update based on the comments with OP:
- 根据 OP 的评论进行更新：
Looking at your email concat loop, you do not have to do it each time when a new book comes UNLESS YOUR MAILING LIST DIFFERS FOR EACH WORKBOOK.... You may take that loop out of the mail workbooks iteration.
看着你的电子邮件CONCAT循环，你不必每次都这样做时，一本新书来，除非你的邮件列表不同，为每个工作簿...。您可以将该循环从邮件工作簿迭代中移除。
For p = 1 To addressNum Step 1
mailList = Sheets("Data").Cells(begRow + p, i) & " ; " & mailList
Next p
回答by mkingston
One problem I can see here is that you do the following:
我在这里看到的一个问题是您执行以下操作：
Set OutMail = OutApp.CreateItem(0)
outside the send loop. You should move that here:
在发送循环之外。你应该把它移到这里：
[...]
For p = 1 To addressNum Step 1
mailList = Sheets("Data").Cells(begRow + p, i) & " ; " & mailList
Next p
Set OutMail = OutApp.CreateItem(0)
With OutMail
[...]
I can't comment on your specific error because I don't know what data is going into your OutMail object. However, to help you debug, I recommend you:
我无法评论您的具体错误，因为我不知道哪些数据会进入您的 OutMail 对象。但是，为了帮助您调试，我建议您：
- Close the
With OutMailblock with anEnd With - Set a reference to Microsoft Outlook 14.0 Object Library
- Declare OutApp as Outlook.Application (
Dim OutApp as Outlook.Application) - Declare OutMail as Outlook.MailItem (
Dim OutMail as Outlook.MailItem) - Initialise OutApp as follows:
Set OutApp = New Outlook.Application
With OutMail用一个关闭块End With- 设置对 Microsoft Outlook 14.0 对象库的引用
- 将 OutApp 声明为 Outlook.Application (
Dim OutApp as Outlook.Application) - 将 OutMail 声明为 Outlook.MailItem (
Dim OutMail as Outlook.MailItem) - 初始化 OutApp 如下：
Set OutApp = New Outlook.Application
The above are not necessary (except maybe closing your With OutMailblock) but may help you to diagnose problems with your code.
以上不是必需的（除了可能关闭您的With OutMail块），但可以帮助您诊断代码问题。
Also note that if you're using a newer version of Outlook, other applications (Excel, Word, Access etc.) may be prevented from sending by security controls: http://support.microsoft.com/kb/263084.
另请注意，如果您使用的是较新版本的 Outlook，其他应用程序（Excel、Word、Access 等）可能会被安全控制阻止发送：http: //support.microsoft.com/kb/263084。
