Mail Merge Access to Word VBA (1 Viewer)

[aslure]

I am in a pickle, I have a query (called mailmerge) that has all the fields that match the fields in the word document that I want to mail merge into. The problem here is I need a button programmed in VBA to mailmerge the records from the query into the document. I also need each record merged as its own document and named uniquely by a field in the query. I also then need to convert that word document into a pdf to be sent out.

Is this anyway possible and if so can someone share some sample code on how I can do this?

 

[isladogs]

Do an online search for Albert Kallal's super easy mail merge utility

 

[aslure]

Thanks for this, however I am still having a bit of trouble getting the pieces that I want. He shows a form setup and what to do with multiple record conversion. However he does not talk about file save as unique names according to fields in the query nor how to save it as a PDF once done.

 

[aslure]

This is what I have so far:

Private Sub btnexport_Click()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String

' Set Template Path
wdInputName = CurrentProject.Path & "\PDF Forms\Liability Certificates.docx"

' Create unique save filename with minutes
' and seconds to prevent overwrite
outFileName = "Liability_" & Format(Now(), "yyyymmddmms")

' Output File Path w/outFileName
wdOutputName = CurrentProject.Path & "\Split PDF" & outFileName

Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)

' Start mail merge
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY mailmerge", _
SQLStatement:="SELECT * FROM [mailmerge]"
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With

' Hide Word During Merge
oWord.Visible = False

' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17

' Save file as Word Document
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16

' Quit Word to Save Memory
oWord.Quit savechanges:=False

' Clean up memory
Set oWord = Nothing
Set oWdoc = Nothing

End Sub

 

[Cronk]

However he does not talk about file save as unique names

Does this mean you want a separate document for each record returned by your mailmerge query? If so, you need to do multiple merges, one for each record, in a loop.


BTW

Format(Now(), "yyyymmddmms")

should be

Format(Now(), "yyyymmdd[B][COLOR=Red]hh[/COLOR][/B]s")

and there is probably a back slash missing in

wdOutputName = CurrentProject.Path & "\Split PDF" & outFileName

ie

wdOutputName = CurrentProject.Path & "\Split PDF[U][COLOR=red]\[/COLOR][/U]" & outFileName

 

[aslure]

Does this mean you want a separate document for each record returned by your mailmerge query? If so, you need to do multiple merges, one for each record, in a loop.


BTW

Format(Now(), "yyyymmddmms")

should be

Format(Now(), "yyyymmdd[B][COLOR=Red]hh[/COLOR][/B]s")

and there is probably a back slash missing in

wdOutputName = CurrentProject.Path & "\Split PDF" & outFileName

ie

wdOutputName = CurrentProject.Path & "\Split PDF[U][COLOR=red]\[/COLOR][/U]" & outFileName

Yes I am trying to figure out a loop where I could save each document created by the mailmerge as a separate document with a unique name based on a field from the query.

 

[bastanu]

You need to include the saving as pdf and Word in a loop - see the end of this link for one way of doing it: http://www.vbaexpress.com/forum/showthread.php?45330-Solved-Loop-VBA-for-Mail-Merge-Document

Cheers,
Vlad

 

[Cronk]

Yes I am trying to figure out a loop where I could save each document created by the mailmerge as a separate document with a unique name based on a field from the query.

The loop would be based on each record in your query
set rst = currentdb.openrecordset(SELECT * FROM merge"
rst.movefirst
do while not rst.eof
....
SQLStatement:="SELECT * FROM [mailmerge] where <unique field> =" & rst!UniqueField
...
oWord.ActiveDocument.SaveAs2 wdOutputName & rst!RecipientName & ".pdf"
rst.movenext
loop



loop

 

[bastanu]

You only need to know how many records the query has; you need to navigate inside the mailmerge "template" and save the individual records, therefore the need for manipulating the datasource:

For i=1 to iCount (number of records in the mailmerge query)
   oWord.ActiveDocument.MailMerge.Datasource.FirstRecord=i
   oWord.ActiveDocument.MailMerge.Datasource.LastRecord=i

  'save as pdf
   oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17


   'save as Word
   oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16

Next i

 

[bastanu]

Sorry, forgot to mention that you can use the datafields property of the datasource to get your unique element used to save the file:

Dim sUnique as string
s=oWord.ActiveDocument.MailMerge.DataSource.DataFields ("YourUniqueID")

https://msdn.microsoft.com/en-us/vba/word-vba/articles/mailmergedatafields-object-word

Cheers,
Vlad

 

[Mark_]

Is there a reason to go through Word to produce one report per record saved as a PDF instead of using a loop to export a report on the record saved as a PDF?

Just wondering if you do need word in this rather than simply doing it all in ACCESS.

 

[aslure]

Is there a reason to go through Word to produce one report per record saved as a PDF instead of using a loop to export a report on the record saved as a PDF?

Just wondering if you do need word in this rather than simply doing it all in ACCESS.

I am bit confused by your question. I am mail merging it into word and then converting it into PDF since that is the way it needs to be sent to the client.

 

[isladogs]

Mark was asking whether you could use an Access report saved as a PDF.

 

[Mark_]

@ Colin,

Exactly.

 

[aslure]

@ Colin,

Exactly.

Ohh so I understand what you mean, that is actually a smarter option. I just don't know how great I am in recreating this word doc in an access report. Is there any any information on how someone might do that?