CODEWITHSUNDEEP
excelvba
chris
chris

Reputation: 141

How to disable e-mail creation screen displaying in outlook?

Below code displays e-mail creation screen in outlook each time i run it. i tried removing .display from the second function but it gives run-time error. I am new to VBA macros ,Please advice on how to hide the e-mail creation screen for each new e-mail that is being triggered. thanks a lot in advance.

Public Sub loopCheck()
Dim NumRows As Integer
Dim eID As String
Dim eName As String
Dim eEmail As String
Dim supportGroup As String
Dim managerEmail As String
Dim acName As String

Dim x As Integer
      Application.ScreenUpdating = False
      NumRows = Worksheets("Data").Range("A5", Range("A5").End(xlDown)).Rows.Count  ' Set numrows = number of rows of data.
      Worksheets("Data").Range("A5").Select ' Select first record.

      For x = 1 To NumRows  ' Establish "For" loop to loop "numrows" number of times.

        eID = Worksheets("Data").Range("A" & x + 4).Value
        eName = Worksheets("Data").Range("B" & x + 4).Value
        eEmail = Worksheets("Data").Range("C" & x + 4).Value
        supportGroup = Worksheets("Data").Range("F" & x + 4).Value
        managerEmail = Worksheets("Data").Range("G" & x + 4).Value
        acName = Worksheets("Data").Range("I" & x + 4).Value


        'Prepare table to be sent locally.
        Worksheets("Data").Range("AA5").Value = eID
        Worksheets("Data").Range("AB5").Value = eName
        Worksheets("Data").Range("AC5").Value = eEmail
        Worksheets("Data").Range("AF5").Value = supportGroup

        managerEmail = managerEmail + ";" + Worksheets("Data").Range("AA1").Value

        'Call Emails function.
        Call Emails(eEmail, managerEmail)

         ActiveCell.Offset(1, 0).Select
      Next

      Application.ScreenUpdating = True
End Sub

Public Sub Emails(y As String, z As String)

Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

Dim a As String
Dim b As String

a = y
b = z


Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)

With newEmail
    .To = a
    .CC = b
    .BCC = ""
    .Subject = "test loop"
    .Body = ""
    .display

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


    Worksheets("Data").Range("AA4:AF5").Copy

    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)

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

Set newEmail = Nothing
Set outlook = Nothing

End Sub

Upvotes: 1

Views: 641

Answers (1)

skkakkar
skkakkar

Reputation: 2838

You have two instances of .display in your Emails sub .If first instance is suppressed it locks the editor and would not allow program to run. Second instance can be commented out and .send is enough for program working. Still first instance will display on screen . 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. Method followed by you is a preferred method for embedding of images or charts in the HTML body.

your adopted code is similar to SO Question . Please refer to opening comments in Answer by Dmitry Streblechenko " --

You should also use MailItem.GetInspector instead of Application.ActiveInspector since the message is not yet displayed.

So if you want to suppress e-mail creation screen display, please adopt other approach as suggested earlier.

Further Eugene Astafiev also mentioned while answering a question HERE

That's a known issue in Outlook. You have to call the Display method first to get the inspector visible.

Otherwise it won't work.

I think you can not suppress display of e-mail creation screen display by invoking this approach.

Upvotes: 1

Related Questions