I am trying to createa macro in excel which will send an email to a large number of people bycopying the contents of a word file as the body of the email. The list of emailaddresses is listed in column A of sheet1. I modified the code below from a post I found earlier. My problem istwofold; firstly when I run it, the macro hangs and I get a message that thedocument is locked for editing by XXX and do I want to open it as read only. This is despite the fact that the document isnot open nor is Word running. Secondly, Iget a run time 91 Error “Object variable or with block variable not set” Theproblem line is:
SetoMailWordDoc = oOutApp.ActiveInspector.WordEditor
Could someone pleasegive me the benefit of their expertise? I am running excel, word & outlook2010.
SetoMailWordDoc = oOutApp.ActiveInspector.WordEditor
Could someone pleasegive me the benefit of their expertise? I am running excel, word & outlook2010.
Code:
Sub CopyBodyFromWord()
Dim oOutApp As Object
Dim oMailItem As Object
Dim oWordApp As Object
Dim oWordDoc As Object
Dim oMailWordDoc As Object
Dim WDocName As String
For j = 1 To 3 'use cells 1 to 3 in column "A" as a test run
If Sheets("SHEET1").Range("A1").Value <> "" Then
myadd = myadd & ";" & Sheets("SHEET1").Range("A" & j).Value
End If
Next
On Error GoTo CleanUp
WDocName = InputBox("What is the name of the word file to be copied to the email?")
Set oWordApp = CreateObject("Word.Application")
Set oWordDoc = oWordApp.Documents.Open("C:\Users\Janet & Peter\Documents\" & WDocName)
oWordDoc.Content.Copy
Set oOutApp = CreateObject("Outlook.Application")
Set oMailItem = oOutApp.CreateItem(0)
With oMailItem
.To = ""
.Bcc = myadd
.Subject = "Test Email"
End With
Set oMailWordDoc = oOutApp.ActiveInspector.WordEditor
oMailWordDoc.Application.Selection.Paste
CleanUp:
oWordApp.Quit
Set oMailWordDoc = Nothing
Set oMailItem = Nothing
Set oOutApp = Nothing
Set oWordDoc = Nothing
Set oWordApp = Nothing
End Sub