Excel VBA opens multiple Word applications leading to errors

Question

I'm trying to deploy a program in Excel VBA to send mail, copying and pasting text from Word documents.

My program runs but at a certain point it throws errors and I see there are a lot of Word applications open so I have to close them with the Task Manager. I tried to use the Object.Quit function to set the object to Nothing.

I think that the random errors of the program have their root in the bad use of my computer memory. I have no idea of how use the memory in the computer because my background has nothing to do with programming.

Sub CustomizedMail()

    Dim wd As Object, editor As Object
    Dim outlookApp As Outlook.Application
    Dim mymail As Outlook.MailItem
    Dim doc As Object
    Dim generalDirectory As String
    Dim document As String
    Dim ActiveRow As Integer
    Dim mailType As String

    Break = Chr(13) + Chr(10)

    'Selects address of letters to Clients
    generalDirectory = "C:\Users\Rodrigo\OneDrive - InBody Co., Ltd\Ventas Rod\Forecast\Ppts informativas x área\Para enviar\"
    'Selects document to be sent according to ppt type value in worksheet
    ActiveRow = ActiveCell.Row
    mailType = ActiveCell.Worksheet.Range("O" & ActiveRow).Value

    'Check mailType
    If mailType = "" Then
        MsgBox "Selecciona un tipo de mail"
        Exit Sub
    End If

    'Opens word document and copies its information
    document = generalDirectory & mailType & ".docx"
    Set wd = CreateObject("Word.Application")
    Set doc = wd.documents.Open(document)
    'wd.Visible = True
    doc.Content.Copy
    doc.Close
    'Set wd = Nothing

    'Opens Outlook and paste

    Set outlookApp = New Outlook.Application
    'CreateObject("Outlook.Application") 'New Outlook.Application
    Set mymail = outlookApp.CreateItem(olMailItem)
    With mymail
        On Error GoTo 1
        .To = ActiveCell.Worksheet.Range("N" & ActiveRow)
        If mailType = "Presentación" Then
            .Subject = "Bioimpedanciómetros profesionales InBody"
        Else
            .Subject = "Bioimpedanciómetros para " & mailType
        End If
        '.BodyFormat = olFormatRichText
        Set editor = .GetInspector.WordEditor
        editor.Content.Paste
        'editor.Quit
        Set editor = Nothing
        .Display
    End With
    'Append corresponding file
    sourceFile = generalDirectory & "INBODY - " & mailType & ".pdf"
    mymail.Attachments.Add sourceFile

    ActiveCell.Worksheet.Range("T" & ActiveRow).Value = "Yes"
    ActiveCell.Worksheet.Range("V" & ActiveRow).Value = Date
    'MsgBox ThisWorkbook.FullName
    'MsgBox ThisWorkbook.Path
    Exit Sub
1:          MsgBox "Excel se puso pendejo, intenta de nuevo"
End Sub

In your code , you don't have wd.Quit and Set wd = Nothing is commented out. Is this intended? — DecimalTurn
Set wd = CreateObject("Word.Application") will create a new instance of Word each time it's run. What you should do is to use On Error Resume Next before Set wd = Word.Application and create a new instance only if an error occurs (meaning there is no loaded instance of Word). The probable reason why Wd.Quit appears to have no effect is because you have so many instances open that one being shut down makes no difference. Much the same applies to your handling of the Outlook application. — Variatus
Thanks for your answer, .Quit and wd = Nothing are commented because I did experiments to see if they work and as they seems not to function in my code I let them commented. I will try the On Error Resume Next. Do you know why an error could happen? I thought that with computers errors have as main cause code issues, what should I do to avoid this type of errors? — Rodrigo Reyes

Sam Sam · Accepted Answer · 2020-03-25T08:31:26

You can solve a lot of your problems by reusing your objects. Try something like this:

Sub SendALotOfMails()
    Dim wd as Object
    Dim outlookApp as Object

    Set wd = CreateObject("Word.Application")
    Set outlookApp = New Outlook.Application

    ' Reusing word and outlook objects
    CustomizedMail wd, outlookApp
End Sub

Sub CustomizedMail(wd As Object, outlookApp as Object)
    ...
End Sub

This is obviously only part of the solution.

Excel VBA opens multiple Word applications leading to errors

1 Answers