excel - 如何使用 vba 将 word doc 和 excel 中的内容添加为电子邮件？

Question

我想要从 word doc 和 excel 文件中获取的电子邮件正文

Sub SendMailItem()        
    Dim Answer As VbMsgBoxResult        
    Answer = MsgBox("Are you sure you want to run the macro?", vbYesNo, "Run Macro")

    If Answer = vbYes Then        
        Dim sh As Worksheet            
        Set sh = ThisWorkbook.Sheets("Sheet1")

        Dim OA As Object        
        Dim msg As Object        
        Set OA = CreateObject("Outlook.Application")

        Dim i As Integer        
        Dim last_row As Integer        
        last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

        For i = 2 To last_row        
            Set msg = OA.CreateItem(olMailItem)        
            Dim wd As Object, editor As Object
            Dim doc As Object
            Set wd = CreateObject("Word.Application")
            wd.visible = True

            Set doc = wd.Documents.Open(FileName:="C:\Users\usengupta\Desktop\test\Test word Document.docx", ReadOnly:=True)    
            doc.Content.Copy        
            doc.Close

            Set wd = Nothing

            With msg    
                .To = sh.Range("A" & i).Value        
                .CC = sh.Range("B" & i).Value
                .Subject = sh.Range("C" & i).Value        
                .Body = olRichFormatText        
                .Attachments.Add sh.Range("E" & i).Value

                Set editor = .GetInspector.WordEditor        
                editor.Content.Paste        
                .SentOnBehalfOfName = "[e-mail]"        
                .Display        
                .Send        
            End With    
        Next i

        MsgBox "Mail Sent"        
    End If    
End Sub

标签： excelvba