Macro button to mail merge info (1 Viewer)

[Abigail Florence]

Hi

I have a form that I would like to have the option to mail merge the information to word document. I want a macro button to dot his. Could you tell me the correct way to go about this please?

Many thanks

Abigail

 

[moke123]

Try albert Kallal's super easy word merge- http://www.kallal.ca/msaccess/msaccess.html

 

[mike60smart]

Hi Abigail
Can you upload your database?

 

[Pat Hartman]

I really dislike trying to make mail merge work. Here is a sample that uses OLE. It is a sample so it is hard-coded and this particular technique, although it shows the details of how to use OLE, won't be useful if you have more than a few document types to fill. I'm working on a more sophisticated sample but it could be a while before it is ready.

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk

 

[Gasman]

I have just created some code, based on code I found on the net, and updated by an expert on vbaexpress forum.
Mine is running fron Excel vba and only handles, what I wanted it to do, but it could be parameterised?
Let me know if you want me to upload it.

 

[moke123]

here's an example https://www.access-programmers.co.u...g-a-word-doc-into-access.325276/#post-1849909

 

[Abigail Florence]

I have just created some code, based on code I found on the net, and updated by an expert on vbaexpress forum.
Mine is running fron Excel vba and only handles, what I wanted it to do, but it could be parameterised?
Let me know if you want me to upload it.

Hi thanks for your response, Yes please!

 

[Abigail Florence]

I really dislike trying to make mail merge work. Here is a sample that uses OLE. It is a sample so it is hard-coded and this particular technique, although it shows the details of how to use OLE, won't be useful if you have more than a few document types to fill. I'm working on a more sophisticated sample but it could be a while before it is ready.

Thanks for your response. I am trying to create a labels from the tables in access and wanted a macro to do this. What would you say is the best way to go about this?

 

[Gasman]

Ok, code is below. Some background for this code.

I created a workbook as an address book for the local community car scheme. The controller for that scheme is not that computer literate, so trying to make it easy as possible for him.
The objective is to produce A4 pages with 8 label addresses per page, which will then get folder to be placed in to A5 poly pockets.

So the code takes the activesheet for it's data. So effectively the code changes the merge document source.

The mailmerge document was made as normal. That is the strMMDOC. The code ends up printing the merge to a suitably named pdf file, that can be viewed and printed as needed.
The end result is a document called Labels1 which is never saved.

DO NOT alter those quotes in the Select clause. I thought they were the quotes that you see on websites when code is displayed, and VBA complains about them, and you need to change to single quote character '. These are not those characters, despite looking the same.

If you do change them, then you get a prompt for a table for a workbook not even mentioned, and that is confusing as hell.

HTH

Sub RunMerge()
' Sourced from: http://www.vbaexpress.com/forum/showthread.php?70461-Change-Word-mailmerge-source-with-VBA
' Note: this code requires a reference to the Word object model to be set, via Tools|References in the VBE.
On Error GoTo Err_Handler

Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String, strPDFName As String
Dim iLastRow As Integer

StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & "\"
StrMMDoc = "F:\Users\Paul\Documents\Test Address Details 7165 MM.docx"
StrName = ActiveSheet.Name 'InputBox("Please input the name of the source worksheet")

If Trim(StrName) = "" Then Exit Sub
'Trim Filter column else we get extra records with no values
iLastRow = GetLastRow(StrName, "A") + 1
ActiveSheet.Range("A" & iLastRow & ":J1000").ClearContents

Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = True
wdApp.WordBasic.DisableAutoMacros
wdApp.DisplayAlerts = wdAlertsNone
'StrMMDoc = StrMMPath & "MailMergeMainDocument.doc"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdMailingLabels
    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `" & StrName & "$`"
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
  End With
'  .Close SaveChanges:=False
'Save as PDF file
strPDFName = "GCCS Passengers - " & StrName
With wdApp.ActiveDocument
    .SaveAs Filename:=StrMMPath & strPDFName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    '.Close SaveChanges:=False
End With

'wdApp.Documents("Labels1").ExportAsFixedFormat OutputFileName:= _
'    StrMMPath & strPDFName & ".pdf", _
'    ExportFormat:=wdExportFormatPDF, _
'    OpenAfterExport:=True, _
'    OptimizeFor:=wdExportOptimizeForPrint, _
'    Range:=wdExportAllDocument, _
'    IncludeDocProps:=True, _
'    CreateBookmarks:=wdExportCreateWordBookmarks, _
'    BitmapMissingFonts:=True

End With

wdApp.DisplayAlerts = wdAlertsAll
'MsgBox "Mailmerge document created. Switching to Word application, document Labels1"
wdApp.Activate

Err_Resume:
Set wdDoc = Nothing
Set wdApp = Nothing
Application.ScreenUpdating = True

Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume Err_Resume
End Sub