today we're going to convert your mail
merge into separate pdf
documents now if you started down this
rabbit hole you'll know that mail merge
produces one massive document
and whether you convert to pdf first or
later you've somehow got to split this
up
and the processes for doing this at
least those on google are
either really complicated or require a
third-party plug-in
so we're going to avoid either of those
today but that's not the only problem
if you somehow managed to get through
the process and create separate pdf
documents
you'll find that they're called
something completely useless like
document 1 document 2
document 752 and you're going to spend
the next two or three hours
renaming all these files before you can
send them to the recipient
or file them away it's a colossal waste
of time and that's the second thing
we're going to avoid today
so how we're going to do this we're
going to give you a word macro that you
can
copy and paste into your word system to
automate the process of saving out
your mail merge into individual
documents and exporting as pds
better than that the macro is set up to
read
a folder path and a file name from the
mail merge data
itself to specify the name of the file
and where you want to save this
i cannot stress how powerful this is
when you set up your word mail merge
data
you'll typically do it as a table in
excel and that means you've got all the
excel formulas
available to you to define the name and
the save to location
so if you want to have a name or a date
or a reference number in there
you can if you want to have some kind of
group identifier you can
the choice is entirely yours and more
importantly you're not limited just the
file name you can do it with the folder
path as well
so if you've got say one folder for
every recipient
you can save the pdf directly into that
folder
you don't have to later on filing it
saving you again a colossal amount of
time
so how we're going to do this well the
first thing we need to do is to get the
macro into your word system
we provide the macro on our website in
plain text format
so you need to just copy it from there
and paste it into word but don't worry
we will go through all of this
in this video so you can see every
single step second step we'll set up the
data
in excel and the word mail merge in word
and then run it so you can see how it
works
and then finally as a bonus we will go
through every single line of the code
line by line showing you exactly what it
does in word how it automates word
and with that in mind we've written the
code not in a purist fashion
but rather to be as understandable as
readable as possible
to the layman and finally to answer the
last question you probably have
what's the point in creating a pdf if i
can't then send it to people we've got
another video coming out
where we'll use the word mail merge
email feature
and a macro to send emails with
attachments
and actually in fact custom subject
matters and a cc box as well
neither of which are available in the
standard version of mail merge
to email but that's another video we'll
deal with that later
so now let's get to it so here we are in
windows explorer where we have a folder
containing
our data as an excel spreadsheet and our
letter
as a word document now for anyone using
some kind of
cloud or sharing service such as
sharepoint or dropbox
you'll need to make sure that your
service is synced with your computer so
that you have a local folder which
appears
the process of word saving a pdf does
not allow direct
saving to for example sharepoint so for
us here we have the demo sharepoint site
and then we've just got the folder
called end of year
inside that so this will be saving
effectively directly to sharepoint but
doing so virasync folder
so without the way let's actually get on
with the job
so first things first let's open up word
and we will add the macro in
now if you're joining us here the macro
is on our website in the article linked
to this video so if you're watching the
video on the website it's below
and if you are watching this on say
youtube please look in the description
below
where you'll find a link to the article
on the website where the text
of the macro is so let's open up word
and here is our letter so first things
first as i promised we'll do the macro
so the way you can add a macro to your
system is using this developer tab up
here
now if you don't have this appearing
that's perfectly normal for word they
hide it by default
and the way you make it appear is you
just click in the ribbon anywhere and
you go to customize ribbon here
and you click the checkbox next to
developer in this right hand column
once done press ok and the developer tab
will appear
and this brings you the visual basic and
macro button on the left hand side here
so to bring up visual basic where we're
going to add our macro in
we click the visual basic button and
this brings up the visual basic
editor if your visual basic editor
doesn't look exactly like this don't
worry
all we need is this project panel to
appear and if yours doesn't you can find
it by clicking view here
and project explorer or pressing control
r here so once done you should have this
panel
appearing if not on the left and at
least somewhere on your screen
the project explorer looks a lot like
windows explorer you've got folders and
they contain files and the files contain
text which define your
macros and all we're going to do is
create a new little text file and
paste our macro into it and that makes
it available
now you can see here we've got two top
level entities here we've got one called
normal and one called
project brackets letter now this normal
one is so-called normal template and
this is a default template in word
and it's used to store all your defaults
you can define styles in it but you can
also define macros in it
and normally is opened every time you
open word so that's really the best
place to save any macro that you want to
be able to access
repeatedly project letter refers to the
letter template which we've just opened
and while we could save our macro in
there it will only therefore be
available to that one letter
and once you've closed it it won't be
available anywhere else so
i would recommend that we insert it into
normal and the way we insert it is we
simply right click
go to insert and module
and having done that we find here that
this modules folder will be created if
it hasn't been created already
and we've got module 1 appearing and all
we need to do
is to go to the website select the text
of
the macro copy it and paste it in and
i'm just going to right click i've
already copied it
and paste and there we go so the text is
now in there
i will go through the text in detail
later on but we need to know is
this sub and this n sub that defines our
macro
that defines the macro name and this
green text down the side tells you what
each line is doing
we'll run through this in detail in the
video later on but right now
that's what we've got and having copied
and pasted our macro in
we can close this down so our system is
ready to go with that macro in fact the
way we run the macro later on is we
click macros
and there it is it's ready to go but
we're not ready to run it yet because we
haven't set up our data
so quickly let's just look at our
templates this is a very simple letter
template
i'm sure you'll want to do something far
more detailed for your own work
but for us all we've got is we've got a
full name field here
we've got a known as field here for the
salutation and finally
we're going to give them an end of year
complement because we're nice like that
and so everybody's got an individualized
compliment which goes in this one
i've already set this up as a mail merge
so the recipients are already selected
and the fields have already been added
so this is done
and we are ready to look at the data so
let's now open up
the excel spreadsheet the first three
columns on the left
are as you might expect we've got one
called full name one called known as and
one called complement
and these correspond to the three fields
that you saw in the mail merge
in addition to this we have created four
additional columns
doc folder path doc file name pdf folder
path
and pdf file name now the names of these
columns are
important and need to be exactly as you
see here the spaces have been removed
and the first letter of each word has
been capitalized
this enables the macro to know where to
save and with what name each of the doc
and pdf files so what we need to do is
to put in
the relevant data so first of all doc
folder path this is where we want to
save all of the word
documents now the process requires you
to save a word document before
converting to pdf
so because we have to save it we might
as well save it somewhere useful and
even if you just delete it all
afterwards
it doesn't really matter and the way we
go about finding the doc folder path
is we look at windows explorer and we
simply need to
find the folder we want to save the
documents into
like here i've got this ready-made one
called docs and we click in the address
bar here
make sure it's all selected and we copy
the text
once done we can simply click on the
cell
and paste the data and there it is that
is the folder path
already set up for the documents
now in the beginning of the video i did
promise that we could have much more
complicated folder paths
and indeed you could do this but we're
not going to do that here
however if you wanted to you could
always use a formula and for example
have a subfolder of docs
whatever you do please be advised that
you do need the folders to already exist
in the system before you run this macro
the macro won't create the folders
for you so we'll keep it simple we're
going to save all the word documents
into docs so i'm just going to double
click on the corner here and that fills
it all the way down
into each of the five rows so the doc
file name we need to make sure each of
them are unique
because they're all going into the same
folder and to do this
i am going to simply use the full name
and some kind of description of the
content
so i'm going to make a formula open
equals a quote
and we'll just call this end of year
letter
to and then we'll add in using the
ampsand
to combine text the name so there it is
we have a formula and i'm just going to
double click here and paste it down
and so here we have each file will be
called end of year letter two and it's
each of our names now you'll notice i
haven't included
the extension the docx extension that
will be automatically added
by the macro if you do
inadvertently add it you'll just find
that your file is called docx docx
it will still work so next up the pdf
folder path
coming back to windows explorer we've
already created a pdfs folder as well
again you could go much more complex but
we'll keep ours simple and save
all the pdfs into one folder so i've
copied the text
and i'll paste it in there and once
again double click
to fill down and there we go we've set
up our pdf folder path
so finally the pdf file name and we
could
we could make it exactly the same as the
word document file name
that is not a problem so i've copied the
formula there and pasted it down so this
is the same as the doc file name
so that is our data setup we know now
from this data that we'll save
all the word documents into the folder
called docs
we will call them end of year letter
plus the name and the pds will go into a
folder called pdfs
and each of the letters will have it
their own name so this is all set up
we are ready to go okay so here we are
back in windows explorer i'm going to
reopen the letter
and we will put this onto the left hand
side so that we can see
the letter and we'll zoom out quickly so
we can see the whole letter so here's
our letter
and on the right hand side what we're
going to open up is the two
folders this is the docs folder and this
is the pdf folder so we can see the
files
as they're created within these two
folders
so to run it we click in the developer
tab here press macros
and here's our macro ready to go so
press run
and so as this runs you can see here
that in each of the
folders the word documents are created
followed by the pdfs
and so there we go the process has been
completed and we have
five docx letters and five pdfs so let's
just check one of them we'll check
bethany's
open this up and here we are bethany
klein dear beth
and beth is indeed superb so
our process has worked we've got named
pdf they have the contents
of the mail merge we have
succeeded so that's it please go off
have a play use the code
and finally it's time for the running
through of the vba macro
in its entirety so here we are back in
vba where we're going to run through the
macro and see
how it performs the mail merge so on the
right hand side we have all this green
text
these are called comments and they're
defined by this
single quote and everything after the
single quote is green
and green text is ignored by the
computer so we can
write whatever we want there and
typically you use these
to give a description in real world
terms of what the line does
now i'm going to tell you what these
lines do which will roughly correspond
to this green text over here
so the first line here sub mail merged
pdf this defines a subroutine which is
the same as a macro and gives a name
mail merged pdf
that's all this line does the brackets
you can ignore
the first line of our subroutine here
dim master dock record number and single
dock what this does this sets up what
are called variables and
the best thing to do is think of the
variables as post-it notes so what we've
done is we've collected three post-it
notes together
we've put them on our desk and we've
scribbled onto them a name
master doc record number and single
document and that's really all we need
to think about
at this time so the next line here
is we set master doc remember this is
just a post-it note
to the active document and if we bring
back word
when we click go on our macro we have a
document that's front and center
this is the active document and this
being our mail merge document is the one
we want to identify so we identify this
as
master document and now we know which
one this is
every single time we want to refer to it
we don't have to worry about other
documents getting in the way or anything
getting confused here
so once that's done what we're going to
do is we're going to run what's called a
for loop and that starts with a
4 here and runs through to this next
line here
and what this does is this runs the code
in the middle from here
to here a fixed number of times
each time it runs it this value here
record number
another post-it note will have a
different value
so conceptually you might say this is a
bit like putting a
number one you want a bit of paper
handing it to an assistant saying going
off and getting that file or doing
something with it
they come back you put a different one
on and you send them off again and you
do it each and every time
so this loop will run from record number
one
through to this thing here which don't
worry about it just yet but this defines
the total number of records and to give
you a feeling of how this would work in
the mail merge if i preview the results
here so we can actually see the data
this is a bit like going to one and then
we'll do something
and then we'll click onto two do
something click onto three and in fact
we'll show you all these steps as we do
them
so let's go back to our code this bit
as i said previously this is the total
number of records
within your mail merge and here we're
just going to have a quick glimpse of
how a macro can identify different
things
as it runs so we've already identified
that the master document is the
well active document the document with
the mail merge template in it
and that contains the mail merge data as
well
so what this does this is like an
address so we start with the top level
thing
the master document we say i want to
drill down into that
and give me the mail merge that's
associated with that
document inside that mail merge we drill
down again we identify the data source
that is the excel spreadsheet we
provided it and we go into that and we
say give me
the record count i.e the number of rows
in that excel spreadsheet
so this will return in our case the
number five we only had five records set
up for it
but in your case it could be any number
of records
so all this does is this defines total
number of records
in our mail merge and puts an end
condition for this
for loop so it only runs up to that
total number again coming back to word
this is a little bit like clicking this
skip to the end last record button and
there it is the last record's five
so that would be the record count five
for the next steps i'm going to close
down the
project explorer over here and i'm going
to push this to the left hand side so we
can see
the mailbridge data on the right and we
have here our
code on the left and the reason i'm
doing this is to show you exactly how
this works
with the mail merge so the first line
master doc
dot mail merge dot data source dot
active record equals record number now
remember record number is set to 1 for
the first iteration
loop then two then three then four then
five and it ends
and this is the same as going to
mailings and going
under preview results to here and just
simply entering
the correct record number so in our case
we start with one
so i put in a one press enter and there
we go we've gone now to the first record
adam griffith so that's what this does
in the background
this is really important because later
on every time we refer to the data of
the mail merge to find out the save
locations and the file names
we want to make sure that we're pulling
it from the right row so this says
we are looking at row one or two or
three depending on what record number is
so next up masterdoc.malmo.destination
equals wd sentinel document this is the
same as clicking
finish and merge and going here to edit
individual documents
so we click on that and we get this up
so that's what this line does
this is done in the background so you
wouldn't actually ever see this
this window here appear but in the
background that's effectively what it's
doing
so next up we set the master the my
stock mail merge data source
first record to record number and the
last record is the record number and
that's the same as going here
from and two and putting
a one in both boxes because we start at
one
finally master dot dot mail motion to
execute false
and that's the same as clicking ok so
literally
all that code has done is exactly what
you saw me do there
and it has done this mail merge for the
first record
and here it is only one letter it's
one page long this is quite simply the
first document that's been extracted
and has been created as a new document
so the thing we now need to do is to
slap a post-it note onto that new
document so we know
what it is so we set the single doc to
this
and it's while it says active document
and yes active document was used up here
as well
this is now the active document being
the front document
and this one the letter document is no
longer the active document it will still
have the master doc
post-it note on it but here at the front
active document is this one and this now
has the single doc
post-it note on it so having identified
this new active document which is the
result of our mail merge just one record
remember
we now need to save it and we need to
export it as pdf
and this first set of four lines here
this is in fact one line
of code but it's been split over four
lines
this saves document as a word document
and that's the same as clicking here
going to save as choosing a folder
and naming your file and saving it next
up we have another four lines here
and this is the same as going up to file
and going to save as adobe pdf
again choosing a folder giving it a file
name and saving it
so finally having done all that we can
close the document now i haven't saved
this one
but if you had you would uh not get that
window to
ask you to save it because it already
saved and
that's what this line does here does it
closes the document
and now we're ready to do the next
result
so this line here next record number
this tells us
we're going to increment record number
by one and then we're going to go up
here
to this four and repeat the process
again so that would be
equivalent of going here putting in a
two
going here add individual documents set
that to two set that to two
open it up save it close it so that's
the process that we've automated
and once it's done it for all of our
records in our data sets we get to end
sub
and that has finished the macro so
before we go let's have a quick look at
the detail of the save as step and the
export as pdf step so while these are
over
four lines they are in fact one single
line this little underscore here says
go on to a new line but don't start a
new code line
so this is one single command and at the
top
we tell it we're saving and then it's
just got two options here
which have the the name of the option
here file name and then file format
followed by colon and an equals and to
the file name
is not just the file name but is
actually the file path
in its entirety so that's the folder
path followed by slash followed by the
file name
and the way we create the file name is
we take this thing
which is the folder path and this is
identified by going to the master dot
mail merge data source
then looking at a item called data
fields
for the one that's named doc folderpath
and returning its value
so this will return the value of the doc
folder path
for the row that is the active record so
the row we're exporting
this will return the doc folder path
value we then
easy amazon is just like in excel it
adds two bits of strings together
so we add in the backslash and then we
do the same
for the uh doc file name and so this is
retrieved
and finally we add on the docx at the
end so this builds up one single string
which is the folder path and file name
combined and this is provided as the
file name so we know where to save the
document
finally we have this file format here
which is simply saying
save as a docx document that's the new
version rather than the 97 document
of the last century uh so the exporters
fix file format is very very similar
uh exporter fix file format is a generic
name for the pdf export
and we have an output file name rather
than just simply file name but it is
assembled in pretty much the same way we
have the pdf folder path
backslash and then the the name of the
file followed by pdf
and finally we tell it we want to export
as pdf as opposed to another format
so these lines although they spread over
four lines and look very complicated
they're relatively simple and are built
up of these monstrosities here
which as long as they are are simply
return the field
from the current row which has the name
given in these quotes here and in the
brackets
so that's it that covers the entire
macro
all i've said here is presented to you
in this green text on the right so
please feel free to read that
but that is it we have done our mail
merge
to pdf the results are here we are ready
to go
and send these out via email
so thanks for watching i hope you found
this useful as mentioned before we've
got another video where we use
word mail merge and a macro to create a
better
email system where we can actually send
attachments to people so please do check
that out
on top of that please obviously do check
out our website we've got a lot of tips
and tricks
and everything on there which will help
you systematize your work and
reclaim your life so thanks for watching
see you next time