After mail merge code want save as box for Word doc

[mbbx5va2]

So I have a working mail merge procedure but it produces PDFs. Now I'm trying to save the mail merged document as a word document. The beginning of the procedure is as follows:

Dim xls As Excel.Application
Set xls = New Excel.Application
Dim wrdApp As Word.Application
Set wrdApp = New Word.Application
Dim sPathFileTemplate As String
sPathFileTemplate = ThisWorkbook.Path & "\OFF template macro.docx" 

'Setting up template 
Dim doc As Word.Document
Set doc = wrdApp.Documents.Add(sPathFileTemplate)
wrdApp.Visible = False ' Make MS Word Invisible
Dim sIn As String
sIn = ThisWorkbook.FullName

After this in VBA I've written the code for the SQL, Opened the merge and then done the merge. Now all I want to do is save a copy in word with the save as box appearing so that I can insert a file name. I've had a go and come up with the following:

wrdApp.Visible = True 

doc.Save 

wrdApp.Documents.Add doc.FullName 

doc.SaveAs "C:\Documents\" & "test1.docx"
doc.Close False
Set doc = Nothing
wrdApp.Quit False
Set wrdApp = Nothing
MsgBox "Done"
End Sub

Alternatively I am thinking some variation of:

SaveAsName = Application.DefaultFilePath & "\" & "docx"
With doc
.Activedocument.SaveAs Filename:=SaveAsName
End With

would work.

Any ideas with this would be much appreciated.

Thanks

[westconn1]

from memory the merged document is a new document with a default file name, no extention, so assuming only 1 new document, you can try like

for each d in wrdapp.documents
   if instr(d.name,".doc") = 0 then d.saveAs saveasname: exit for
next

if there could be multiple new documents then some additional criteria may be required, or some part of the default filename for merged documents

[mbbx5va2]

from memory the merged document is a new document with a default file name, no extention, so assuming only 1 new document, you can try like

for each d in wrdapp.documents
   if instr(d.name,".doc") = 0 then d.saveAs saveasname: exit for
next

if there could be multiple new documents then some additional criteria may be required, or some part of the default filename for merged documents

Hi thanks for the response. The data source in excel contains 11 rows of data with the first row being the field names. Each row corresponds to a letter. So once the mail merge is done it produces 10 one page letters in one document for the PDF version.

I will have a go at using the code sometime today and tomorrow.

[westconn1]

as they are individual 1 page documents, you probably have several to save, so remove the exit for, and update the saveAs filename for each


or convert to a single multipage document, in the mailmerge object

[snb]

Sub M_snb()
   with getobject("G:\OF\maindocument.docx")
     .mailmerge execute
     with activedocument
         .exportasfixedformat "G:\OF\merged_document.pdf"
         .saveas2 "G:\OF\merged_document.docx"
         .close 0
     end with
  end with
End Sub

[mbbx5va2]

"as they are individual 1 page documents, you probably have several to save, so remove the exit for, and update the saveAs filename for each


or convert to a single multipage document, in the mailmerge object"



I've had a go inserting your initial code and I get run time error 13: Type mismatch. It then highlights

 d.saveAs saveasname

On help it mentions that the variable or property isn't the correct type. However when I put

Dim As Object

it still returns the same error. I have tried removing the exit for and still get the same error. Also I've tried including and removing the final 4 lines of my initial code but again the same error. I am missing something. I'll continue working on it and see where I get.

When I try the code from my first attempt it seems to open up 3 word documents and saves another to a folder. One of the 3 documents is the one I need i.e it has all the letters that I need in one document but the code needs work.

[snb]

saveas2

[mbbx5va2]

saveas2

Hi Sorry I was meant to quote westconn1 for my previous message. I'm just trying to get yours to work right now.

[mbbx5va2]

Sub M_snb()
   with getobject("G:\OF\maindocument.docx")
     .mailmerge execute
     with activedocument
         .exportasfixedformat "G:\OF\merged_document.pdf"
         .saveas2 "G:\OF\merged_document.docx"
         .close 0
     end with
  end with
End Sub

Hi

I had to change a couple of things but it works.

Many thanks