Word Query via Excel

[Chris Williamson]

Hi Guys,

This code I've got below does:
1) Opens a userform - the user completes the user form.
2) A new email message then opens.
3) MS Word then opens with some text data populated into some bookmarks.


My query is...
1) When the word document is opened, this window on my screen is inactive, so how do I make the word document the active window on my screen?
2) Then when it is the active window how do I get the code to 'protect' the word document for me?


Sub Test()

' show the userform
Form1.Show

' loading message - timed for 2 seconds
CreateObject("WScript.Shell").Popup _
"Loading... Please Wait", 2, "Loading..."

' open microsoft outlook for the new email message
Set olApp = CreateObject("Outlook.Application")
Set newmessage = olApp.CreateItem(0)
newmessage.SentOnBehalfOfName = Worksheets("Sheet1").Range("A1").Value
newmessage.To = Worksheets("Sheet2").Range("A2").Value
newmessage.body = "Dear Sir/Madam," & Chr(13) & Chr(13) _
& "Please see the attached file, we are looking forward to your reply as soon as possible." & Chr(13) & Chr(13) _
& "Thank you." & Chr(13) & Chr(13) & Chr(13) _
& "**** Please Insert Your Email Signature Here ****"
newmessage.Subject = "Information"
newmessage.Display
Set olApp = Nothing
Set newmessage = Nothing

' loading message - timed for 2 seconds
CreateObject("WScript.Shell").Popup _
"Opening Microsoft Word... Please Wait", 2, "Loading..."

' open microsoft word
Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Add "File Path\Folder\Folder\Folder\File Name.dot"
With objWord.ActiveDocument
.Bookmarks("Name1").Range.Text = ws.Range("M1").Value
.Bookmarks("Name2").Range.Text = ws.Range("M2").Value
.Bookmarks("Name3").Range.Text = ws.Range("M3").Value
.Bookmarks("Name4").Range.Text = ws.Range("M4").Value
.Bookmarks("Name5").Range.Text = ws.Range("M5").Value
End With
Set objWord = Nothing

End Sub

 

[NdNoviceHlp]

Not sure but you might want to delete this part of the code. The VBA execution of the code just stops ie. you're waiting for the msgbox to disappear. Trial this code to see my point.

[sheet1!a1] = [sheet1!a1] + 1
CreateObject("WScript.Shell").Popup _
"Opening Microsoft Word... Please Wait", 2, "Loading..."
[sheet1!a1] = [sheet1!a1] + 1

Anyways, why do you want to have the active document visible and on your screen? I think you really want to save the .dot as a .doc and close the .dot without saving changes. The newly saved .doc can probably be set to read only if this is what you mean by protection. HTH. Dave