Sending mail with macro is creating Excel ghost processes

[Davy80]

Dear all,

Who can help me with the code below.
I'm not an expert on vba and the code is creating 2 ghost processes of excel which will not be released once the mail is send.

Mailing works fine, but somehow excel won't release the processes.

Can someone help me to get the correct code?
It problably has something to do wtih the .to and .subject because their value are read from the excel file .

I can't kill excel enterely because people using this workbook may have other excel files opened..

Here's the code:

Sub Mail_PE()

Dim Response As Integer

' Displays a message box with the yes and no options.
Response = MsgBox(prompt:="Mail DCM to the Product Engineer?", Buttons:=vbYesNo)

' If the Yes is selected
If Response = vbYes Then

'Step 1:  Declare variables

Dim OLApp As Object
Dim OLMail As Object

'Step 2:  Open Outlook start a new mail item


Set OLApp = CreateObject("Outlook.Application")
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon

'Step 3:  Build your mail item and send

 With OLMail

    .to = Worksheets("Macro Data").Range("F14").Text
    .CC = ""
    .BCC = ""
    .Subject = Worksheets("Macro Data").Range("D8").Text
    .HTMLBody = _
    "[I]...stuff here with link to file in body....."[/I]
  

   ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail is not send. Please send manual.", vbExclamation
    Else
      MsgBox "E-mail send succesfully. We will start on the DCM asap.", vbInformation
    End If
    On Error GoTo 0

  End With
 
'Step 4:  Memory cleanup

    Set OLMail = Nothing
    Set OLApp = Nothing
  Else
         ' If the No-button is selected.
         Exit Sub

      End If
 End Sub

 

[RoryA]

Welcome to the forum.

Assuming you are running this code in Excel, I can't see anything in the part you have posted that would cause ghost processes.

 

[Davy80]

Thanks!

Yes, this code is running in Excel.
There are more macro's in this workbook. the 2 ghost processes are caused when i run this code.
Is it possible one of the other macro's is causing these processes? They don't run or are accessed when this code is started.

 

[Davy80]

RoryA,

I've found it! a Lucky guess....

After the .sent action there is a Application.Visible = True

Deleting that line at all resolves the problem.

 

[RoryA]

I doubt it. Is there anything Excel related in this part:

    .HTMLBody = _
    "...stuff here with link to file in body....."

or is that literally the code you are testing?

 

[Davy80]

This is the full code. I removed the Application.Visible = True and this exact code is running perfectly without the ghost processes

Sub Mail_Product_engineer()

Dim Response As Integer
      
' Displays a message box with the yes and no options.
      Response = MsgBox(prompt:="Mail DCM to the Product Engineer?", Buttons:=vbYesNo)
     
 ' If the Yes is selected
      If Response = vbYes Then

'Step 1:  Declare variables
        
    Dim OLApp As Object
    Dim OLMail As Object

'Step 2:  Open Outlook start a new mail item
    
    
    Set OLApp = CreateObject("Outlook.Application")
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
    
'Step 3:  Build your mail item and send
    
With OLMail
  
    .to = Worksheets("Macro Data").Range("F14").Text
    .CC = ""
    .BCC = ""
    .Subject = Worksheets("Macro Data").Range("D8").Text
    .HTMLBody = _
    "[FONT=segoe ui] Beste, [/FONT]
" & _
    "[FONT=segoe ui] Graag bovengenoemde DCM controleren en goedkeuren [/FONT]
" & _
    "[FONT=segoe ui] Indien de DCM wordt goedgekeurd, dan graag de workflow starten. [/FONT]
" & _
    "[FONT=segoe ui] Zie onderstaande link om de DCM te openen. Je kunt de link openen door erop te klikken met CTRL ingedrukt te houden[/FONT]
" & _
    "[FONT=segoe ui] [URL="https://www.mrexcel.com/forum/& Chr(34) & Worksheets("]Link naar DCM File[/URL][/FONT]
" & _
    "[FONT=segoe ui] Met vriendelijke groet,[/FONT]
"
 
   ' Try to send
    On Error Resume Next
    .Send
    
    If Err Then
      MsgBox "E-mail is niet verstuurd. Verstuur handmatig. Zie map: O:\Public\Engineering\DCM Database\DCM Files\", vbExclamation
    Else
      MsgBox "E-mail succesvol verzonden. Wij zullen de DCM in behandeling nemen.", vbInformation
    End If
    On Error GoTo 0
   
  End With
'Step 4:  Memory cleanup
    
    Set OLMail = Nothing
    Set OLApp = Nothing
  Else
         ' If the No-button is selected.
         Exit Sub
         
      End If
End Sub

 

[Davy80]

I removed some css tags like p & a because the post keeps the html for some reason.

 .to = Worksheets("Macro Data").Range("F14").Text
    .CC = ""
    .BCC = ""
    .Subject = Worksheets("Macro Data").Range("D8").Text
    .HTMLBody = _
    " Beste," & _
    " Graag bovengenoemde DCM controleren en goedkeuren " & _
    " Indien de DCM wordt goedgekeurd, dan graag de workflow starten." & _
    " Zie onderstaande link om de DCM te openen. Je kunt de link openen door erop te klikken met CTRL ingedrukt te houden" & _
    " a href=" & Chr(34) & Worksheets("Macro Data").Range("D9") & Chr(34) & ">Link naar DCM File /a" & _
    "Met vriendelijke groet,"

 

[RoryA]

This is the full code. I removed the Application.Visible = True and this exact code is running perfectly without the ghost processes

That is very strange! Had you hidden Excel? If not, why did you have that line there?

 

[Davy80]

The only workbook that is hidden is my Personal.xlsb. There i have stored some macro's which i acces from the Ribbon.

I think the Application.Visible is a left-over from another workbook.
I did copy that code for the most part from another workbook i've created several years ago and that macro closes Excel all together after it's finished.
I did check it just now and there the ghost process is also visible for a short blink but it's gone because Excel closes all together.

I'm happy that my problem is resolved, but i find it strange that that single line calls for 2 new instances of Excel.EXE in the task manager.

 

[RoryA]

It certainly shouldn't cause that!