Excel VBA 发送带有多个附件的电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19911642/
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 Sending emails with multiple attachements
提问by Adjit
So we are holding this big event and I have an excel sheet with everyones name, email address as well as their itinerary files (there are 2 of them) Cells(x, 3)and Cells(x, 4). What I am trying to do is go down the column and send everyone a 'personalized' email with all of their information.
所以我们正在举办这个盛大的活动，我有一张 Excel 表格，上面有每个人的姓名、电子邮件地址以及他们的行程文件（其中有 2 个）Cells(x, 3)和Cells(x, 4). 我正在尝试做的是沿着专栏向每个人发送一封“个性化”电子邮件，其中包含他们的所有信息。
In the code, the forloop only goes to 3 because I am just testing it out by sending the emails to myself and don't want to end up getting 1000 emails :P
在代码中，for循环只进行到 3，因为我只是通过向自己发送电子邮件来测试它，并且不想最终收到 1000 封电子邮件：P
I keep getting a Run-Time Error 440 (Automation Error)at the lines where I attempt to add the attachments... not sure what's going on or how to remedy it any help is appreciated
我在尝试添加附件的行中不断收到运行时错误 440（自动化错误）...
Code
代码
Sub CreateHTMLMail()
'Creates a new e-mail item and modifies its properties.
Dim olApp As Object
Dim objMail As Object
Dim body, head, filePath, subject As String
Dim x As Long
Set olApp = CreateObject("Outlook.Application")
'Create e-mail item
Set objMail = olApp.CreateItem(0)
filePath = "\fileserver\homeshares\Tsee\My Documents\Metropolitan Sales\MNF"
subject = "Important Travel Information for MNF Event this weekend"
x = 1
For x = 1 To 3
head = "<HTML><BODY><P>Hi " & Cells(x, 1).Value & ",</P>"
body = body & "<BR /><P>We are looking forward to having you at our <STRONG>Metropolitan Night Football Event</STRONG> this upcoming Sunday, <STRONG>11/17</STRONG>! Note, that the Giants game time has changed from 8:30 PM to 4:25 PM.</P>"
body = body & "<BR /><P>Please find attached your travel information packet that contains important addresses and confirmation numbers. Please read through it and let me know if you have any questions.</P>"
body = body & "<BR /><P>If you need to reach me this weekend, please call my cell phone <STRONG>(631) 793-9047</STRONG> or email me.</P>"
body = body & "<BR /><P>Thanks,<BR />Liz</P></BODY></HTML>"
With objMail
.subject = subject
.To = Cells(x, 2).Value
.Attachments.Add = filePath & "/" & Cells(x, 3).Value
.Attachments.Add = filePath & "/" & Cells(x, 4).Value
.BodyFormat = olFormatHTML
.HTMLBody = head & body
.Send
End With
Next x
End Sub
采纳答案by Siddharth Rout
Further to the above comments, @bamie9l has already solved one problem of yours
除了上面的评论，@bamie9l 已经解决了你的一个问题
Problem 2
问题二
@bamie9l Awesome! That worked, but now at the .BodyFormat = olFormatHTML line I get Run-time error '5': Invalid procedure call or argument – metsales 13 mins ago
@bamie9l 太棒了！那行得通，但现在在 .BodyFormat = olFormatHTML 行，我收到运行时错误 '5': Invalid procedure call or argument – metsales 13 分钟前
You are latebinding with Outlook from Excel and olFormatHTMLis an Outlook constant and hence Excel is unable to recognize it. In the Immediate Windowof MS-Outlook if you type ?olFormatHTMLthen you will note that the value of that constant is 2
您在 Excel 中使用 Outlook 进行后期绑定并且olFormatHTML是 Outlook 常量，因此 Excel 无法识别它。在Immediate WindowMS-Outlook 中，如果您键入，?olFormatHTML那么您会注意到该常量的值是2
Hence we have to declare that constant in Excel. Like I mentioned, either you can put Const olFormatHTML = 2at the top of the code or replace .BodyFormat = olFormatHTMLby .BodyFormat = 2
因此，我们必须在 Excel 中声明该常量。就像我提到的，您可以放在Const olFormatHTML = 2代码顶部或替换.BodyFormat = olFormatHTML为.BodyFormat = 2
Problem 3
问题三
@SiddharthRout So that works, but now I get a crazy automation error... it goes through the loop once.. sends 1 email and then when it gets up to .subject = subject I get Run-time error '-2147221238 (8004010a)': Automation Error which as far as I know is the same as Run-Time Error 440 – metsales
@SiddharthRout 所以这可行，但现在我遇到了一个疯狂的自动化错误......它通过循环一次......发送 1 封电子邮件，然后当它到达 .subject = subject 我得到运行时错误 '-2147221238 (8004010a )': 自动化错误，据我所知与运行时错误 440 相同 – metsales
The problem is that you are creating the outlook item outside the loop by
问题是您正在循环外创建 Outlook 项目
Set objMail = olApp.CreateItem(0)
Outlook already sent that email and now for the next email you will have to re-create it. So move that line inside the loop.
Outlook 已经发送了该电子邮件，现在对于下一封电子邮件，您必须重新创建它。所以在循环内移动那条线。
For x = 1 To 3
Set objMail = olApp.CreateItem(0)
head = "<HTML><BODY><P>Hi " & Cells(x, 1).Value & ",</P>"
Body = "Blah Blah"
With objMail
.subject = subject
.To = Cells(x, 2).Value
.Attachments.Add = FilePath & "/" & Cells(x, 3).Value
.Attachments.Add = FilePath & "/" & Cells(x, 4).Value
.BodyFormat = olFormatHTML
.HTMLBody = head & Body
.Send
End With
Next x
