Excel VBA:发送的 Outlook 电子邮件不包括粘贴的范围 [英] Excel VBA: Sent Outlook email does not include pasted Range

问题描述

我有初步回答问题如何只在电子邮件正文中粘贴可见单元格

我测试和发布的代码(见下文)不包括发送电子邮件.在 OP 将其添加到他的问题之后，我添加了 .Send 部分，但我获得的行为非常奇怪.如果我在发送之前放置一个断点，并执行 Sub，则会创建一封包含正确信息(包括粘贴的 Excel Range)的电子邮件.然后我继续执行并正确发送电子邮件.但是，如果我一次运行整个 Sub，没有断点，则发送的电子邮件的正文中没有粘贴 Excel Range.

The code I tested and posted (see below) did not include sending email. After the OP added that to his question, I added the .Send part, but the behavior I obtained is very strange. If I place a breakpoint just before sending, and I execute the Sub, an email is created with the correct info (including the pasted Excel Range). Then I continue execution and the email is correctly sent. But if I run the whole Sub at once, with no breakpoints, the email is sent with no pasted Excel Range in the body.

这是什么原因，解决办法是什么?

What is the reason for that, and what is the solution?

我试过注释/取消注释最后两行(Set ... = Nothing)，但没有帮助.

I have tried commenting/uncommenting the two last lines (Set ... = Nothing), but it does not help.

相关问题:

从Excel 到 Outlook 中的电子邮件正文

将格式化的 Excel 范围粘贴到 Outlook 邮件中

参考代码(基于 Ron de Bruin 的典型代码，参见 这个和this):

Code of reference (based on the quintessential code by Ron de Bruin, see this and this):

Sub SendEmail()

    Dim OutlookApp As Object
    'Dim OutlookApp As Outlook.Application
    Dim MItem As Object
    'Dim MItem As Outlook.MailItem

    'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
    'Set OutlookApp = New Outlook.Application

    Dim Sendrng As Range
    Set Sendrng = Worksheets("Test").Range("A1").SpecialCells(xlCellTypeVisible)
    Sendrng.Copy

    'Create Mail Item
    Set MItem = OutlookApp.CreateItem(0)
    'Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
        .To = "[e-mail]"
        .To = "[e-mail]"
        .Subject = "Test"
        .CC = ""
        .BCC = ""
        '.Body = "a"
        .Display
    End With
    SendKeys "^({v})", True
    With MItem
        .Send
    End With

    'Set OutlookApp = Nothing
    'Set MItem = Nothing

End Sub

推荐答案

但是，如果我一次运行整个 Sub，没有断点，则发送的电子邮件正文中没有粘贴 Excel 范围.这是什么原因，有什么解决办法?

But if I run the whole Sub at once, with no breakpoints, the email is sent with no pasted Excel Range in the body. What is the reason for that, and what is the solution?

原因很简单.当您使用断点时，您为 Excel 提供了足够的时间进行复制粘贴.SendKeys 因此在使用其他应用程序时非常不可靠.

The reason is pretty simple. When you use breakpoint, you are giving Excel enough time for a copy paste. SendKeys hence are very unreliable when working with other applications.

有很多方法可以解决您的问题.一是留出足够的时间进行复制粘贴.您可以通过使用 DoEvents 或强制 Wait Time 来做到这一点.例如

There are many ways to solve your problem. One is to give enough time for a copy paste. You can do that by using DoEvents or forcing a Wait Time. For example

SendKeys "^({v})", True
DoEvents

或

SendKeys "^({v})", True
Wait 2 '<~~ Wait for 2 seconds

并在你的代码中使用这个子

and use this sub in your code

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

顺便说一句，您可以使用 Ron de Bruin 的 RangetoHTML 函数代替 SendKeys，如图 这里

BTW, instead of using SendKeys you can use the RangetoHTML function by Ron de Bruin as shown HERE

编辑

如果上述方法不起作用，则意味着 SendKeys 在这种情况下执行得太快，请在 .Display 之后立即使用 DoEvents/Wait代码>以及.

If the above doesn't work then that means SendKeys is getting executed too fast in that case, use DoEvents/Wait right after .Display as well.

.Display
DoEvents

或

.Display
Wait 2

这篇关于Excel VBA:发送的 Outlook 电子邮件不包括粘贴的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！