PDA

View Full Version : After mail merge code want save as box for Word doc



mbbx5va2
07-24-2014, 01:07 PM
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. :think:

Thanks

westconn1
07-24-2014, 02:19 PM
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
nextif there could be multiple new documents then some additional criteria may be required, or some part of the default filename for merged documents

mbbx5va2
07-24-2014, 11:39 PM
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
nextif 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
07-25-2014, 03:06 AM
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
07-25-2014, 03:51 AM
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
07-26-2014, 06:39 AM
"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
07-26-2014, 06:56 AM
saveas2

mbbx5va2
07-26-2014, 07:18 AM
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
07-27-2014, 10:41 AM
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 :bow: