4

I have initially answered question How to only paste visible cells in an email body

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?

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

Related questions:

Copy range of cells from Excel to body of email in Outlook

Pasting formatted Excel range into Outlook message

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 = "test@email.com"
        .To = "SSEREBRINSKY@TENARIS.COM"
        .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
Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185

1 Answers1

6

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?

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.

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

Or

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

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

EDIT

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

Or

.Display
Wait 2
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • +1: I second using the exact code for pasting the `Range` you want into the body. This is what I use as well. The only big downside to it is the `Body` gets converted to `HTMLBody`, which limits some things you want to do (and changes the default mail font to Times New Roman for me, icky but quite easy to solve). – WGS Jan 07 '14 at 03:02
  • 1
    @BK201: True :) One can always use `SendMessage` API ;) – Siddharth Rout Jan 07 '14 at 03:03
  • 1
    Something new to learn everyday. Will read up on this `SendMessage` API. Looks promising, and it might even solve this issue I have with sending charts and graphs to the mail body. ;) – WGS Jan 07 '14 at 03:07
  • I confirm that only adding a `DoEvents` after `SendKeys` was enough. I suspected it had to do with some (unwanted) fast processing of the email, but I did not know about `DoEvents` (see [documentation](http://support.microsoft.com/kb/118468)). Awesome. – sancho.s ReinstateMonicaCellio Jan 07 '14 at 03:24
  • + 1 I can only see your answers everywhere :) – Pradeep Kumar Jan 07 '14 at 10:35