Transcript for:
Enhanced Mail Merge Email Techniques

today we're going to use word mail merge to send emails but we're going to add in all the features that are missing from the standard version features such as the ability to add attachments have a custom subject and save the message in drafts so you can check it before it gets sent now this video followed on from a previous video where we used word mail merge to create a series of pdf documents which were then in this video going to send out to the recipient as an attachment however when we started looking into how we could do this we realized that really the best thing to do is to properly enhance the mail merge to email system so that you can add in all the features that are missing so what features have we added my favorites the ability to save the email in draft if you've got an important email the last thing you want to do is to close your eyes press the go button and have 50 or 100 emails sent out which may have an error in it so what we can do with this system is you click go you have 50 drafts you can then check each of them and if you really want to you can send each individually having checked it or you can spot check a few delete all the drafts and then hit go knowing that those drafts will be sent out automatically next up we have a custom subject there's nothing worse for recipient engagement than giving them an email with a generic subject here they might even just ignore it so what we can do here is we can add in some kind of personal information to really make that email subject pop out at them so they read the email and are happy to receive it next up we have attachments the ability to add one or more custom documents to each email we have the ability to choose the account it's sent from and to user send as or send on behalf of features of the exchange system if you didn't know this you can be set up by your admins so that you can send an email on behalf of either somebody else or on behalf of a team to which you're a member of in the first case this enables an assistant for example to send emails out on behalf of the ceo and in the second case it enables a team member to send out an email as the team rather than as an individual to give a consistent appearance to the recipients finally we've added in some very standard features a cc a bcc delay delivery read receipts delivery receipts importance and sensitivity so how do we go about doing this firstly we're going to give you a macro which you can copy from our website and paste into word to make that available to you we'll take you through all the process of doing that in this video so don't worry if you're not familiar with adding macros or anything like that the macro reads the data for the email from the mail merge data itself so having added the macro to word will then look at the data in excel and add in all the fields that you can use to send your emails having done that and set up our example we'll run it and show you how it all works and then finally right at the end we will go through the macro line by line so you know exactly what it's doing each and every step of the way now the process of creating the pds we were able to do a very simple macro we deliberately simplified it and went through it at a level for people who are not necessarily familiar with vba or macros unfortunately this macro is a lot more complicated so we'll be going through it at a higher level assuming that people have an understanding of vba nevertheless we will go through every line so you know what it's doing so that about covers it let's get to it so here we are in windows explorer where we have a folder containing the email template as a word document data in an excel spreadsheet and a folder called pdfs which contain the attachments we're going to apply to each of the emails now all of this is on sharepoint in a cycle demo mail merge and vba don't play that well with sharepoint so we have synced it so that it appears as if it's local to our computer so firstly let's look at the template in word and add our macro so here is our letter template we have already set up the mail merge in mailings we've selected the recipients and linked it to the uh spreadsheet we simply have a deal known as that's the only mail merge field in the email itself and then we've got please find attached your letter now of course you can make the body of the email as complex you want with as many mail merge fields as you desire the only other thing to note about this template is that we have copied and pasted the signature from my email into this now it is possible to have the system add in the default signature however given we give the ability to change accounts and indeed send an email as someone else it turns out to be easier to copy and paste the correct signature into the body of the email explicitly and therefore you know exactly what you're adding to your email so this is the template we don't need to look at it much more let's go about adding our macro so the way we add a macro is via the developer tab up here now for some of you it won't be available it is hidden by default and all you need to do is to right click in the ribbon and go to customize ribbon and on the right hand side here you'll be able to just check the box next to developer and this will make the developer tab appear and the buttons we're interested in are here visual basic and here macros so visual basic brings up the visual basic editor and that allows us to add our macro and macros enables us to run it once added so we'll click the visual basic button and this brings up our visual basic editor now don't worry if your editor doesn't look quite like this there's not much we need to interact with here the only thing we really need is this project explorer on the left here and if you can't see this go to view project explorer and click and this will bring it up so next question is how we add our macro in word you have what's called the normal template and this is a word template that's opened every time you open word and this can be used to store macros which are then available every time you open word so this is the best place to store any macro that you're going to want to use on multiple documents i have a load of macros stored in my normal template and i use them every time i open word they're linked to buttons and everything else so how we're going to add this specific macro all we need to do is to right click on normal here and go to insert module and you can see here i've got this module one here we can ignore this one this was one i added earlier here's module one it's been added it's a blank module this is just like a text file and all we need to do is copy and paste the macro into this window and i've already copied it so i'm going to right click and go paste and there is our macro now we're not quite there yet we need to add in what are called libraries and this note here references required is what that's all about so because we're sending emails we need to enable this macro to talk to outlook so that's why the outlook library is there in addition to generate the text of the email we actually have to use the mail merge to save an html document we then read that document into the email and to read it we require this microsoft scripting runtime so let's add these references we need to do is go up here to tools and go to references and then we scroll down somewhere into the middle where we'll find microsoft outlook object library and if i scroll down we should find the scripting runtime here and having checked the boxes next to them we click ok and that's all we need to do to enable these libraries to be used by this macro so our macro is now set up and ready to go and so we can close down the vba editor and so here we are back in the explorer where we'll open up the data so here we are in excel where we have our data in an excel spreadsheet for any mail merge we know that we need to have the field headings in the top row and the data underneath it we've just added a little bit of blue formatting to make it easy to read now the first column here full name this will be used later so let's ignore it for now second field known as this is the only field that appeared in the body of the email as part of the mail merge proper and once that's in there we can now ignore it the remaining 12 fields are all used by the macro to generate the email and it's important that we get the headers of these fields correct otherwise they won't be recognized by the system so if you've got a typo in one of the headers it will simply be ignored and you won't get the results you want so the headers are 2 cc bcc subject importance sensitivity read receipt which is all one word delivery receipt and delivery time both also one word account send as one word and attachment these headers are case insensitive so you don't have to worry about capitalization but please note the lack of spaces between words now before we run through all of the fields please note that you can ignore any field that you don't want to use so if you don't want to have the read receipt or delivery receipt you can simply remove the fields there you go they're gone i don't have to worry about them anymore any field that's not present will simply be ignored and nuclear if you leave a field blank so example importance here but leave the column in there it will equally be ignored and when i say it's ignored for something like importance you'll just use the default that's set for your outlook so importance default is typically normal and if yours has been overridden you'll know about it a fun thing to note is that all these fields can be written in explicitly or can be generated using formulas and indeed we'll do that for the subject so let's look at what we can do with the fields the toolbox pretty self-explanatory we have the address that we're sending the email to this should be a full email address and not just the person's username if you want to have multiple email addresses simply separate them with a semicolon the cc and the bcc box are exactly the same and you can specify cc in a bcc field i'm going to add in a cc example let's send this one to myself there we go and just undo that to remove the hyperlink the bcc box we're gonna ignore one thing to note is that if you don't provide a valid email address that is one containing the at character in at least one of the two cc and bcc fields the row will not cause an email to be created and no attempt will be made to send an email so removing the email address or even replacing the at sign with something else like a question mark or a hash will cause that row to be ignored by the macro so the subject let's create a custom subject now the scenario we used to create the pdfs was an end-of-year letter our interview letter was very simple we just had a compliment in it because we were nice like that but we want our subject to keep with that theme and therefore we're going to have a nice friendly happy new year and a name in our subject so we're going to create this using a formula we'll use the equals and then we'll quote and we'll go happy oops new year and then ampersand to combine text together uh adam griffith and then just for effect we'll put a exclamation mark on the end so there we go there is our subject line and i can just double click on the corner here and the forwarder will automatically create it for all of the recipients so one advantage of this yes you can use formulas to create all of your mail merge data now the importance we're going to make this high you can use high normal or low in this field it is case insensitive but you have to use the exact words other than that my default is set to normal so i'm overriding it with a high sensitivity my default is also normal but i'm going to call this one personal now the sensitivities you can use are personal confidential private and normal at this point if i hadn't deleted them we would have got to the delivery receipt and read receipt fields if you do want to request either of these receipts then simply put a true a yes or a y into these fields and if you want to turn off a delivery receipt or read receipt put in a false no or n next up delivery time now this one's a little bit involved because you can put in a number of different values and get different effects the most obvious way of defining a delivery time is using what's called a date time and this is a combination of date and time and i can type one in here keeping with our theme we want to send this before people go off on their christmas breaks which means we're going to send it out on friday the 18th of uh december 2020 and we want to send it out before people go home we'll make it 15 30 so that early leavers get it as much as people who stay late so there we go that will now send out on the 18th of december 2020 at 3 30 in the afternoon one of the issues that people might have with a date time is that if you lose the formatting it's hard to get back let me show you what i mean here's our date time if i format this in a general format it just turns into a weird number this number marks the number of full days after the 31st of december 1899 and the 0.645 represents 0.645 a day or 3 30 in the afternoon so this number is in fact a date time however it's just not formatted correctly and if you want to bring that formatting we can't use a date because it just gives us a date and we can't use a time because it just gives us a time so we have to dive in here to more number formats and if you go to custom here and this one here dd mnm yyhm that one is our date time and this returns us back to what we're dealing with so if you're doing this and you're you're struggling to get the date time appearing it may be because of formatting so do check that out now we can just put in a date and if we put in a date it will be sent at midnight at the start of whatever that date is and we can put in a time at which point it will be sent at the next instance of that time so if you're doing this in the afternoon and you put in nine o'clock in the morning it will be sent the next day at nine o'clock in the morning now i'm going to delete this because we don't really want to have our deliveries delayed one final note on deliveries is if you put a date or a date time within the past it's simply ignored and the email is sent immediately next up we have these fields account and send as and the account is the actual account you use to send the email and this will be the one that saves the sent item typically this is the only account you have in your outlook if you've got multiple accounts you'll know about it and you'll use this to select an account other than your default account to select a specific account you need to provide the full email address of that account not a username it's gonna be the full email address because that's how it identifies it the senders box is a bit more useful microsoft exchange allows you to be set up so you can send as or send on behalf of either a person or a team now this feature needs to be enabled by your admin but if it is you can put in the email address of the person or team that you want to send us and it will be used now the choice between send as and send them behalf of is done by your admin and it will pick whichever one is set up by your admin if you're set up with both which is technically possible that means you're set up as send as so use the full email address and you can send us a person for example an assistant can send us say the ceo to give a letter a nice official ring to it alternatively you can send on behalf of your team so the communications are marked with a team name and gives a unified appearance to any recipient finally we get to the attachment field now we've only got one here but you can have as many as you want and you simply need to have each field labeled either attachment or if you want to give them unique names you call them attachment 1 and attachment 2 and so on and all you need to do is to make sure that it works is either it's attachment or if it's attachment 1 there are no spaces and there's nothing else apart from the word attachment and some numbers so you can have attachment 5250 if you really want to that will work just as well as attachment one and you can have as many columns as you want so if an email has five attachments to it then you will need five columns for each of those five attachments however if all the emails have only got one attachment which we'll do in this one you only need one attachment field so let's get rid of the other one okay so how do we identify the attachment to the system what we need to do is use the full file path and let me show you how we identify that so here is our windows explorer window again and our attachments are in this folder called pdfs and the full file path is the combination of the folder path which is up here in the address bar followed by the file name and one way we can identify a full file path of a file is to hold down the shift key right click on the file and by holding down the shift key the contextual menu gets this option here called copy as path so we click on that now when we go back to our spreadsheet we can paste in the data and there it is the full file path so it would be quite laborious to go through each and every one of our attachments and add them individually so let me show you a little technique we can use to check and add our attachment file names to this document so first thing i'm going to do is go back to our windows explorer and use ctrl a to select all and then hold down shift and we'll copy all of the file paths so this is now copied five of them and i want you to imagine that you're sending say 150 pdfs so there's gonna be a lot of them there and what we need to do is make sure that they're all available to send as much as to add in the correct file paths so having copied those i'm going to create a new sheet and we will paste in the file parts and i'm quickly just going to label this we'll call this one file paths and i'm going to turn it into a table for ease of reference my table has thanks very much uh expand that and we'll call the table files now i apologize if you haven't used tables much all i've done is uh put some order to the data but fundamentally here is a list of all the files that are available now what we want to do is we want to line up each of the file paths with the correct row using the unique identifier which in this case is the person's full name and the technique we're going to use is a vlookup with wild cards and let me show you how this works i'm hoping you've used the vlookup before even if you haven't used this exact formulation so the first thing we want to do is rather than looking up a value like the person's name we're going to use the person's name with wildcards on either end of it and what this tells excel to do is not look up a cell which exactly matches but look up a cell which contains that bit of text anywhere within it so for the sake of the file path for adam griffith which is here down here this will say anywhere where we have adam griffith and it will match on this one but wouldn't match on any of the others because they don't contain adam griffith so comment the data the way we set this up is go equals vlookup the lookup value starts with a star then we use the amazon to combine text together we'll go across the column a which is the full name and then we'll ampersand in another star like this so if you look up in the formula bar here it's star a2 and star so it's star and griffith star so that's the value we're going to look up the table array we're going to look it up in is the table and we want to return the value from the first column because we want to look up the file path itself so we can use that but we still want the false because it is an exact match yes it's got wild cards but we want it to exactly match and griffith so there's our formula and i'll press return and we'll have to expand this quickly here it is it has somehow managed to retrieve adam griffith from that table and if i double click again it will fill it down and all of the names have been filled in now you may think this is a long convoluted way of doing this because you could have just simply used a text string and combined it with all of this stuff but this will work in any place where you've got a specific identifier that is somewhere within a file name and this will make it quite easy there's another advantage which i'll show you if for whatever reason adam griffith's email doesn't exist and simulate that i'll just delete that uh data from the cell and there we go so we've only got four pdfs available to us when we come back here we finally got a big n a so a big red flag and we can quickly check for that to make sure that the pdfs are available this is much better than assuming we've got a pdf available to us and then finding it doesn't get sent out so we send out a rather foolish looking email that says his attachment and it hasn't got an attachment so there it is i'll quickly undo that so that adam griffith email comes back in so there's our data it has been set up now before i go there's a bit of a pick up i have realized i haven't added in an example for the senders field so i'm just going to pick charlotte murray and i'm going to send this from demo at imnos.com now demo is a sharepoint site and a team that i've created for demonstration purposes and so we'll see this email will go out in the name of demo so that little pickup done let's save this and go back and do the mail merge okay so reopening the docx email we can check in mailings that we've uh we're properly set up here it is edit recipient list and we can see all the data is available we can scroll across it's definitely reddit there's all the attachments and everything else so we know we're good to go so let's run this and we'll do this first of all into drafts to run this what i'm going to do is i'm going to move the mail merge over to the right hand side of the screen here and so here we have it on on the right we have the manage template and on the left we have our drafts box of the email system so you can see the drafts as they appear so now let's see how the macro works if i click on developer i go to macros i've removed all the other macros from this system so you've only got one enhancement merge to email all we need to do is make sure it's selected and click run and the first thing we get is we have a nice little message box here that says mail merge to email will proceed for five records click yes to send them immediately no to save the emails in draft and cancel to abort and note that we've got the no as the default option so that at the very worst you create a load of drafts and don't inadvertently send a bunch of emails so if i press cancel it's always good to have an option to bail out at the last minute this will just simply end the procedure and we won't do anything with the data so i'm gonna have to rerun it rerun it and i'm gonna click no to save the emails into draft so click no and we've got a bit of a flashy thing as each document is created but on the left hand side here you can see we've created five emails all ready to go each with attachment and everything else so let's have a quick look uh beth beth's email here it is uh was set up to cc myself so there's a cc box and we can see here the attachment has worked next to note is that if we actually click on this little arrow on the tags button we get the properties and we can see that the importance is set to high the sensitivity is set to personal and uh we haven't requested delivery or read receipts but we could have and the delivery delay has not been set so the settings have been set to what we hoped now one slight problem with this system and i don't honestly understand why is that the image doesn't display in drafts now it does send so when we send the email it will work but it doesn't display in drafts and i'm not sure why that is but uh don't worry about that if you see it that's gone through the draft we're pretty happy with this so i'm going to close this i'm going to now delete all of these drafts and let's take a deep breath and send all the emails so i click back on macros again and i'll click run and i'm going to click yes there so this is going to go directly to sending all the emails remember we've checked them in draft so we're happy with the content and we're ready to go so i click yes and the computer does five mail merges creates five emails and here is one of the emails that's been sent we can see here that uh it says happy new year sherlock murray we can see here that it has been sent from demo and if we hover over this we see it's a private group it's a the team that we used for demonstrations uh it's tagged as personal with sent with high importance we've got the attachment and the image indeed has appeared now so we are happy with that so that's how we can do an enhanced mail merge with attachments and importance and the ability to send us other things and a custom subject all using simply word mail merge to email plus a macro so now the moment you've all been waiting for let us go through the macro itself okay so we'll bring up the visual basic editor and have a look at the macro now as mentioned earlier this macro is longer and more complex than the one we did for the mail merge to pdf and so i will be going through this a little bit more at a higher level assuming some knowledge of vba so quickly running through it the first thing is this note to add references we've already done the act of adding the references here so we won't do that again second up we have a series of variables that we have declared so the first thing we do is we set the variable mm to the active document mail merge now the active document is the document front and center when we click the run of the macro and therefore the one with the mail merge setup in it but just in case you click around or anything else while it's running we want to make sure we've tagged that as the one that we're using for the mail merge throughout next up all we do here is we check that the mail merge has been set up so it's got a data source and is ready to go this is where we present a message box to the user saying first of all the number of records that will be processed so you get a feel for how long it's going to take and gives you the option to send the emails to save and draft or to cancel so if it says cancel we exit the sub we just get out of there if it says yes then we set the thing called send flag to true and the only other option is no but we use case else to pick up everything else and we say send flag is false having done that and knowing that we're proceeding we will set this outlook app to the outlook application and the fso to this file system object the way we convert the mail merge into an email is that we will save it as an html file and then we'll reread it and put that into the email so we need to have a location to save the file to and a file name for that and what we do is we use the existing temporary file system that exists in windows so what this does is it uses this file system object to actually get a temporary name so normally a temporary name is a combination of letters and numbers followed by the extension temp we're going to remove the temp we'll be needing just the letters and numbers later on so we'll set that up here so next up we have this for loop which will loop through each of the records in the mail merge so it goes from one all the way through to the record count which is the number of records and so with the record number set the first thing we do is we set what's called the active record in the mail merge to the record numbers next up what we're doing is we're just checking that we have a valid or a seemingly valid address somewhere in the data one advantage of this is removing the email address or even replacing the at sign with something else like a question mark or a hash will cause that row to be ignored by the macro and what this does is it sets its boolean quantity which is a yes no true false quantity to false and then it runs through each the fields in the mail merge data source and if the field name matches to two cc or bcc then it tests whether the contents of the field contains this at character and if it does we set the flag to true and we exit the four so we actually get all the way out to here this is the first time you'll see this is trim lcase df.name all we're here trying to do is trim removes uh proceeding and trading spaces in our case converts to lowercase and what this does is makes the df.name which is the name of the field can be case insensitive and if someone's inadvertently put a space at the end or the beginning or something that won't cause it any problems okay so we get to this line and this is where we test whether or not an email address has been found and if it has then we'll continue otherwise we'll just ignore this row and move on to the next one so now we need to save the document as html and we do this by specifying this field as sent new document we choose to have only one record go into this new document so we set the first and last record to the same record number this is a bit like saying print from page one to one you only get page one finally we execute it so this will generate a new word document which will be front and center so it becomes the active document we'll just tag this with this variable called single document so that just in case someone clicks around we won't actually lose the document and start saving a different one there's only two things here so it's very very unlikely but just in case so single doc save as here we're going to save our document as an html file in the temporary folder so uh environ temp that just pulls up the user's temporary folder and here's our temp file name we're going so we just created this file path here which is the temporary folder with a backslash then a temporary file name and temp at the end and the file format we've specified if i scroll over a bit to file format filtered html so that is an html file generated by word from the mail merge once you've done that we can close the next step we're going to read the html file back in to a variable called mail body so this we use this thing called a file system object we open a text file here's the same file name and we just read the whole lot into this string called mailbody so that's effectively created the html code which defines the email with all the formatting and everything else that we want so next up we're ready to create our email in outlook so we set this variable outlook mail to being a new mail item a new email ready to be populated and we will check that the body format is html so that they can receive the html text that we've just generated now here's where things get a little bit complicated when word saves the html file any images are saved in a folder which has the name of the file ie temp file name and then underscore files so what we want to do is scan through that folder and pick up all the images and attach them to the email and by attaching them to the email they're then available to be presented within the body of the email itself so that any images appear in the email so the way we do that is it's fso get folder dot files simply lists all the files that available in this folder which is in the temp folder followed by temp file name underscore files i.e the attach files to the html and for each of the files we check to see whether the file name is referenced in the mailbody as an image and images are referenced as src followed by the file name underscore files and the name so this checks for a very specific tag in the html which indicates that the file is included as an image and if it finds it which means that the in string is just greater than zero then we add the image to the mail and this one and the zero specifies that it is hidden and not included in the main body of the email so having done that we then also replace this source tag within the html of the email with an alternative value which is actually the sources cid and then the name and for our purposes here that simply means the image is contained in the attachments rather than in some folder on your computer so having done that modified the html we can then add the html into the body of the mail so this will now read it all and look at it and go well i can find all my images they're attached to me already and i know where they go so all images will display correctly next up we need to run through all of the data fields and add them or change them as required and this is set up in a very relaxed fashion so that any additional fields that aren't recognized any fields that are omitted anything else do not cause errors they're just ignored so what we do is we go through each and every data field of the mail merge now some of these will be included in the body of the mail like the known as field and what will happen with those is it just simply won't match anything and therefore be ignored equally if it doesn't find for example the delivery receipt field it doesn't matter it just doesn't change anything it doesn't worry and the first thing we do is we actually check whether the value is not blank so if you left a value blank it means you're ignoring it and this tests for that so next up we've got another select case and we set up the thing we're having a look at which in this case is the the name of the field it's converted to lowercase and with leading and trading spaces removed and we have a look at the options we have so the first thing is it the two field if it is then we simply add the value to the to field of the male if it's cc we add it to the cc field if it's bcc we add it to the bcc field and if it's subject we add it to the subject field and you can see that if for example it doesn't find the bcc field in the mail merge data it simply won't ever try and write the bcc field into the email so it will remain blank next up if the field is for important then we want to have a look to see if any of the allowable values are provided which is high normal and low we use another select case for that in this case looking at the value rather than the field name and again we trim it and we lowercase it just in case of any spheres spaces or capitalization and if it's high we set to high if it's normally set to normal if it's low we set to low and of course if it's something that's unrecognizable then we would simply ignore it and therefore the importance will remain at the default for outlook which is typically normal but could have been said something differently sensitivity is very similar except we've got confidential personal private and normal next up read receipt we said we accepted all values of true yes and why if you feed in the uh the boolean true that's the true in capital centered in a cell that will actually be converted into this by this trim function here so this will accept both true as a word and true as the kind of excel cell value that's in capitals anyway if it finds a yes it will set it to true if it finds a false it will set it to false this can be used to override a default for read receipt and finally if it doesn't find anything it just leaves it as it is with the default delivery seat is exactly the same so it moves swiftly on from there right so next up we have the delivery time and this might take a little bit more explanation so the first problem we face is that due to formatting problems the date time value from excel may be just simply a value you saw when we did the video earlier and i reformatted the date time it turned into a value of about 44 000 or it may be a date so this test fit being numeric which is a number or a date and if either those are true it proceeds otherwise it will just ignore it so if you write hello in the field it will just ignore that uh we want to test whether or not it is a time that is in the past so a time in excel and indeed in mail merge is any uh date time value which is less than one which is essentially a period of 24 hours and so this looks for it not only being a time which is less than one but a time that's less than the current time the current time is simply now which is a date time of now minus date which is today's date so this would end up with the time part of the date time only so this simply tests for is the time in the past today and in which case it defers the delivery time to the date plus one day plus the time that was specified so if you specify one o'clock in the morning unless you're actually sending this at about half past 12 at night then it is likely to go this is tomorrow one o'clock in the morning next up we test if the date value is less than one and this test is for a time a time is less than one a date time is gonna be greater than one and if it is then uh we know it's not uh a time that's in the past today it's gonna be in the future today so we simply use the date and the the time that is given so for example if you send give this um 2 300 hours 11 o'clock in the evening then this will most likely be in the future and this will add it to today's date so the deferred date will be today at 2300 hours so finally we get to here we know it's not a time so it has to be a date but nevertheless we check to make sure that it is actually in the future and if it is we set it to that if it's in the past there's no point in having a deferred delivery so we just ignore it so that's what this does adds a little bit of data correction and value added to the input but the result is that we have a valid delayed delivery next up we have account now for this one we can't just set the account based on the string we actually have to search the account and then set it so what we do is we run through every single account in outlook and then if we find an account that matches the the value provided remember the value is a full email address then we use that account to specify the send using account and then we just stop checking accounts if we don't find any matching account then this will simply be ignored almost at the end we have the send as uh field and all this does is check that we have a valid email address and then if so then we use the send on behalf of name field and we set that to the value and center behalf of internet does both send as and send on behalf of the decision is made in the server based on the settings for that person so finally we get to the attachments and because we allow attachments to be both the word attachment and the word attachment followed by any numbers we have to stop checking for the exact name of the field and instead use a more complex test but all this does is it says if the first part of the trimmed lowercase name first 10 characters is attachment and on top of that either after that is nothing at all or a number is numeric then we will count that as a valid attachment we add the file defined by the file path here to our email so finally we end the select that's uh the test for the name of the field we end the if which is the test for the data value being blank in which case we just ignore the field and we move on to the next field so once all that is done it's gone to all the fields then we will be ready to send the email or save it in drafts so right at the beginning we had a message box that popped up and said do you want to send them or save them in drafts and that set this thing called send flag so we test that and if it was set then we send the email and if it wasn't set then we closed the email when saving it finally for good practice we clear the outlook mail variable we set it to nothing and that basically releases it from that email and closing up all of the things and if this is just a test for whether the valid address is present in the row of data and then once that's done we move on to the next record so now record number will go to two and we'll run the whole process again once it's gone through all the records we end the sub because we've sent or saved all of our emails so thank you for watching i hope you found that useful if you haven't watched it before please do check out the video where we created the pdfs using mail merge and another macro in addition please do go to our website we have a lot of other articles and helpful tips and tricks which will help you systematize your work and reclaim your life so thanks for watching see you next time