个人中心
gpt4 book ai didi

vba - Excel VBA 发送带有多个附件的电子邮件

转载 作者：行者123 更新时间：2023-12-02 10:49:23 25 4
gpt4 key购买 nike

所以我们要举办这个大型事件，我有一张 Excel 表格，其中包含每个人的姓名、电子邮件地址以及他们的行程文件(其中有 2 个)Cells(x, 3) 和 单元格(x，4)。我想做的是沿着专栏向每个人发送一封包含他们所有信息的“个性化”电子邮件。

在代码中，for 循环仅转到 3，因为我只是通过向自己发送电子邮件来测试它，并且不希望最终收到 1000 封电子邮件:P

我在尝试添加附件的行不断收到运行时错误 440(自动化错误)...不确定发生了什么或如何补救，感谢任何帮助

代码

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 已经解决了您的一个问题

问题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

您正在从 Excel 与 Outlook 进行后期绑定(bind)，并且 olFormatHTML 是 Outlook 常量，因此 Excel 无法识别它。在 MS-Outlook 的立即窗口中，如果您键入?olFormatHTML，那么您会注意到该常量的值为2

enter image description here

因此我们必须在 Excel 中声明该常量。就像我提到的，您可以将 Const olFormatHTML = 2 放在代码顶部，也可以将 .BodyFormat = olFormatHTML 替换为 .BodyFormat = 2

问题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

问题是您正在循环之外创建 Outlook 项目

Set objMail = olApp.CreateItem(0)

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

关于vba - Excel VBA 发送带有多个附件的电子邮件，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19911642/

25 4 0
文章推荐： c# - 为什么类似虚拟场的事件以 C# 中的方式工作？
文章推荐： haskell - 约束多态: variable vs.函数参数
文章推荐： sql-server - 通过 ADO 检索存储过程输出参数
文章推荐： asp.net - IIS 在编译的 .net 站点中查找 .cs 文件
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com