excel 如何在电子邮件VBA宏中添加正文时粘贴到图片已在使用?

gajydyqb  于 2023-05-08  发布在  其他
关注(0)|答案(2)|浏览(260)

因此,我有一个宏在Excel VBA,它起草了一封电子邮件与收件人,主题,并粘贴一个表使用选定的范围。当我使用身体功能时,它不会增加身体。我该怎么做?这是我目前所做的。

Sub Mail_Button()

    Dim rng As Range
    Dim olApp As Object
    Dim Email As Object
    Dim Sht As Excel.Worksheet
    Dim wdDoc As Word.Document
    Dim wd As Date
    wd = WorksheetFunction.WorkDay(Date, -1)
    
    Set Sht = ActiveWorkbook.Worksheets("Report & Email")
    Set rng = Sht.Range("B1:F9")
        rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set olApp = CreateObject("Outlook.Application")
    Set Email = olApp.CreateItem(0)
    Set wdDoc = Email.GetInspector.WordEditor

    With Email
        .To = "person@gmail.com"
        .Subject = "Report - For " & Format(wd, "mmmm dd, yyyy") & " Session"
        .Body = "Please see attached the report for yesterday."
        
        wdDoc.Range.PasteAndFormat Type:=wdChartPicture
        
    End With
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Set Email = Nothing
    Set olApp = Nothing
    
    MsgBox ("Done!")
    
End Sub
juzqafwq

juzqafwq1#

身体是正确的。但是Inspector.WordEditor.PasteAndFormat似乎在邮件实际显示后覆盖了正文(关于这一点,请参阅Debug.Print消息)-从我的测试显示:只有在Displaying之后才实际设置新的(和最终的)主体。
有了这些知识,我们就可以在粘贴发生后获得邮件的HTMLBody。HTMLBody只是一个字符串,以HTML格式表示邮件正文。在这个字符串中,我们可以插入我们自己的HTML(例如,可以是您想要的附加文本)。在我的示例中,我删除了结束标记(进入html(标记)的body(标记);添加了我需要的元素(在我的例子中是一个div元素,里面有一些文本,然后再次添加了删除前的结束标记。这样我就在HTML Body中插入了一些内容。
仅仅使用body属性是不起作用的,因为表不能转换成字符串(只能转换成HTML格式的字符串)

With Email
    .To = "person@gmail.com"
    .Subject = "Report - For " & Format(wd, "mmmm dd, yyyy") & " Session"
    .Body = "Test"
    Debug.Print "body before Paste: " & .Body
    wdDoc.Range.PasteAndFormat Type:=wdChartPicture
        
    Debug.Print "body after paste and before display: " & .Body
    .Display
    Debug.Print "body after display: " & .Body
        
    myHTMLBody = .HTMLBody
    myHTMLBody = Replace(myHTMLBody, "</body></html>", "")
    myHTMLBody = myHTMLBody & "<div> Test text that is shown below the table </div></body></html>"
    .HTMLBody = myHTMLBody
End With

你也可以删除HTML-Body的开头标签和标记,以便在Table之前插入Elements。
另一种方法是用Inspector.WordEditor构造整个邮件(所以不仅仅是粘贴,还有文本添加)。但如果这将如何工作,我不知道,因为我从来没有使用过检查员。WordEditor在VBA中构建邮件

jmp7cifd

jmp7cifd2#

尝试

Sub SendEmailWithRange()
    Dim rng As Range, x
    Dim doc As Object
    Dim Sht As Worksheet
    Dim wd As Date
    wd = WorksheetFunction.WorkDay(Date, -1)
    
    Set Sht = ActiveWorkbook.Worksheets("Report & Email")
    Set rng = Sht.Range("B1:F9")
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    
    With CreateObject("outlook.application").CreateItem(0)
        .Display'Change to .Send to send the email immediately
        
        .Body = "Please see attached the report for yesterday."
        
        Set doc = .GetInspector.WordEditor
        
        x = doc.Range.End - 1
        rng.Copy
        doc.Range(x).Paste
            
        .To = "person@gmail.com"
        .Subject = "Report - For " & Format(wd, "mmmm dd, yyyy") & " Session"
        
        Application.CutCopyMode = 0
    End With
    
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

相关问题