Excel VBA发送带有多个附件的电子邮件 [英] Excel VBA Sending emails with multiple attachements
问题描述
因此，我们正在举行此大型活动，我有一个excel表，其中包含每个人的姓名，电子邮件地址以及其行程文件(其中有2个)Cells(x, 3)和Cells(x, 4).我正在尝试做的是在专栏中浏览并向所有人发送个性化"电子邮件，其中包含他们的所有信息.
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.
在代码中，for循环仅进行3次，因为我只是通过向自己发送电子邮件来测试它，并且不想最终收到1000封电子邮件:P
In the code, the for loop 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
在尝试添加附件的行中，我不断收到运行时错误440(自动化错误) ...不确定发生了什么或如何补救它，对您有所帮助
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
代码
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
推荐答案
在上面的评论中，@ bamie9l已经解决了您的一个问题
Further to the above comments, @bamie9l has already solved one problem of yours
问题2
@ bamie9l太棒了！那行得通，但是现在在.BodyFormat = olFormatHTML行，我得到运行时错误"5":无效的过程调用或参数– 13分钟前metsales
@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
您正在与Excel中的Outlook进行后期绑定，并且olFormatHTML是Outlook常量，因此Excel无法识别它.在MS-Outlook的Immediate Window中，如果键入?olFormatHTML，则将注意到该常数的值为2
You are latebinding with Outlook from Excel and olFormatHTML is an Outlook constant and hence Excel is unable to recognize it. In the Immediate Window of MS-Outlook if you type ?olFormatHTML then you will note that the value of that constant is 2
因此，我们必须在Excel中声明该常量.就像我提到的，您可以将Const olFormatHTML = 2放在代码的顶部，也可以将.BodyFormat = olFormatHTML替换为.BodyFormat = 2
Hence we have to declare that constant in Excel. Like I mentioned, either you can put Const olFormatHTML = 2 at the top of the code or replace .BodyFormat = olFormatHTML by .BodyFormat = 2
问题3
@SiddharthRout这样就可以了，但是现在我遇到了一个疯狂的自动化错误...它遍历了循环..发送1封电子邮件，然后当它上升到.subject = subject时，我收到运行时错误'- 2147221238(8004010a)':据我所知，自动化错误与运行时错误440 – metsales相同
@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
问题在于，您是通过以下方式在循环之外创建Outlook项目的
The problem is that you are creating the outlook item outside the loop by
Set objMail = olApp.CreateItem(0)
Outlook已经发送了该电子邮件，现在，对于下一封电子邮件，您将不得不重新创建它.因此，将那条线移动到循环内.
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.
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
这篇关于Excel VBA发送带有多个附件的电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
