Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-07-2020, 02:19 AM
JamesWood JamesWood is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2019
Novice
Doc to PDF mail merge
 
Join Date: Nov 2020
Posts: 10
JamesWood is on a distinguished road
Red face Doc to PDF mail merge


Hi guys


I have a mail merge document with all the letters separated by a section break.


I'm wondering if it would be easy to write a macro that finds a particular word in each letter/section and uses that as a string for the file name (e.g. it searches for the words 'strictly private' and then uses the line beneath it which is the client's name), and then saves each section as a separate PDF file?


Thanks a lot in advance
James
Reply With Quote
  #2  
Old 12-07-2020, 05:18 AM
gmayor's Avatar
gmayor gmayor is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,344
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

See Merge and Split which has an option to do that.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 12-07-2020, 05:21 AM
JamesWood JamesWood is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2019
Novice
Doc to PDF mail merge
 
Join Date: Nov 2020
Posts: 10
JamesWood is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
See Merge and Split which has an option to do that.


Thanks! I'm trying to avoid add-ins as it's for my workplace. I'll post a code I was working on, maybe someone can help adjust it to work.
Reply With Quote
  #4  
Old 12-07-2020, 05:26 AM
JamesWood JamesWood is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2019
Novice
Doc to PDF mail merge
 
Join Date: Nov 2020
Posts: 10
JamesWood is on a distinguished road
Default

So here's a macro I was working on:


So it starts by asking what folder I want to save the PDFs to, and then it copies each section to a new document, uses the line UNDER 'strictly private' as a string for the filename + today's date, and then saves.


However I don't want it to copy each letter new document, but I'm not sure how to achieve this.




Sub PDFBySection()
'Select folder'
Dim sFolder As String
' Open the select folder prompt
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "U:"
.Title = "Select a folder to save to"
If .Show = -1 Then ' if OK is pressed
sFolder = .SelectedItems(1)
End If
End With

If sFolder <> "" Then ' if a file was chosen
Selection.HomeKey Unit:=wdStory

Application.Browser.Target = wdBrowseSection

'Copy letter to new document'
For i = 1 To ((ActiveDocument.Sections.Count) - 1)
ActiveDocument.Bookmarks("\Section").Range.Copy
Documents.Add
Selection.Paste

'Removes the break that is copied at the end of the section, if any.
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1

'Grabs client name'
Selection.HomeKey Unit:=wdStory

With Selection.Find
.Forward = True
.ClearFormatting
.MatchWholeWord = True
.MatchCase = False
.Wrap = wdFindContinue
.Execute FindText:="Strictly Private"
End With

Selection.MoveDown Unit:=wdLine, Count:=1
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Copy

'Get current date for filename'
Dim xDate As String
On Error Resume Next
xDate = Format((Year(Now() + 1) Mod 100), "20##") & _
Format((Month(Now() + 1) Mod 100), "0#") & _
Format((Day(Now()) Mod 100), "0#")

'Create a string for file name from line 6 where client's name is'
Dim strTemp As String
Dim MyData As DataObject
Set MyData = New DataObject
MyData.GetFromClipboard

'String to put date first then client name second for file name'
strTemp = xDate & " " & MyData.GetText(1)

'Save as PDF'
ActiveDocument.SaveAs FileName:=(sFolder) & "" & (strTemp) & ".PDF", FileFormat:=wdFormatPDF
ActiveDocument.Close savechanges:=wdDoNotSaveChanges

'Move the selection to the next section in the document
Application.Browser.Next
Next i
End If
End Sub
Reply With Quote
  #5  
Old 12-07-2020, 05:59 AM
gmayor's Avatar
gmayor gmayor is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,344
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

Quote:
Originally Posted by JamesWood View Post
Thanks! I'm trying to avoid add-ins as it's for my workplace.
My add-ins are in use by government, military and industry around the world. Some users even donate for using them. I am surprised that your company will let you use VBA to screw up their systems and not allow the use of add-ins.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 12-07-2020, 07:22 AM
JamesWood JamesWood is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2019
Novice
Doc to PDF mail merge
 
Join Date: Nov 2020
Posts: 10
JamesWood is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
My add-ins are in use by government, military and industry around the world. Some users even donate for using them. I am surprised that your company will let you use VBA to screw up their systems and not allow the use of add-ins.


That doesn't really help, but yeah, they're only simple macros for basic tasks, nothing that can actually screw anything up
Reply With Quote
  #7  
Old 12-07-2020, 11:13 AM
Charles Kenyon Charles Kenyon is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,103
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

Quote:
That doesn't really help, but yeah, they're only simple macros for basic tasks, nothing that can actually screw anything up
I think your decision makers misunderstand the power of "simple macros." Simple macros can royally screw up anything! The code in the typical Add-In is no more complex than what you've posted. In the case of Add-Ins on Graham's site, it is better written code from a professional who really understands Word. His add-ins have been used by thousands.
Reply With Quote
  #8  
Old 12-07-2020, 10:52 PM
macropod's Avatar
macropod macropod is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,726
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum:
https://www.msofficeforums.com/mail-...ps-tricks.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 12-08-2020, 01:18 AM
JamesWood JamesWood is offline Doc to PDF mail merge Windows 10 Doc to PDF mail merge Office 2019
Novice
Doc to PDF mail merge
 
Join Date: Nov 2020
Posts: 10
JamesWood is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum:
https://www.msofficeforums.com/mail-...ps-tricks.html
Thanks for this!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Doc to PDF mail merge Mail Merge with Fields extracted from ROWS instead of COLUMNS or transpose of typical mail merge djrexy Mail Merge 1 05-20-2020 04:04 PM
Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA RMerckling Mail Merge 16 05-17-2018 05:19 PM
Doc to PDF mail merge How do I mail merge records into table columns with more than one merge field? tech123 Mail Merge 1 04-26-2017 07:13 PM
Doc to PDF mail merge Mail Merge is Deleting objects in my header and footer during the merge bgranzow Mail Merge 9 06-05-2015 05:03 AM
Mail merge how to link mail merge field value to a column heading dsummers Mail Merge 1 05-08-2014 02:59 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft