Excel, can't quit Word instance VBA

[chungiemo]

Hi

The code below creates a outlook email and copies the text in the word document to the email and sents each user their details.

The code works (found a good bit of code and reused it) which copys a word document to a outlook body, but i am trying to close the instance of the word each time, but its not exiting word for me. I have tried to quit the word but to no avail and wonder if anyone can help?

I have attached the code as it may be helpful for someone doing a similar piece.

'First Run Create Mail from List 
 
Sub Create_Mail_From_List() 
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010. 
     
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim cell As Range 
     
    Dim objSelection 
 
    Dim wd As Object, editor As Object 
    Dim doc As Object 
    Dim oMail As MailItem 
     
    'Dim wd As Word.Application 
    'Dim doc As Word.Document 
     
    Dim oOutlookApp As Outlook.Application 
 
    Dim myMail As Outlook.MailItem 
     
    Application.ScreenUpdating = True 
     
    Set OutApp = CreateObject("Outlook.Application") 
 
    'Sent Username Email Code 
    On Error GoTo cleanup 
    'Email Address Field is column G 
    For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants) 
        'Checkbox Status column H 
        If cell.Value Like "?*@?*.?*" And _ 
           LCase(Cells(cell.Row, 8).Value) = "yes" Then 
 
            Set OutMail = OutApp.CreateItem(0) 
            On Error Resume Next 
            With OutMail 
                'Sent email from Group email address 
                OutMail.SentOnBehalfOfName = "test@net" 
                .To = cell.Value 
                .Subject = "test" 
                 
                'Replace Employee Name at Column D 
                RecipientName = "Dear " & Cells(cell.Row, "E").Value _ 
                & vbNewLine & vbNewLine 
  
                'Copy the Word Contents 
                Set wd = CreateObject("Word.Application") 
                Set doc = wd.Documents.Open("C:\Users\test\Word Template.docx") 
                 
                'Add extra line to word document 
                wd.Visible = True 
                Set objSelection = wd.Selection 
                objSelection.TypeText (RecipientName) 
                
                'Copy word doc text 
                 doc.Content.Copy 
     
                'paste word document to body of email 
                .GetInspector.Display 
                .BodyFormat = olFormatRichText 
                Set editor = .GetInspector.WordEditor 
                editor.Content.Paste 
                .Display 
                 
                'Close word document without saving 
                doc.Close SaveChanges:=wdDoNotSaveChanges 
                 
                'quit word instance 
                doc.Visible = True 
                Application.SendKeys ("{ENTER}") 
                doc.Close 
                wd.Quit 
                AppActivate wd 
                Application.SendKeys ("{ENTER}") 
                Set wd = Nothing 
                      
                'Update Excel Audit Email Sent 
                Cells(cell.Row, "H").Value = "Sent" 
                Cells(cell.Row, "I").Value = "Mail Sent: " & Date + Time 
                         
                 'Cells(i, 5).Font.Bold = True 
 
                'You can also add files like this: 
                '.Attachments.Add ("Y:\test1.docx") 
                 
                '.Send  'Or use Display. 
                 
                'Disable the mail security dialog to Send/not Send the mail 
                '.Display 
                Application.Wait (Now + TimeValue("0:00:02")) 
                Application.SendKeys "%s" 
         
  
            End With 
             
            On Error GoTo 0 
            Set OutMail = Nothing 
        End If 
    Next cell 
 
cleanup: 
    Set OutApp = Nothing 
    Application.ScreenUpdating = True 
End Sub  

[chungiemo]

Fogot to add with the word instance it does display the message "You placed a large amount of text to the clipboard" not sure if this is whats causing it. Should I clear the clipboard before quitting word.

[LJMetzger]

Hi chungiemo,

You have a lot of moving parts. I have the following suggestions which may help you.

a. Move the following line before the 'for each' loop:

Please Login or Register  to view this content.

b. Move the following lines after the 'next cell' line:

Please Login or Register  to view this content.

c. Delete the following lines inside the for next loop:

Please Login or Register  to view this content.

d. Add the following code at the top of your code module:

Please Login or Register  to view this content.

e. Add the following code after the editor.Content.Paste line:

Please Login or Register  to view this content.

f. The following lines are probably not needed. In general 'SendKeys' is used when there is no other way to do something. This code looks like it originated many years ago when 'SendKeys' was probably needed.

Please Login or Register  to view this content.

If you need additional help please upload a sample workbook. It is difficult for us to recreate what you are doing without one.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Lewis