Pasting to Outlook from Excel - Outlook Screen Updating

Question

I have found the code to paste a chart from excel to outlook here on stack over flow.

This works fine but The issue is the outlook creating new email and pasting procedure is getting displayed on the screen. Is there any way to disable or make this to background?

Sub Mail_Range()
    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.ActiveSheet

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Dim rng As Range
    Set rng = Sht.Range("A5:W20")
        rng.Copy

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

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(0)

    Dim vInspector As Object
    Set vInspector = OutMail.GetInspector

    Dim wEditor As Object
    Set wEditor = vInspector.WordEditor

    With OutMail
        .TO = "xxx.xxx.com"
        .CC = ""
        .Subject = Sht.Range("A5").Value
        .GetInspector
         wEditor.Paragraphs(1).Range.Text = "This is an auto generated e-mail" & vbCr
         wEditor.Paragraphs(2).Range.Paste
        .send
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    Application.CutCopyMode = False

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

When I'm using

With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

after outlook application creation my pasting code doesn't work. Im getting an email with no content.

Your line `.ScreenUpdating = Fales` should be `.ScreenUpdating = False`. If it's not that... There is nothing else on your code that would make excel to show you the procedure. — Damian, Mar 20 '19 at 08:01
I recommend always to activate `Option Explicit` to avoid such typos: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. — Pᴇʜ, Mar 20 '19 at 08:01
@Damian That's small typo. When I use this my pasting code doesnt work. I'm getting a blank email with no content. I have used Option Explicit — Manu Mohan, Mar 20 '19 at 08:09
That happens to me too, you can't have `ScreenUpdate = False` when pasting images... I turn it on, but then you can see how the procedure is done, because I don't use `Select` or `Activate` doing the images my procedure only shows a small move on every image. Turn on your screenupdating and don't use select, is not that big of a problem if there are few images. — Damian, Mar 20 '19 at 08:12
Please explain if what you want is that Excel sends the email with outlook closed or without displaying the outlook window. Anyway, check the code from Ron de Bruin to have a different approach: https://www.rondebruin.nl/win/s1/outlook/bmail2.htm — Ricardo Diaz, Mar 20 '19 at 10:02

score 0 · Answer 1 · answered Mar 20 '19 at 08:05

0

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

With Application  <<<---- change to OutApp
    .ScreenUpdating = False
    .EnableEvents = False
End With

answered Mar 20 '19 at 08:05

Peasodecesar

1
1

When I use this my pasting code doesn't work. I'm getting a blank email with no content. – Manu Mohan Mar 20 '19 at 08:09
This raises an error as OutApp is an object that doesn't have those properties. – Ricardo Diaz Mar 20 '19 at 09:47

score 0 · Answer 2 · answered Mar 20 '19 at 08:16

If your problem is that you don't want to see the whole procedure of the mail, but turning the ScreenUpdating to false shows you a blank image, here is my code for dealing this:

            Call AhorroMemoria(False)
            Imagen.CopyPicture xlScreen, xlBitmap
            With wsM.ChartObjects.Add(Imagen.Left - Imagen.Left * 0.15, Imagen.Top - Imagen.Top * 0.15, _
            Imagen.Width - Imagen.Width * 0.15, Imagen.Height - Imagen.Height * 0.15)
                .Activate
                wsM.Shapes("Gráfico 1").Line.Visible = msoFalse
                .Chart.Paste
                .Chart.Export wb.Path & "\" & Servicio & Contador & ".jpg", "JPG"
            End With
            Call AhorroMemoria(True)

Call AhorroMemoria(False) turns on everything, screenupdating, enablevents and so... I do that just when copying the image as you can see on the code, then I turn it all off again on the Call AhorroMemoria(True).

Hope it helps.

I'm using a procedure to create and temporary store the images attached to the mail, on this procedure I reenable both `ScreenUpdating` and `EnableEvents` just before doing the copy and paste of the image. It's my way to do it. — Damian, Mar 20 '19 at 08:29

score 0 · Accepted Answer · answered Mar 20 '19 at 10:33

Thanks all for helping me out. All your codes helped in some way. But i have found more simpler code from Microsoft here. I'm not sure which versions will support this and it has any other challenges. For now this works for me in Office 2016.

I'm getting an email envelope for brief amount of time in excel but no issues as the accidental edit can't be done in this method. In the original method accidental edit was possible during the paste function is running.

Also this code seamlessly emailing charts on the excel sheet.

Option Explicit    
Sub Send_Range()

       ' Select the range of cells on the active worksheet.
       ActiveSheet.Range("A1:B5").Select

       ' Show the envelope on the ActiveWorkbook.
       ActiveWorkbook.EnvelopeVisible = True

       ' Set the optional introduction field thats adds
       ' some header text to the email body. It also sets
       ' the To and Subject lines. Finally the message
       ' is sent.
       With ActiveSheet.MailEnvelope
          .Introduction = "This is a sample worksheet."
          .Item.To = "E-Mail_Address_Here"
          .Item.Subject = "My subject"
          .Item.Send
       End With
    End Sub

Though you have submitted your answer and closed the issue please give a try to my answer posted a few minutes ago just to share your viewpoint on the approach adopted by me. — skkakkar, Mar 20 '19 at 11:03

skkakkar · Answer 4 · 2019-03-20T15:50:20.960

I think you can not suppress display of e-mail creation screen display by invoking Word Editor approach. If you go through previous SO Posts and comments of experienced experts it gets amply clear that you can not suppress display of e-mail creation screen display.

In order to totally disable e-mail creation screen display please take reference from programs at roundebruin which covers all types of possibilities of sending emails without displaying email creation screen. Here is a slight variation to your code which works for me and posted, if someone finds it useful for similar situation.

Public Sub Emails()
Dim str As String
Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
str = ws1.Range("A5").Value
With newEmail
    .To = "xxx.xxx.com"
    .CC = ""
    .BCC = ""
    .Subject = str
    .body = ""
    .display

    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor

   'Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    ws1.Range("A5").Copy
    pageEditor.Application.Selection.Paste xlValues
    ws1.Range("A5:W20").Copy
    pageEditor.Application.Selection.Paste xlValues

    .send
    Set pageEditor = Nothing
    Set xInspect = Nothing
End With

Set newEmail = Nothing
Set outlook = Nothing
With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Pasting to Outlook from Excel - Outlook Screen Updating

4 Answers4