Open Word. modify, send email and close without saving from Excel

[Brian R Armstrong]

I would like some help please with the following - I have try to merge without success

I would like to open a existing Word document, modify - add some text (Name, Date, Reference number) from an linked to an open Excel Worksheet, send as an email and close the Word document without saving - ready to repeat for other Names ……

I have two separate vba codes

One to open document - add Name, Date, Reference number from Excel Workbook.

The second to send the Word document as the body of an email to Named person again from the Excel Workbook.

Can I combine the codes such that I can close without saving the Word document after the email is sent?

Sub QpenDoc() 'adds name to Word.doc
Dim objWord
Dim objDoc

Set objWord = CreateObject("Word.Application")

objWord.Visible = True
 
Set objDoc = objWord.Documents.Open("C:\Users\Main User\Documents\Custom Office Templates\Reward Voucher v2.dotm") 'objWord.Documents.Add

Dim i As Integer
Dim strValue As String

objDoc.Activate 'bring focus to the document created

strValue = "Dear " & Cells(i + 1, 1) 

objWord.Selection.TypeText Text:=strValue 'write the value to the document

objDoc.Close savechanges:=False
Set objWord = Nothing
'Set itm = Nothing
'Set wd = Nothing

Sub SendDocAsMsg()
Dim wd As Word.Application
Dim doc As Word.Document
Dim itm As Outlook.MailItem
Dim ID As String
Dim blnWeOpenedWord As Boolean
On Error Resume Next

Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
blnWeOpenedWord = True
End If
Set doc = wd.Documents.Open _
(Filename:="C:\Users\Main User\Documents\Custom Office Templates\Reward Voucher v2.dotm", ReadOnly:=True)
Set itm = doc.MailEnvelope.Item
With itm
.To = "bra80@tiscali.co.uk" 'bra80@tiscali.co.uk, rozquirk@hotmail.co.uk
.Subject = "Roll Call"
.Send
End With
doc.Close wdDoNotSaveChanges
If blnWeOpenedWord Then
wd.Quit
End If
Set doc = Nothing
Set itm = Nothing
Set wd = Nothing
End Sub

Appreciate any help
Kind Regards Brian A
Windows 10, Office 2016

 

[Macropod]

Is there a reason for not using mailmerge (which you could automate) to do this?

 

[Brian R Armstrong]

Paul thanks for tip - I will research mailmerge to see how it works for me

Regards Brian A