如何将活动的 Excel 工作簿附加到电子邮件-解网

问：

我整个上午都在尝试让这个VBA脚本将我的活动excel文档附加到自动生成的Outlook邮件中。如果我将文件路径声明为字符串并附加它，一切正常。除了我想附加当前 excel 文档的完整文件路径而不是使用静态字符串值。

这是我的代码：

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sAttach As String
    Dim sTo As String
    Dim sCC As String



    'For To field
    Set emailRng = Worksheets("Pre-Clearance Email").Range("E11:J14")

    For Each cl In emailRng
        sTo = sTo & ";" & cl.Value
    Next

    sTo = Mid(sTo, 2)

    'For CC field
    Set emailRngCC = Worksheets("Pre-Clearance Email").Range("E16:J19")

    For Each cl In emailRngCC
        sCC = sCC & ";" & cl.Value
    Next

    sCC = Mid(sCC, 2)



    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'variable declarations for email body and attachment
    strbody = "<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>Please see the attached aliases for validation. Please let me know if you have any questions.<p>Thank you.</BODY>"
    sAttach = "K:\CRM Support\Data\Systematic Trade Recon (1).xlsm"

    'the below code adds a users default signature to the email
    With OutMail
        .Display
    End With
        signature = OutMail.HTMLBody

    With OutMail
        .to = sTo
        .CC = sCC
        .Subject = "STR Pre-Clearance"
        .HTMLBody = strbody & signature
        .Attachments.Add (ActiveDocument.FullName)

        '.Attachments.Add sAttach
        .Display 'Instead of .Display, you can use .Send to send the email _
                    or .Save to save a copy in the drafts folder
    End With

编译器在这一行给了我一个错误：

.Attachments.Add (ActiveDocument.FullName)

我已经做了一些研究，并试图自己解决问题，但我只是无法弄清楚如何使此脚本将活动文件附加到此Outlook邮件。正如你从我的代码中看到的，我的备份选项是只使用一个字符串变量和一个静态地址来附加文件，但我宁愿让这个脚本比这更通用。

这是我找到的一个网站，它给了我这个想法：这里

Excel VBA 展望

如何将活动的 Excel 工作簿附加到电子邮件

How to attach active Excel workbook to an email

评论

评论