|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Start MailMerge for 3 different Word documents in Excel depending on value in column A
Hi guys
I am trying to make a button in my excel-sheet that simply starts a MailMerge in word (for that my colleagues can use my work, too). Depending on the value in row A it has to open 3 different word docs and do the mail merge: - if value in A = 1 then doe the mailmerge for "sb1.docx" - if value in A = 2 then doe the mailmerge for "sb2.docx" - if value in A = 3 then doe the mailmerge for "sb3.docx" Because we need single word docs for every entry in excel, I already use a macro, when i open the word docs and start mail merge manually (sadly my colleagues dont have enough knowhow to do that, thats why I try to create the button in excel): Code:
Sub aaaaSerienbrief() ' set variables Dim iBrief As Integer, sBrief As String Dim AppShell As Object Dim BrowseDir As Variant Dim Path As String ' catch any errors On Error GoTo ErrorHandling ' determine path Set AppShell = CreateObject("Shell.Application") Set BrowseDir = AppShell.BrowseForFolder(0, "Speicherort für Serienbriefe auswählen", 0, 16) _ _ _ _ If BrowseDir = "Desktop" Then Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") Else Path = BrowseDir.items().Item().Path End If If Path = "" Then GoTo ErrorHandling Path = Path & "\Serienbrief-" & Format(Now, "dd.mm.yyyy-hh.mm.ss") & "\" MkDir Path On Error GoTo ErrorHandling ' hide application for better performance MsgBox "Serienbriefe werden exportiert. Dieser Vorganag kann einige Minuten dauern - _ Microsoft Word wird während dieser Zeit ausgeblendet", vbOKOnly + vbInformation Application.Visible = False ' create bulkletter and export as docx With ActiveDocument.MailMerge .DataSource.ActiveRecord = 1 Do .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = .ActiveRecord .LastRecord = .ActiveRecord sBrief = Path & .DataFields("ID").Value & ".docx" End With .Execute Pause:=False If .DataSource.DataFields("ID").Value > "" Then ActiveDocument.SaveAs FileName:=sBrief, FileFormat:=wdFormatdocx End If ActiveDocument.Close False If .DataSource.ActiveRecord < .DataSource.RecordCount Then .DataSource.ActiveRecord = wdNextRecord Else Exit Do End If Loop End With ' error handling ErrorHandling: Application.Visible = True If Err.Number = 76 Then MsgBox "Der ausgewählte Speicherort ist ungültig", vbOKOnly + vbCritical ElseIf Err.Number = 5852 Then MsgBox "Das Dokument ist kein Serienbrief" ElseIf Err.Number = 4198 Then MsgBox "Der ausgewählte Speicherort ist ungültig", vbOKOnly + vbCritical ElseIf Err.Number = 91 Then MsgBox "Exportieren von Serienbriefen abgebrochen", vbOKOnly + vbExclamation ElseIf Err.Number > 0 Then MsgBox "Unbekannter Fehler: " & Err.Number & " - Bitte Makro erneut ausführen.", _ vbOKOnly + vbCritical Else MsgBox "Serienbriefe erfolgreich exportiert", vbOKOnly + vbInformation End If End Sub |
#2
|
|||
|
|||
This should give you a good start. You will need to change the sheet name and add the column A loop counter. Both are trivial. If you need help with that, post back. Try it for one run first.
Code:
Sub Test_MergeRun() 'Const wdocOutput As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Macros\ATT_Looper.txt" Const wdocForm As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Mail Merges\ATT_NE_MM_orig.docx" Const xlData As String = "\\Sasquatch\Common IS$\GP\Transfer Attendance\Mail Merge Creator.xlsm" Const StrSQL As String = "SELECT * FROM 'NonExempt$'" MergeRun wdocForm, xlData, StrSQL End Sub 'If Word found locked fields shows after a merge, see this workaround: 'http://support.microsoft.com/kb/292155 - due to inline text in Autoshape layout. 'DataSources, https://support.office.com/en-us/article/Data-sources-you-can-use-for-a-mail-merge-9de322a6-f0f9-448d-a113-5fab317d9ef4 'MergeRun frmDoc, datFile, "SELECT * FROM `Sheet1$`" 'Requires Tools > References > Microsoft Word 11.0 Object Library Sub MergeRun(frmFile As String, datFile As String, _ SQL As String, _ Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _ Optional iNoCopies As Integer = 1) Dim wdApp As Word.Application Dim myDoc As Word.Document 'Tell user what file is missing and exit. If Dir(frmFile) = "" Then MsgBox "Form file does not exist." & vbLf & frmFile, _ vbCritical, "Exit - Missing Form File" End If If Dir(datFile) = "" Then MsgBox "Data file does not exist." & vbLf & datFile, _ vbCritical, "Exit - Missing Data File" End If If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err.Number <> 0 Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo errorHandler With wdApp On Error GoTo errorHandler wdApp.Application.DisplayAlerts = wdAlertsNone 'Open form file and associate data file Set myDoc = .Documents.Open(frmFile, False, True, False) .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters .ActiveDocument.MailMerge.OpenDataSource Name:=datFile, _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _ AddToRecentFiles:=False, PassWordDocument:="", PasswordTemplate:="", _ WritePassWordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="", SQLStatement:=SQL, SQLStatement1 _ :="", SubType:=wdMergeSubTypeOther 'Merge to a new document With wdApp.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With .Visible = True If bPrint = True Then .Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _ wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _ ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _ False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 End If If bClose = True Then .ActiveDocument.Close False .ActiveDocument.Close False End If wdApp.Application.DisplayAlerts = wdAlertsAll End With errorExit: On Error Resume Next myDoc.Close False Set myDoc = Nothing Set wdApp = Nothing Exit Sub errorHandler: MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description Resume errorExit End Sub |
#3
|
||||
|
||||
You are going about this the wrong way, IMHO. You can have all three letters in the same mailmerge main document and use IF fields to determine which one to generate for a given record. No complicated looping etc. is required. As for generating the individual documents, see:
Send Mailmerge Output to Individual Files; and Run a Mailmerge from Excel, Sending the Output to Individual Files, in the Mailmerge Tips and Tricks 'Sticky' thread at the top of the Word mailmerge forum: https://www.msofficeforums.com/mail-...ps-tricks.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Hello and thank you very much for your replies!
I think I found out how to change the document paths in Kenneth Hobbson's VBA code: Code:
Const wdocForm As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Mail Merges\ATT_NE_MM_orig.docx" Const xlData As String = \\Sasquatch\Common IS$\GP\Transfer Attendance\Mail Merge Creator.xlsm The problem of macropod's answer is that the 3 letters have completely different text and a different amount of sites. All they have in common is the merge fields... |
#5
|
||||
|
||||
Those issues are quite irrelevant. All you need to manage that is three IF fields for the whole document - one for each letter.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
I dont think that can work. Word doc nomber one has 6 pages. Word doc 2 has 8 pages. Word doc 3 has 12 pages and it is completely different, I also added some different text boxes and other stuff to that three documents... How can I ever manage to make one document out of that?
|
#7
|
|||
|
|||
Code:
Sub Test_MergeRun() Dim dat$, sql$, p$, doc$, c As Range, r As Range p = "c:\MyFiles\Word\sb\sb" dat = "c:\MyFiles\Excel\Dat\dat.xls" sql = "SELECT * FROM 'Sheet1$'" Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp)) For Each c In r doc = p & c & ".docx" MergeRun doc, dat, sql Next c End Sub |
#8
|
|||
|
|||
Where do I have to put this loop in teh entire code?
And do I have to make 3 of this loops for every word doc? or is this: p = "c:\MyFiles\Word\sb\sb" numbering itself? Sorry that I am such a noob with VBA |
#9
|
|||
|
|||
You have to change the code to suit your needs. I had to make guesses.
Code:
p = "c:\MyFiles\Word\sb\sb" dat = "c:\MyFiles\Excel\Dat\dat.xls" sql = "SELECT * FROM 'Sheet1$'" Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp)) Dat is the full path to your excel data file. sql gets the data from all of sheet1 in your dat file. r is the range A2 and down from the current sheet with the suffix docx filenames, 1,2,3, etc. The code goes into a Module as does the MergeRun() code. |
#10
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
I didnt know word can handle if functions that refer to a value in excel. Not everyone is so clever and skilled like you. Thats why I am asking my question in this forum
So thanks for your help ---- NOT |
#12
|
||||
|
||||
Quote:
The fields would be constructed along the lines of: {IF{MERGEFIELD LetterType}= "1" "All the content for letter 1 goes here"}{IF{MERGEFIELD LetterType}= "2" "All the content for letter 2 goes here"}{IF{MERGEFIELD LetterType}= "3" "All the content for letter 3 goes here"} or: {IF«LetterType»= "1" "All the content for letter 1 goes here"}{IF«LetterType»= "2" "All the content for letter 2 goes here"}{IF«LetterType»= "3" "All the content for letter 3 goes here"} If the letters have the same opening/closing content, that content can be left outside the fields so there's only one version to maintain. Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required. The only potentially-significant limitation is that the content inside the double quotes cannot include more double quotes, but even that can be worked around via the use of paired single quotes. As for: If you want to be rude, just go away. It is you, after all, who keeps making dumb statements about what you think Word can and cannot do without bothering to accept or try the advice you've been given by those even you admit are more skilled than you.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
How can I see the if-fields after reopening the document?
How can I say the excel macro to not merge entries with 0 in column A? I found out that the if-fields are already there but kind of hidden and can hardly been seen... IS there a possibility to save the different textes in something like auto-text-blocks or so, for that the text is not shown in detail when i press CTRL+F9? Because the text is changing every 14 days and it is really hard to keep an overview if all the texts are together in one. |
#14
|
||||
|
||||
You can see what's in all the fields by pressing Alt-F9 to toggles the field code display on/off.
The field coding I suggested already won't merge any record that has a 0 in column A. Pressing Ctrl-F9 creates a set of field braces; it has nothing to do with viewing fields or auto-text. Moreover, embedding your content in an IF field has no effect on that content's formatting. As for content changing every 14 days, you're going to have that issue whichever approach you take. You might even have a column in your workbook into which you can insert whatever that variable text is, so you can reference it via a mergefield instead of having to edit the mailmerge main document.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
The excel-vba code for mailmerge single documents I copied from your suggested sticky thread is giving me word docs for every entry, also for those that have value 0 in column A (although they are kind of empty, they are still there) and I dont know how to change that
The IF-fields are kind of hidden in the text that should actually follow after the IF... see here for example: https://imgur.com/a/DupAVkf That makes it kind of hard to keep an overview... Sadly I cannot paste the text into excel cells because of formatting and length At least i tried that before and it didnt take all text into word only the first sentences. I tried to save Auto-Text and insert that after the "{IF{MERGEFIELD Anz}= "1"- expressions but it inserts only the text. Would be great of there would just be any fieldcode/field/cross reference or whatever and the full text only be shown when exit the Alt-F9 view by pressing F9. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mailmerge Excel with a Master Word Template (via VBA) | stevenel | Excel Programming | 8 | 08-14-2018 03:05 PM |
Rounding Up or Down depending on Start & End time | richardst | Excel | 4 | 06-21-2016 06:03 PM |
Marking specific cells depending on start and end time entered | ellebb85 | Excel Programming | 0 | 01-17-2016 02:50 AM |
documents saved with double column revert to single column when re-opened | danw | Word | 6 | 04-08-2015 06:24 AM |
Merge Word documents using a mailmerge field | Concertina | Mail Merge | 3 | 02-18-2013 04:12 AM |