Excel VBA Introduction Part 29.1 - Creating Outlook Emails

welcome to this byzal tutorial in this video we're going to cover how to create outlook emails using excel vba so the video is all about how you can control microsoft outlook by writing vba code in an excel project there's quite a lot of topics to cover and it gets quite complex but we'll start with the basics of how you can reference an outlook object library and how you can simply open up outlook and create a new email object we'll show you how to set basic email properties such as who the email is going to go to and what the subject is and also how you can set the format of the email and add text to the body of the email depending on which format you've chosen we'll show you how you can include a simple signature and add basic attachments to the email before we show you how you can then make the code version independent to make sure it will work on almost any version of office for the latter half of the video we're going to get into more complicated techniques first of all showing you how you can write complex body text which is built up of various values of cells and we'll include there how you can build an html email by using various html tags and applying basic formatting as well towards the end of the video we'll look at how you can then use the microsoft word editor to generate a rich text format email and then finally we'll show you how you can make sure you can choose a specific signature file to be included at the end of each email so there's certainly a lot to do and it gets quite complicated certainly towards the end let's get started the first example in this video we're going to write some vba code in excel that creates a brand new outlook email and then sends that to somebody we'll stick with basic plain text emails to begin with and deal with more complex html and rich text formatting later on and for this video i'm going to be using office 2013 although if you're in office 2010 and 2007 it doesn't really matter the same code will still work so to get started i need to head to the vb editor and i've actually already set up a brand new module for myself called using outlook and i'm going to begin a new submission in here called send basic email before i start writing any code i want to make sure that excel will understand what i mean when i start talking about things like the outlook application and email objects at the moment excel doesn't even know that outlook exists so we're going to widen excel's vocabulary by setting a reference to outlook's object library if you've watched the videos on how to create word documents or powerpoint presentations this is exactly the same process head to the tools menu choose references and then scroll through the huge long list to find the option for microsoft outlook there will be a version number listed after the word outlook in the list so in my case it's version number 15 because that's uh that's because i'm working in office 2013 and that's the internal version number four of that version of office if you're working in office 2010 you'll find this is the version number 14 in 2007 it's version number 12. there is no version number 13 which is good news if you're superstitious i suppose so i'm just check the reference for the um for the version you have available and then click ok and now excel knows how to talk to outlook and it will understand the definitions of all the main keywords that are part of the outlook object library now we can declare a variable which will hold a reference to the outlook application so i'm going to call mine ol app short for outlook application as and in the intellisense list i want to look for the word application and i find that that appears twice in the list once for excel month for outlook it doesn't actually matter which of the two you choose at this point this reference actually gets resolved when the code gets compiled what excel will do is it will check through its list of references that we have set and the first library encounters which has a definition of what an application is that's the type of application you'll get so because excel sits above outlook in this list this variable will actually allow us to hold the reference to the excel application we can change the priority of our libraries by selecting them and using the arrows to increase and decrease their priority but you can never move out lookup of excel as you're programming in excel that kind of has to take precedence so the way to get around this is rather than referencing the word application directly you can qualify it with the name of the library to which it belongs so if i press control and space to display the intellisense list i can look for the word outlook in the list that's the reference to the outlook object library you see this little icon which is a set of books and then in there i can type in a full stop and look for the word application here there's only one definition for an application in the outlook library so that's the type of application we will get so that's the variable declared which will hold the reference to the the application what we need to do next is actually start outlook running to start outlook running we can create a new instance of the outlook application and to do that we can say set olap equals new outlook.application so that line starts out like running although you wouldn't physically see outlook appear on screen one way to prove that outlook actually is running is to use the windows task manager application if i press control and shift and escape on the keyboard that will display the windows task manager and on the processors tab i've got my processors sorted out particularly by name i can click on the column heading there to do that if i scroll down to where outlook will appear when it is actually running what i can then do is head back to the subroutine and i'm going to use f8 to begin stepping through so if i execute this line of code and then switch back to the task manager i'll say i now have a process called outlook.exe so that's that's the process that's been generated by that line of code now this process will remain available until we finish with it so and when we end the subreddit if i press f8 here to end the subroutine and then quickly switch back to the task manager you'll see after a short period of time the outlet process will disappear so when you finish working with that look it will automatically tie you up after itself so i'm going to close down the task manager application now that we have a new instance of outlook available what we can do is actually make use of it to create a new email just before we create our new email i wanted to mention a couple of other techniques you could use to create a new instance of outlook if you've watched the videos on word or powerpoint before this one you'll be familiar with these two techniques by now so sorry this is a bit boring by this point um but the first technique i'm going to mention is something called an auto instancing variable i'm going to make a copy of this subroutine and paste it in down below make a small change to the name of the routine i'm going to call it auto instancing and the idea behind an auto instancing variable is that you don't choose when to create a new instance so i'm going to remove that line altogether instead you can incorporate the new keyword into the variable declaration slightly misleadingly that line doesn't actually create the new instance of outlook what happens is anytime the ol app variable name is used in code the vb editor checks to see if it references an application yet and if it doesn't it will create the new instance automatically so if we were going to do something to the application that's the point at which it would be created now that sounds really convenient basically you can save writing an entire line of code there can't you but there are two small downsides to it first of all it's not just the first time you use this variable naming code it's every time you use ol app in this subroutine the vb editor has to check if it references something or not so that does add a small overhead to the subroutines that you write the other small problem is that you can't yourself test if ol app is nothing then do something so for example you might want to display a message there's a very simple example outlook isn't running because as soon as the vb editor encounters that keyword then that's the point at which it actually starts running so if in fact if i display the task manager application at this point just so you can see outlook isn't running at this stage if i begin stepping through this routine do you see it skipped over the statement so this this logical test failed and that means the outlook must now be running and indeed it is so that's the idea of an also instancing variable it's one that gets created automatically as and when it's needed but there are a couple of small downsides which mean that i don't tend to use it myself in the real world so for the rest of this video we're going to be going back to the non-auto instancing variables the other technique that you could use to create a new instance of outlook is related to version control and essentially what happens if you sent this workbook to somebody with a different version of office installed to you so if i head to the tools menu and choose references earlier i set a reference to outlook 15 which is part of office 2013. if i sent this to somebody who only had office 2010 or 2007 installed the outlook library here would be listed with the word missing next to its name and the code wouldn't actually work the library was forum for excel and office would actually automatically change their versions down to whichever the user was running it's just extra references that you've set that wouldn't automatically do that so what i'm going to do here is uncheck the reference to outlook and click ok and that means at this point i can't run this subreddit in at all because if i try to do that i'm told that basically excel doesn't understand what an outlook application is so if i hit okay and reset the subroutine i'm going to make a quick copy of this and paste it in down below i'm going to call it send basic email create object and the idea is that rather than referencing the outlook application class which excel doesn't understand at this point you use the more generic keyword object and then instead of using the new keyword you call a function that's part of vba called create object if i open a set of round brackets there you'll see that it's got two parameters and there's one compulsory one called class as string so a class is like an outlook.application it's a type of object or a definition for a type of object the create object function allows us to pass that in as a string of text which means that the vb editor doesn't have to understand what that is it doesn't have to interpret what outlook.application is when the code is compiled so that means again if i use the f8 key in fact if i display the the task manager first ctrl shift escape and scroll down to where outlook will be and then if i step through the routine using the f8 key we'll see if i go back to the task manager i have a new instance of outlook so essentially the create object method allows you to make your code version independent these techniques are distinguished by the terms early binding and late binding so late binding is when you don't set a reference to an object library you rely on the vb editor to work out which library it should set automatically when the code runs the one small downside to late binding is because we don't have a reference to the object library we don't get any help in form of the intellisense list when we write our code so if you remember earlier on when i said olap dot you've got the full list of all its methods and properties now you have to know exactly what code to write because you don't get any help whatsoever in the real world what you tend to do is start writing your code using the early binding technique and then when you're ready to release the program to other people that's the point at which you switch to a late binding technique and that's the approach we're going to take in this video so i'm going to remove the late binding method from there and i'm just going to remove that comment as well and i'm going to head back to the tools menu and choose references and then scroll down the list to find microsoft outlook 15 check that box again and now we're ready to start writing some code that will actually create the new email when i create the new email object i want to store a reference to it in a variable to make it easier to work with so to start with i'm going to declare a new variable called ol email as outlook dot mail item to create the new email itself i'm going to say set ol email equals then i'm not going to use the new keyword what i'm going to do is use a user method of the outlook application itself so i'm going to say olap dot create item then open a set of round brackets to see a list of all the types of things that i can get outlook to create for me now obviously we're creating a mail item object so i want to create or specify that i want an ol mail item object created but you can also create things like contacts and appointments and all sorts of other cool things as well we're going to stick with emails for now at least so i'm going to close around brackets after that and that will give me access to my new email object now just as when we created the new instance of the application we didn't actually physically see it appear on screen you wouldn't see the email that you've created appear either but it's fairly straightforward to make that email visible so what we're going to do is say ol email dot display so display is a method of the email object and then all we need to do is run the subreddit team to see what we end up with so we ought to end up with basically a blank email for the time being at least you will have any standard footer text that you've set up for your email account other than that we have to fill in every single property of the email before we can actually then send it so that's the next step filling in the various properties of an email object because we're going to be changing lots of properties of the same object rather than having to write ol email several times what we can do is use a with statement so i'm going to put the word with in front of ol email and then i can take the dot display down to the next line make sure that i have an end with statement as well to go along with that and then we can simply carry on filling in the various properties of the email so let's start with who the email is going to there's a property there called two and i can make that equal to an email address i better not give you my emails just like um let's put in a generic email i'm gonna put someone at somewhere.com that will do is a very very generic example then we could put in we could put in a a cc or a bcc property as well into the carbon copy or blind carbon copy fields the email address themselves you don't just have to put in a single email at a time you can put in a list of addresses you can have a semicolon limited delimited list of email addresses so i could say someone else at somewhere.com uh or i could put in a named mailing list if you have a mailing list in in your um outlook account you can put in the name of the mailing list there essentially anything you would ordinarily do if you were filling in this uh this two field in the email i'm gonna stick with a single email address just for now the next thing i'll do is fill in the subject so say dot subject equals i'm gonna call that seem like um movie report and then um in fact for now i think that's probably enough what i want to do is just have a quick look at what the email will look like when i run it so if i press f5 this time i should see when the email appears we get the email address filled in the two field then we got the subject filled in as well so that's how easy it is to change basic properties of of an email object i'm going to close that down without saving the changes the next thing i want to do is start adding to the body text of the email and that's where things get a little bit more tricky the main thing which makes setting the body text of an email so awkward is that outlook has three different formats you can use to compose an email in so if i quickly switch into outlook and i've displayed the outlook options dialog box here already so if you're in office 2010 or 13 you can enter the file menu and choose options in outlook 2007 i believe it's the office button and outlook options on the mail tab you can see the default format that messages are composed in it's mindset to html but there are two other types british text and plain text the format that's used for any an email um determines which product you should use to set the body text so if i cancel that dialog box and close on outlook you'll see that if i run this subroutine now my email says html at the top of it so it's in the html format but i can change that either manually from the format text tab i can choose plain text or rich text but i can also change that property in code it's always worthwhile doing this to make sure that the email that you generate is the format that you're expecting so let's change that property first of all the property which changes the body format of your email is fairly conveniently called body format so if i say dots body format equals i get a list of choices html plain and rich text also unspecified which i'm not going to consider here so i know that my email is already set to an html format so let's change it to a plain text email instead so if i do that and then run the subroutine you see two things first of all you'll have the word plain text here rather than html and also smart disappointingly we've lost our signature this is actually just a feature of where you change the body format of the email what i'm going to do is close that down without saving the changes and instead of setting the body format after the email has been displayed i'm going to move that line so i'm going to cut it from there and place it at the top so i'm going to place it before we display the email so having done that if i run the submitting again we will see that we've got a plain text message again but we actually retain the signature that's quite important if i just quickly change it one more time to the other options so rather than um playing we'll have rich text since that's rich text and run the subroutine again and this time we'll see it's a rich text email and we've got the nicely formatted version of the signature so that's the first step changing the body format property the next step is to actually change the body text of the email we've got several choices of how we do that depending on which body format we've chosen so the generic way that you do this regardless of which body format you've set is to modify the body property so if i say dot body equals um let's put in a single simple line of text i say dear someone so again regardless of which body format you've chosen this will allow you to set the body text and if i run that one we will see we've got a rich text email there with a phrase dear someone in it again of course we've lost the signature but we'll resolve to get that one back in a few moments let's just check quickly that it will work whether it's a an html email as well so format html and if i run it again we'll get an html email with dsm1 and again we've lost a signature and one more time we'll run it as a plain text email format plane one more time and there's the plain text version okay so the body property is the most generic way to do this it's worthwhile quickly mentioning that there are a couple of other properties you can use dedicated products that you can use for html emails so if i revert the body format back to an html email and then instead of just saying dot body i'm going to say dot html body it won't make any difference if i just use a basic single simple string of text like this if i run that one we will get a fairly straightforward string of text but the beauty of the html body property is you can put in proper html tags so i did something very quick and simple just to demonstrate the principle if i put an h1 tag and then close that tag at the end so i'll say slash h1 that will put in a the phrase there someone formatted as heading 1. so i've run that one this time then we'll get a completely different format so we'll come back to the html body a little bit later on and show you a couple of other things that you can do there is one other property as well that you can modify i'm not going to demonstrate in too much detail here but if i had a rich text format there is a rtf body property now this one's a little bit more tricky to work with um the rtf body doesn't allow you to put in simple strings of text like this the rtf body property only accepts a certain kind of data so i try to run this one at this point and this will fail so i'm going to come back to the rtf body again a little bit later on and show you what you can do with that so for now we're just going to stick with the basic body property so i'm going to change that back to the body property and change the format of the email back to plain text the next thing to do is to make sure that we actually retain our email signature so let's solve that problem now assuming that we were going to change the body text just using a single instruction the easiest way by far to incorporate the existing signature is just to concatenate the body property to the end of our unique phrase of text there so what i can do is i can use an ampersand to concatenate i want a new line character so i'm going to use a vbcrlf visual basic carriage return line feed and the existing body property of the email object if i just say dot body that will tag in whatever is already in the body to the end of my custom phrase there's a small problem with the way this works if i run the subroutine at this point we'll see that it has apparently absolutely no effect whatsoever and the reason for that is because the point at which we're calling on the body property the signature hasn't been generated yet that only happens when the email object gets displayed so if i want to make this work again the order of instructions for outlook is much more important than you'd expect it to be so i'm going to change the where the display method occurs it must i must make sure that this happens after the body format property because as we saw if i display it and then change the body format we lose the signature so this changes the body format and then displays the email which generates the signature and then we can reliably pick up on whatever was already in the email using the body property so finally having done all of that if i run the subroutine one more time there we go there's our basic custom phrase with our signature at the end of it now doing the same thing for an html email is very similar in principle if i close down this copy of the email and don't bother saving the changes there are a couple of things i'll change first of all i'll change the format of the email to an html email then rather than changing the body property i'll change the html body property the character code for joining a line breaking html is not vbcrlf that's a visual basic constant so the character code for html is the break tag so in a set of double quotes the letters br inside a set of angle brackets i could have actually just put that in after the dear someone that would have happily gone in here in fact let me put another one in here as well so i get two line breaks what i also want to do is modify the the body property that i'm calling on because i've run this one at this point all i will get is the basic formatting of the body property what i really want is the nicely formatted html body so i get rid of that and done by saving changes and read the html property when i run the subroutine again i'll get the nicely formatted version with my original signature format one more simple thing that we could do with this email before we get into the complicated stuff is adding an attachment to it so what i'm going to do is attach a word document to the email and the one i'm going to add is this one movie report which is sitting on my desktop currently so i'm going to use this folder path to attach the document i'm going to copy that to the clipboard at this point because i'm going to need that in a moment back in the vb editor to add an attachment to an email is really straightforward you reference the attachments collection of the email object and then simply say add and there's a single compulsory parameter of that the source parameter which as you've probably guessed has to be a file and folder path so if i paste in the path that i've just copied and add a backslash and then say movie report dot dot x that will add that particular document now in the real world of course this relies on the user who's running this code to always be me or logged in as me at least so what i might do here is replace the c users andrew gold with a call to the environment function which lets me ask for the user profile that will return that path and then if i concatenate onto the end of that the desktop folder and the name of the file that will make sure that it works for whoever's running the code as long as of course that file exists in that folder so if i run that subroutine at that point that should show us that the document gets attached one last little tweak that we'll make is to add the send method to the end of the with statement and as you probably guessed this will literally send your email this is something you only want to do if you're absolutely 100 confident that the email you've generated is correct and will go to the correct person i've heard some wonderful horror stories from people in the past about sending auto-generated emails this is the wrong person with the wrong content in it and you definitely do not want to fall into that trap so the send method will send the email i'm not actually going to run this one at this point um just to mention this is how you would do it i'm going to comment this out for design being so that i don't accidentally do that so there's the uh the basics of generating emails with outlook earlier in the video i mentioned the idea of using a technique called late binding to make your code essentially version independent and just while we still have a fairly nice simple version of our subroutine before we make it more complex i'd like to revisit that idea and show you how you could convert this subroutine into using a late binding technique so what i'm going to do is make a copy of this submission and paste it in down below and then change the name of it so that it's called um create send basic email late binding using the late binding technique means that we can remove any reference to a specific version of outlook's object library so i head back to tools references and uncheck that box and then click ok that means of course that the code won't work anymore and if i try to run it i'll get compile errors and essentially anything that i've used from the outlook object library any keyword or class the vbr now has no definition for what that type of object is so if i click ok the first thing we'll have to do if i just reset the subroutine is change any explicit reference to an outlook class to the more generic object so that goes for both the outlook application and the outlook mail item we'll also have to change the way that we create the new instance of outlook so rather than using the new keyword we can change that to a call to the create object function and the reason we're using this is because it allows us to pass in the class of object we're creating as a string of text which means that that doesn't have to be interpreted when the code gets compiled it's interpreted at runtime instead so there's one more set of things we'd also have to change a little bit more difficult to spot but if i try to run the subroutine again at this point there's going to be another couple of compilers cropping up and these are to do with the constants that we've used from outlook's object library now a constant is actually just a convenient keyword that we can use which actually represents a number behind the scenes and if you want to find out what those numbers are really the only way to find out is to use outlooks object library so at the moment i'm going to have to re-check the reference to microsoft outlook and let's scroll through the list and find that again and there it is and if i begin stepping through this subreddit now at this point using the f8 key the most convenient way to find out what value a constant represents is to simply hover the mouse cursor over its name and the tooltip will tell you what value that constant actually refers to so an ol male item actually has a value of zero so if i replace a well-mail item with a value zero that will now do exactly the same thing likewise the next one is ol format html and has a value of two so i can exchange that for a number two and that's all of the constants that i've used in this fairly small short subroutine job is going to be a little bit more difficult when you have a much larger subroutine but that's the process that you would go through so if i reset the subroutine again now and head back to the tools menu and choose references and uncheck the reference to outlook i can run this one one more time and find that we get exactly the same results as though we'd used an early binding technique but with the added convenience of allowing this code to work on almost any computer of course as we saw earlier the big disadvantage with using late binding is you don't get any help whatsoever in the form of the intellisense list so in my with statement now talking about an ol mail item object if i type in full stop no intellisense which means i have to know every single method and property to use it effectively so for that reason we're going to revert back to an early binding technique for the rest of the video but hopefully you're aware now of how you can go about changing your code later on to a late binding technique to make it version independent so just for the rest of the video i'm going to remove that subtleties in all together and go back and check the reference to microsoft outlook and then we can start thinking about doing somewhat more complex things so find outlook check the reference and there we go for the next part of the video i'd like to look at how we can generate more complex body text for the emails that we're creating so to get started i'm going to make a copy of this entire subroutine and i'm going to put it into a new module i'm going to change its name to send complex email and i'm going to change the name of the module to using outlook part two so the example that we're going to do here is we're going to write an email that has a copy of this table of data in it now unfortunately we can't simply copy and paste at this point using the basic properties of our email so what we're going to have to do is write out the contents of each individual cell into the email itself we're going to do this first of all using a plain text email because that's slightly simpler and then we're going to have a look at how to do it using html but essentially the process will be the same we're going to be looping over this range of cells here from a2 down to a15 and for each row we're going to loop over the cells in that row and write out the contents of each cell one by one into the email we'll also have to work out where we put in our line break characters to make sure that the rows of data go into separate rows and we're going to do all of this by creating a custom function but as i said we're going to do this first of all using a plain text email so back in the vb editor what i'm going to do first is modify the body format to ol format plane and then of course not changing the html body we're going to change the basic body property and of course we don't want to insert a an html tag into that so we're going to convert that into we could use vbcrlf or we could use vb newline i'm going to i'm going to go with a new line this time just to tidy up a little bit as well i'm going to ignore the idea of the attachment so the attachment basically contains the data that i'm going to show in the email now so i'm going to remove that line all together i'm also going to just remove this little part here about sending the email as well just to make things a little bit tidier so we're focusing essentially on what happens here when we set the body text now because the code to generate the body text is going to be quite long as i said we're going to separate it out into a different function so just below the subroutine i'm going to declare a new function called get movie data and the function is going to return a string we'll need several variables in this function in order to process our table of data so i'm going to have dim film column as range and that's going to hold the range of cells in column a that comprises our list of film ids we're also going to have a variable called film row as range and that's going to hold each individual row of data as we process the column we'll also need a couple of generic range variables as well i'm going to have one called r as range and we'll call c as range and that will let us process the the loops when we're looping over the collections of objects that's all the range variables we will also need to have a variable where we can build up the string of text which represents the body of our email so i'm going to call this one something nice and short and simple like str as a string now what we can do if i just scroll this down a little bit to make it a bit easier for you guys to read what i can do now is make sure that we go to the correct worksheet i'm going to say sheet1.activate and then i'm going to set which range of cells is stored in the film column variable i'm going to say set film column equals range a2 so that's going to be the top selling list and the bottom cell in the list is from range a1 dot end excel down so if you've watched previous videos in this series before you'll be familiar with this technique by now essentially what it does is sets the top cell in the list to be cell a2 explicitly and the bottom cell in the list is going from cell a1 down to the end of the list in a downwards direction so you can simulate that by holding down the control key and pressing the down arrow key on your keyboard and that jumps down to the bottom selling list so this will guarantee that the code will work regardless of how long the list is assuming that there are no gaps in the list as we've mentioned several times before so that's the column of cells stored what i can do now is write the code that will loop over that column of cells to do that we're going to use a basic for each loop so i'm going to say for each r in film column a couple of blank lines and then say next r so that loop there will process the column of cells from a2 down to a15 it will look at each one in turn what i would like to do for each row is to store a reference to that row of cells in the film row variable so again that means a very similar technique going from the left hand edge this time to the right hand edge of the sheet so this will make sure that it works however many columns are in the table of data so the first job inside our first loop is to set a reference to a range of cells in the film row variable so set film row equals range and the starting cell this time will be whichever cell is referenced by the r variable so that's simply r and then the end cell will be r dot end excel to write and again this is assuming that there are no gaps in our list at any point but that will store the row of cells in that variable then we can write the inner loop which will say for each c in film row a couple of blank lines and then say next c so that's the basic loop structure setup all we've got to do now is start writing out the contents of the cells into our string variable essentially what i want to do here is for each cell that i encounter i want to make the string variable equal to itself and the contents or the value of that cell i'd also like to make sure that the content of each cell gets separated from the next by using a vb tab character so i can say and vb tab now there's more problem with doing it this way of course is that every single cell in a row will have a tab character after its value and that includes the last cell in the row as well so the final column i don't want to add a tab character to the last cell in the column so rather than just always adding a vb tab to the end i'm going to use an if statement that will check if c dot column is less than r.m excel to write dot column then str equals str and the vb tab end if so this guarantees that i will only get a tab character as long as the column that i'm in is before the last cell in that row the last column in that row we can use a very similar technique to add on the new line character at the end of each row of data so just after we finish looping over all the cells in a particular row we can work out whether we need to add in a new line character so we only want to do that as long as we're not on the last row of the table so i can use an if statement i can say if r dot row is less than range a1 dot and excel down dot row then str equals str and vb new line and if and that will give us the complete table of data from the um from the contents of the cells in excel what we finally need to do to make this function work is tell the function to return a value by saying after the loop get movie data equals str so whatever string we've built up in that variable that will be the string of text returned to the function results so that's the whole thing in one page now we can simply plug in the results of this function into the main subroutine sitting at the top of the module so to do that i can head back up to the top and after we've written out the greeting and a new line character i think we're going to have another new line as well so i get a blank line between the greeting and the table and then i can simply concatenate in the result of my get movie data function to do that i just reference get movie data so having done all of that now what we should be able to do is run this subroutine and get not particularly pretty table admittedly but the important thing is we've got the complete table of data from excel into the email that's the important part the formatting is a little bit strange because i've used the vb tab character and the width of the tab character varies depending on how many characters are in each division of the page so when there's a nice consistent set of a number of characters in the values then you get nice consistent spacing when the number of characters differs you get somewhat more inconsistent spacing but as i say don't focus too much on the formatting here this is again a plain text email you're never going to get great formatting in a plain text email the important thing is we've got the complex table of data written into that email now if you did want a slightly more presentable version of this table of data then we're going to have to use either an html or a rich text format document instead and we're still not at the point where i want to deal with rich text format documents just yet so i'm going to show you how you can generate this table in an html email and actually format the table as well so i'm going to close down this email here and don't save the changes then i'm going to make a copy of everything in this module or at least the subroutine and the function so i'm going to copy those two things out and i'm going to create another new module you can probably guess what this one's going to be called and i'm going to paste in these two procedures first and i can rename the module as using outlook part 3 and then i can rename my two procedures first of all the function i'm going to call get movie data html and the subroutine sitting at the top i'm going to call send complex email html that of course means that i'm going to need to modify a couple of things in this subredditing as well so i'm going to have to modify the body format to ol format html and of course i want to change the body text to use html body instead so let me just scroll this across so we can see the whole thing in one screen with again i can't use the vb new line characters to put in the line breaks so i'm going to change these to in fact i can do this in one single go can't i in fact i don't even need to concatenate those i can just add in the line break tags into the original string so i can say break break get movie data then as well i don't want to call the original function i want to call the new version of the function that i'm about to create gets movie data html now we have to work out how to generate an html table which is a little bit different to creating just a simple plain text table just before we start modifying this function i think it's worthwhile having a quick look at what html tables actually look like now i know html expert by any means but i know enough to get by so i've opened up a copy of notepad here and just show you what a basic html table would look like there are three main tags involved in an html table the first one is community called table so html tags get written out in a set of angle brackets and once you've opened a tag you also have to make sure that the tag gets closed later on so a couple of lines further down i'd have a close tag which is essentially the same just with a forward slash in front of the name of the tag so that creates a table inside the table then we have to define the rows and the columns or the rows and the cells technically so inside the table i'm going to tab in here that the indenting is not important it's irrelevant just like in vba the indenting does not actually matter it just tells us to read it so a row is defined with a tr tag and again you need to close that tag later on as well and then for the individual cells in that row use the td tag so i've got a td and then inside the td tag what i can do is write out the contents of the cell so in between those two tags i could say something simple like column one then if i wanted another column or another cell in that row i could simply open up another td tag i don't know i didn't just copy and paste that actually and call that column two close the td tag and that will generate a table with a single row and two columns in it if you wanted more rows than um with the same number of columns it's very very simple you just have more tr tags so you can copy and paste those quite happily and then just change the values um here so i'm going to say this is row two i suppose and this will also be row two and that's the basics of how an html table works it's just a sequence of nested tags one table tag table row tags for the number of rows and td tags for the number of cells or columns in each row so what i'm going to do is i'm going to quickly save this file as a text file on my desktop um oops i'm going to give it a name first i don't know that's why i wouldn't save um html table save it and then close it down and then what i'm going to do just to show you how it actually works i'm just going to change the the txt extension to an htm extension which is fine and then i'm going to open it up using web browsers in this case internet explorer so what this does it will translate that html text into an actual document so that's an html document so that's what our basic table will look like so that's the sort of thing that we need to be able to generate in our code we need to make sure we add the correct tags to the correct place to make sure that our table gets generated in the correct way so let's head back to the vb editor again and we'll start modifying the function to include those tags let's start by adding in the main table tag we know that that has to enclose the entire table of data so we can actually start that before we begin looping over the cells we can simply say str equals and then have the opentable tag now we know that we need to close the table tag after we built the entire table of data so we can simply add that line to the line after the loop so we can say str equals str and the close table tag nice and simple the next thing we can do is build in the tags for each row and we can do that just inside for each r in film column loop so we can say ser equals str and the open tr tag and then after we finish looping over that range we we don't need to worry about whether we're on the last row or not to add in the closed table row tag so unlike with our previous example where we were testing whether we were on the last row or not to choose whether or not to add in the new line character every table tag that we open like this has to be closed explicitly so we'll say str equals str and close tr so there's the um the code for the table row tags we can do a very similar thing now for each table cell again we don't need to check whether we're on the last column or not so we can remove that if statement altogether and simply say for each cell that we encounter we're going to make the string variable equal to itself and the open td tag and then the value of the cell and then a close td tag like so so actually the code for doing this for an html table is actually a lot more simple than with a basic plain text table we haven't got to check whether in the last column of the row or the last row of the table we always open and close every tag that we open one thing that we do have to change that i almost forgot to do earlier on is i've changed the name of the function here get movie data html i haven't changed the name of the function down here i'm not returning the string to the correct function name anymore so i need to make sure that this matches the name of the function that i've declared up here all right so having done all of that let's give this subject in one final test what we should end up with is a much more neatly arranged version of our table so an html table has a very very consistent spacing between these different columns and that's how you can generate html emails in vb code one of the useful features of writing html emails is that it gives you the opportunity to format the body text which is something you can't really do with a plain text email now there are several different approaches to formatting using html i'm going to start by showing you a quick simple example of something called an inline style so just to give you a quick idea of how inline styles work i'm going to go back to my basic text document and the idea behind an inline style is that you write an attribute or add an attribute to the tag that you want to format so if i want to format my entire table what i can do is apply the style attribute to that tag so table space style equals and then inside a set of double quotes i can list out all the properties of that attribute that i want to change so for instance if i want to change the color of my font i could simply say color colon red yes because i spelt the american way again just as in vba um so if i wanted to add another property to that attribute i can type in a semicolon and then i could say for instance font dash size colon 20 px for pixels as you can see you kind of need to know your html reasonably well in order to make this work i'm no expert in html by any means but i know just enough i think to do some basic things one more thing i'll do another semicolon i'll say font dash family colon aerial so having done all that now that should modify everything in the entire table so if i close this down and choose to save it and then i'm going to go back to open the file up changing it to an html document burst so i can say htm choose yes to make sure that happens and then double click to open it up in a web browser so you can see that it's modified all the the font properties it's aerial font size 20 pixels and it's different to 20 points by the way and it's clearly red as well if i want to make any further changes i could also um do if i open this if i right click on the file and choose to open with notepad i can edit it directly without having to change the extension name each time i could even change individual rows if i preferred so what i could do here rather than apply the color red to the entire table what i could do is modify the style property of individual rows so i could say style equals and then in a set of double quotes i can say color column red for the first row and then for the second one if i just copy that part out the second row i could say color blue so having done that again i can close it and save it and then open it up in the web browser again you'll see this time i've got differently colored rows because of where i put the style attribute so that's the basic principle what we need to do now is out work out how to incorporate that into our html code in vba let's start by applying some basic formatting to the greeting text in the html body so at the moment the dsm one line actually isn't enclosing any tags whatsoever so that just has some basic generic formatting applied what i'd like to do is control that a bit more detail so um i'm gonna enclose the dear someone text inside the p tag so p is your paragraph so i say p and then after this someone i can say close p so that puts that in a proper html tag now within there i'd like to apply the style attribute and because this is going to make the rest of the code not run off the line i'm going to use the line or the continuation characters so i'm going to say amazon space underscore then that allows me to carry on typing that line of text on multiple lines so in this paragraph tag that i've opened i'm going to put in the style attribute so p style equals now if you remember from the basic plain text document that i was showing you just there after you have the um the style attribute you need to enclose the rest of the properties inside a set of double quotes and that's going to cause us a small problem in vba because if i try to put a double quote inside a wider set of double quotes here so i'm trying to concatenate this in in one single set of double quotes this won't work this will give me a syntax error when i move away from that line so the trick to doing this is if you want to include a single double quote in your string of text then you actually put in two double quotes in a row and that is perfectly fine the other approach you might have seen as well is if you want to concatenate a double quote character into another string then what you can do is this so you can say ampersand double quotes double quotes double quotes double quotes amazon and that code there will concatenate one single double quote character into the wider string that's a little bit fiddly isn't it so i'm going to revert back to just having two double quotes in a row and that represents a single double quote inside that longer string of text huh right so having got over that what we can do now is say things like uh color so say color colon blue and then a semicolon and i can say font dash family colon i'm going to call it calibri and then finally i'm going to change the font size no i think i'll leave the font size exactly as it is so again now i need to close a set of double quotes inside the style for the style attribute so again i'm going to put in two double quotes in a row and that will generate the correct formatting for my greeting in the email so i'm gonna give this one one quick run through just to see what we end up with and hopefully we'll find that dsm one has changed to blue and it's in font calibri now as well okay so that's the basics of using inline styles and we could go a bit further now and apply lots of inline styles to our table in the get movie data html function as well now although we could use inline styles for absolutely every bit of formatting in the html document it does get a bit tedious doing that particularly if you find yourself repeating the same style for multiple tags so a more sensible approach is to use something called an internal style sheet and that's essentially where you declare all the styles at the top of your html document and then all the tags will just use the styles that you've declared so just to show you how that works in terms of basic principles i'm going to go back to a basic version of my html table and i'm going to add a section to the top of this document i'm going to add the head section so i open up the head tag and then close the head tag afterwards and then i can add all sorts of declarations into that section for consistency's sake we should also have a body tag which encloses all the body content of the html document so i've got an open body tag and a closed body tag at the end okay so in the head tag itself what i can do is open up the style tag and then inside there i can declare all of the individual styles that i would like to use and it works like this so let's say i want to format all the paragraphs in the entire document i type in letter p to represent the p tag and then inside a set of curly brackets i list out all the properties that i want the style attribute of a paragraph to have so i could say for instance color column blue semicolon font dash family colon calibri and so on and so on what i could also do is apply a table style so i can say table and then open and close curly brackets and then in there i could say things like again color column blue semicolon uh font dash family calibri and i can also put maybe a border around the table as well so another semicolon and i can say border colon 1px so it'll be one pixel wide and i want it to be a solid border and i'd like the watercolor to be blue as well okay so there's some basic um styles declared what i can do now is save that document close it down and then i can open it back up again in an internet explorer and i should see that now that all the text in the table is blue and it's got a single pixel border that's also blue around the outside so if that's how i can declare styles for the entire document i simply need to work out how to attach or insert that into the main string that i'm returning to my get movie data html function in fact rather than modify the existing function which already does a particularly useful job we'll create a new function altogether that will generate the html head section so down below i'm going to say function get head html and that's going to return a string enter a couple of times i'll declare a string variable in there that i'll use to build up the html text to dim sdr as a string and then we can simply start concatenating this to build up the uh the head section let me give myself a few blank lines to make this a bit easier to read and we can start by saying str equals and in a set of double quotes i can say head and then style so that will open up the head and the style tags of course i'm going to join on a vb new line character just to make this easier to read when i test it later on you don't need new lines in your html code this is again just purely to make it easier for us to read then i'm going to say str equals str and and let's start with the paragraph or the p tags i'm going to say p followed by space open and close some curly brackets and then inside the curly brackets i'm going to say things like color colon blue semicolon font family colon calibri semicolon should we say font dash size colon 20px as well then at the end of that line i'm almost going to concatenate another newline character and then we can carry on and let's declare the the style for the table tag this time so i'm going to say str equals str and opens and double quotes table open and close some curly brackets close the double quotes and then actually i'm going to copy and paste all the properties for the paragraph that i've already declared just for the sake of convenience and to stop boring you guys watching me type out lots of different properties i'll also add in the border property for the table as well it's going to say border colon 1px solid blue i'll add one more new line character to the end of that then i think that's probably enough just to be able to demonstrate the principle of this what i'm also going to do then is say str equals str and i'm going to close the style tag and i'm going to close the head tag and then i'm also going to open up the body tag so i'm going to assume that this function always returns the complete head section and we might as well open the body tag at the same time so that will generate the uh complete string all i've got to do now is say get htm sorry get head html equals str now to make sure that it's generated the correct sensible looking html code i want to quickly test the function using the immediate window so i'm going to the view menu and choose a media window i'm going to say question mark and get head html when i hit enter this should print out what looks very much like the head section of a basic html document and that looks reasonably good so going with that what we can do now is incorporate this function into our main subroutine to start with let's get rid of the inline style from that paragraph tag we won't need that anymore because that's what our style section in the head of the email will do then let's concatenate the result of our get head html function so let's say get html and our opening paragraph dearsome1 then we'll get the result of the movie data function or get movie data function which will do its same job as last time then we'll join on the signature essentially which is what the html body property will give us and finally i suppose technically we should close the body tag as well as we open the body tag in our get head html function we should also make sure that we close the body tag at the end now if you know a little bit about html you're probably wincing at this point in in thought of the horrible mangled mess of html we'll get here i'm gonna come back and mention that in just a moment just for them just for the time being we're going to run this subject in just to prove that the head section the styles in the head that we've added do affect the rest of the email so again it doesn't look particularly beautiful because i haven't spent much time choosing specific formats for various things but with a little bit of knowledge of html and understanding that you can add styles to the head section of the email you could end up with some neatly formatted things i'm not going to go any further in terms of adding more styles because first of all it's not my forte i'm not very good at formatting in the first place and i probably don't know quite enough about html myself um i'll leave that up to you guys what i do want to think about just briefly is how we can make sure that the html that we're generating in this email is actually sensible html okay so this is really just meant as a fairly quick note for the html purists amongst you just to explain what happens when you concatenate all these bits of text together to generate the email what i'm going to do is i want to print out the result of the html body property after i finish generating it so i'm going to use a debug.print statement i'm going to say html body so that'll print that into the media window the first time i do this i want to avoid concatenating the original html body text just that we get what we ourselves have generated this is purely composed of the two functions and that greeting line what we'll do then is run the 17 and you'll see that we get a basic email it doesn't incorporate the signature because of course we haven't concatenated that to the end but that's okay what i'd like to do now is view the immediate window to see what we've got so you'll see that it starts with exactly what we typed in ourselves earlier on in the get head html function and it also ends at the very very end there with the close table tag so weirdly although we didn't include the closed body tag this html still works to generate the correct email technically speaking we should have a closed body tag here and in fact technically speaking there should be all sorts of other tags associated with this email as well technically the whole thing should be wrapped up in an html tag to make this work and we should have a close html tag right at the end so hopefully what you can get from this already is the fact that outlook is fairly forgiving in terms of generating the correct document even though the html isn't 100 complete and that goes even more for when we do incorporate what the uh the original body text includes i'm just going to clear the immediate window i'm going to press ctrl a to select everything and then delete to clear it out then just close it down temporarily i'm going to re i'm going to bring back in this um the original html body and the closed body tag and then i'm going to run the subroutine again and again recognize that you get a complete email that's displayed properly that we could happily just send and we get the signature text at the bottom all looking sensible but the html itself looks absolutely awful if i view the immediate window the immediate window itself actually isn't even big enough to display all of the information in the html of the uh the email there is a limit to how much text can be displayed in the immediate window and we far far exceeded that so basically everything from the bottom of the window here this is hopefully you can see the uh the signature text of our email scrolling back up this is all of the other stuff that's associated with the original html body property of that email so there's all sorts of styles and classes and all sorts of other declarations in fact i haven't got enough space to display everything else but we have we can't even see the text that we've generated for the email so this part here and this part here actually isn't even included because the immediate windows in window isn't large enough to display it what we can hopefully see if i can pick it out quickly is the fact that in this section here can you see we've got a closed style tag and a closed head tag and another open body tag well technically we've already done that haven't we in our function earlier on this definitely this closed style and close head tag here definitely is not part of our function text this is part of the original html body of the email so our email actually consists of at least two head tags at least two style tags at least two body tags but even though all those discrepancies are part of the html outlook happily displays a sensibly formatted email basically what i'm trying to say here is even though if you're a purist this is absolutely horrible you can relax you can rely on the fact that outlook will generate a sensibly formatted email even if the html looks absolutely awful we could spend absolutely ages generating a new function that takes the original html body and finds just the the signature text in there to make sure we only get the correct part of the html text that we want but it's irrelevant you don't need to do that outlook takes care of it for you a little later in the video i'm going to show you an alternative way to insert signature text into an email which will hopefully make you a little bit more relaxed about this if this is bothering you but for now we're just going to accept the fact that out looks pretty pretty forgiving when it comes to horribly formatted html so i'm going to take the debug.print statement away and that will give us our nicely formatted html email so we've dealt with basic plain text emails and now html emails as well and hopefully you have a reasonably good idea as to how those work what we haven't looked at yet are the rich text format emails and i've been deliberately saving those until towards the end of the video because they're a little bit more difficult to work with than you might expect so what i'm going to do is make a copy of this subroutine that we've just been using and then create another new module so i'm going to insert a new module and yeah i bet you can guess what i'm going to call it using outlook parts and then i'm going to paste that subroutine into the module and change a couple of basic things to make this work as a rich text format emails first of all first of all i'm going to change its name so i'm going to send complex email rtf then i'm going to change the format or the body format of the email to an ol rich text format and then we're going to remove this line here all together we're not going to change the html body at all what we're going to do first is try to change the rtf body property and i think i showed you this earlier in the video if i try to change the rtf body to be equal to let's put it on my standard greeting dear someone then this will absolutely fail if i run it we'll get the empty email but it doesn't fill in any of the values and that's the reason for that is because it's crashed with a runtime error at the point where we try to set the rich text format body property now the reason this fails is because the rtf body property doesn't contain a string of text what it actually contains is an array of bytes i'm going to come back to that idea in just a moment what we're going to do first so rather than try to set that property we're going to try to just read it simply so i'm going to add a debug.print statement debug.print and i'm going to try to print dot rtf body so i'm going to display the media window first of all which is empty currently and then i'm going to run this subroutine and see what we end up with so there's the email that we generate i'm going to close that down and the rtf body of that email looks like a complete list of absolute nonsense there's absolutely no useful information there whatsoever so what we need to do in order to see anything useful at all in the first place is to convert that array of bytes into a sensible string and there's actually a function that will do that for us in vba it's called str conf string convert or string conversion so if i open a set of parentheses the first thing you specify is what string you're trying to convert and then the second parameter says what you're trying to convert it to so i'm going to try to convert this array of bytes into basic unicode text so i'm going to use vb unicode and close the parentheses and then if i run the subroutine again i'll get an email and i can close that down and then i can look in the immediate window and i get although as well as a list of nonsense i do actually get some intelligible bits of text now as well so this is basically what rtf text looks like is it's a set of encoding that dictates how to display the text as well as the text itself so this is what makes it so difficult to actually set the values of the rich text format property um of the rich text format body property but we can make a stab at this what we're going to try to do i'm just get rid of the immediate window here after i've cleared it out what we're going to try to do is just add a basic greeting to this simple email we're gonna try to put in the dear someone greeting so to do that we need to do effectively the reverse of what we've done here we need to encode the phrase dear someone as an array of bytes and we're going to saw the result of that conversion in an array which we're going to declare at the top of the subroutine so let's say dim str open and close brackets as a byte now you don't need to know too much about arrays in general to make this system work but we do have a video that explains arrays in a huge amount of detail if you're interested that's earlier in the series what we're going to do here however is simply convert storing that array str open enclose brackets equals scr-conf open parentheses and what we're trying to convert is the phrase dear someone and we're trying to convert that from unicode this time it's going to use vb from unicode so once we've done that we can then set the rtf body property to be equal to the array of bytes that is stored in that array so let's give this one a quick test if we run it we should find that we can successfully set the rtf body property to the phrase dear someone of course we've lost the uh the signature text um there's not much we can really do about that at this point but at least we can successfully set some text in the uh in the email but that's a huge amount of effort to do and it's very very awkward to do things much more complex than this with rtf format emails what would be a much much better solution would be to rather than write out explicitly all of the text into the rtf body is to use the editor that is used to generate these emails so this is kind of the point we've been leading up to in the whole video rather than explicitly writing out text into the email using the body or html body or rtf body property we can actually get access to the microsoft word editor which makes life an awful lot easier now the approach to using microsoft word to edit the content of your email is a little bit different to what we've done so far but the basic structure is the same so what i'm going to do is copy this everything that we've written already and paste it in down below and of course i'm going to need to change the name of the subroutine so i'm going to call it send complex email in fact i'm going to just say send email uh using word using word editor actually technically so we can remove any reference to an array so we can get rid of that all together we don't need to do any sort of conversions from strings to arrays and vice versa so we can get rid of all that as well tidy up a little bit what we do need to do is declare a couple of extra variables and the first variable we're going to declare is something called o l insp which is short for the word inspector and the type of object we're going to store in there is a reference to an outlook dot inspector and inspector is essentially the the tool that lets you view and and edit an email just like the window that you use to edit the email itself the inspector object then gives us access to something called the word editor and the word editor of an inspector actually returns a reference to a microsoft word document so what i would like to do next is say dim wd doc as a word dot document but as you can see i don't i don't have the ability to do that at this point the intellisense list shows me no reference to microsoft word or to a document object so as you've probably guessed already what we need to do is head to the tools menu and choose references and if you followed the video on microsoft word prior to this one in the series you'll be very familiar with how this one works we're going to look for the microsoft word object library see if i can avoid scrolling past it as i usually do no happen again go back up a little bit there we go microsoft word 15 in my case but whichever version number you have is the one to go for of course click ok and then i can say dim word doc as word dot document okay the next thing we need to do is get references to both of those objects in our with statement i'm going to do this after we've set the basic properties of the email so we're going to say first of all set ol insp equal to and then there's a property of the mail item object that gets the inspector so i can say dot get inspector nice and simple now that i've got that stored in a variable i can set a reference to the word document by saying set wd doc equals ol insp dot and there's a property of the inspector object which is called word editor and that returns a reference to the word document now the beautiful thing about this is that once we've got a reference to the word document we have access to all the methods and properties you'd expect to have from microsoft word so this is where things get really interesting and hopefully a lot more useful than building an email bit by bit by bit using the body property so just to give you an idea of some of the basic things we can do here and hopefully how easy it is to do let's start by adding the basic greeting to our email the generic idea someone greeting what we need to do is reference our word document so we say wd dot dot then we need to reference the range method of that document range allows you to specify a specific range of characters essentially of the document that there's two optional parameters start and end we don't actually want to reference any specific character we're just referencing the entire range of the the complete document because what we're going to do is insert a line before the current range we're going to say dot range dot insert before now the insert before method has a single compulsory parameter text as string so what i'm going to do here is say dear someone and then i'm going to concatenate a couple of vb new line characters as well so nvp new line and vb new line okay so once i've done all of that what i can do is run the subroutine and i'll end up with my email with phrase dear simone a couple of line breaks and then the signature as it stands it's worthwhile mention that this works as well as rich text it will also work happily with html as well and i suppose probably with with plain text but certainly with html like so and let me just give this one a quick try with uh with plain text i haven't actually tested this with plain text let's have a quick look so yeah it does of course um the disadvantages using plain text of course and the reason we want to use word as the editor in the first place is so that we get access to all the lovely formatting features so let's revert to a rich text um formats there okay so there's the basic line of text inserted the greeting what i'd like to do now is put in our table of data from excel now previously we've had to do this cell by cell essentially and read these values into the email using the body property the beauty of using word is that we can do this as a simple copy and paste so that's exactly what we're going to do so back to the vb editor and start with let's say sheet 1 dot activate and then we can say arrange a1 dot current region dot copy so that copies it all to the clipboard now we do have to be a little bit careful with how we paste this into the document let's just have a quick look at how you can do this to start with i'll need to reference the word document of course so i'll say wd doc dot then i need to reference the range method and again i'm going to avoid using the optional parameters at this point i'm just going to say range dot paste now if you've seen the previous video we've created on microsoft word you'll be familiar with all the various methods of pasting things into the word documents there's even a dedicated one for an excel table but for this example i'm just going to go with the basic paste method so having done that i'm going to run the subroutine and see what we end up with there we go we end up with essentially an email that consists of just that table you might have seen before the table got pasted in you might have just quickly spotted the dear someone greeting and the the signature text but because we've referenced the entire range of the document the table gets pasted in over the entire range so this is where these optional parameters come into play if i open up some round brackets after the word range i'm going to specify a start position for pasting in this table and um just for the sake of demonstration just as a very quick and simple example what i'm going to do is i'm going to paste this starting up position number five in the range of the document so um position number five essentially represents this character here the word deer contains four characters and then the fifth character is the space so um it doesn't make sense to do this in the real world obviously but this is just for the sake of demonstration it's a nice easy way to show you how this works as we know that this is the starting point of our entire document character number five of the document will be that position there so if i run the subroutine at this point what we will see is hopefully the word dear and then the entire table that gets pasted in and we've lost the signature again because essentially what we've done here is we've said from character position number five replace that with whatever's being pasted from the clipboard so ideally what we want to do we want to insert the table in at a specific position we also need to specify the end of the range we're pasting into so if i actually just use the same number there the number five essentially our table gets pasted in at that point there so if i run this one one more time we will see the word here and then our table and then you just make out there the word someone and then the signature at the bottom okay so that's um still not perfect obviously is it what we really want to do is make sure that our table gets pasted in after whatever greeting we've added so let's do that bit next ideally we'd like this system to work so that regardless of the length of the greeting that's added to the email the table always gets pasted in immediately after it so the easiest way to do that is to actually store the greeting text in a variable so what we're going to do is say dim str greeting as a string and then we're going to say str greeting equals and then i'm just going to copy this string of text in fact i'm only going to use one new line character here let's just copy a single line character and paste that in there then whenever we're inserting that greeting we don't need to reference or explicitly write that out again we can just reference the str greeting variable now that we've done that what we can do is modify the way the range method works so rather than inserting a specific position what we can do instead is calculate the length of the text stored in that string greeting variable to do that we can use the len function so i'm going to say len open brackets str greeting and then close some brackets and then again do the same thing here len str greeting and i guess we could have stored the result of that in another variable as well as we're referencing it more than once but this will just demonstrate the principle fairly fairly neatly so having done all that i'm just going to run the subreddit in one more time and we ought to get our greeting dear someone with a new line character then the table and then our signature right at the bottom so i hope you're in agreement with me i find this by far and away the most convenient way to generate emails referencing the word editor the beauty of it is because you're just copying and pasting stuff from excel it retains all the standard formatting from excel you haven't got to worry about where to put in new line characters or tab characters or any of that sort of uh sort of stuff so those are several different methods for generating emails i think that's almost enough for one video there's possibly just one more thing i'm going to mention now we've mentioned the idea of using signatures in the email several times so far we've made sure that we always include the default signature at the bottom what we haven't dealt with yet is how you choose between different signatures outlook supports having multiple signatures available for any email and there's always a default one which in my case is my work signature but i've also got a personal signature as well which actually i've just created for the sake of demonstration it's very simple it just says cheers andy and then i can quickly change between those two using the signature tool in the email what would be lovely is if i could add a use a simple method or a property to switch between the signatures that i'm using when the email's been generated but unfortunately i can't do that there is no simple property or method of a mail item that lets me do that what i have to do instead is open up the file that contains the signature text and read that into the email object so you'll need to know a little bit about how the signature files get saved if i just quickly take you to a windows explorer window i've already browsed the correct path for my signatures this will be something similar on your own computers of course depending on which version of windows you're using and what your username is but you can see that i've got for each different signature i've got three different files and you can probably work out why there are three different files i've got a txt file for plain text emails an rtf file for rich text format and an htm file for html emails so what we would have to do to make this system work is make sure that as well as being able to choose the correct signature personal or work in this case we'd also have to choose the correct file type for the version or the format of the email we're creating so let's start by going back to the vb editor and i'm going to go back to a very very basic version of our email code i'm going back to the original using outlook module and this is where we ended up with our first basic emails we'll modify this one to um to create the email and choose the correct signature i'm going to start by tidying up a little bit in this subroutine i'm going to get rid of the send method that we added in for demonstration earlier and i'm also going to get rid of the bit that adds on or concatenates the current html body so that's how we added the signature earlier we're going to do this in a completely different way this time the next thing is i need to be able to talk to files and folders because we need to actually open up one of the signature files to release contents and the best way to do that is to rely on an object library called the microsoft scripting runtime so we head to the tools menu and use references and then scroll through the list to find the microsoft scripting runtime now you may have seen the video that we created earlier in the series on how to use this so we're not going to go into too much detail in this particular video just to show you that you can reference it by ticking the box and clicking ok and then we can use it to declare a couple of extra variables i'm going to call the first one fso as scripting dot file system object which is essentially like an application that allows you to modify and manipulate files and folders and then we're gonna also have something called ts as a scripting dot text stream we'll also have a basic string variable for storing this the path of the the signature file we want to use i'm going to call this one sig path as string and then we want to store the path of the signature file in that variable so i'm going to say sig path equals now i could i suppose just use the specific file and folder path that i showed you earlier on so that would be specific to me as a user but i think it makes more sense to use the environment function to generate part of this path and then tag on the extra part ourselves so what we will do is just quickly show you the the version of the environment function we're going to use in the immediate window we can use environ as you've seen several times in previous videos this time i'm going to use the app data folder so if i search for that you can see that it gives you everything up to the um the folder roaming in this particular version of windows so we need to tag on the remaining part of that which will be microsoft signatures so what we will do is sig path equals environ app data ampersand and then backslash microsoft oops microsoft spell that properly backslash signatures backslash and then to begin with what we're going to do is use the let's use the personal signature so i'm going to say say personal dot and the important thing here is that we use the correct file type for the type of email that we're creating so in this email we're using uh html formatting so i'm going to use the htm signature or extension okay so that will store all the correct values what we now need to do is actually open up that file and read its contents somewhere so to do that we need to create a new instance of a file system object so we can do that in exactly the same way that we create a new instance of outlook let's say set fso equals new scripting dot file system object we can then set a reference to the text file that we're opening in the text stream or the ts variable by saying set ts equals fso dot open text file if i open a set of brackets there set a parentheses it shows me that there are several parameters i can fill in the main one the compulsory one is file name so that's going to be the file name and folder path stored in sig path i can also choose what input output mode i use by default it's open for reading which is actually the one that i want so that's actually all i'm going to do i'm going to pass in just the file name and that will open the file into memory now what i need to do is read the content of that into somewhere else i haven't actually created the variable sorry created the email at this point yet so what i'm going to do here is declare another variable which is going to be called sig text as string and what i'm going to do is i'm going to read the entire contents of that text file into the sig text variable so i'm going to say sig text equals ts.read all so that reads the entire contents and stores it in that variable what i can now do is close down the text file by saying ts.close and then i can say set fso equals nothing just to tidy up a little bit so i can release release the variable as soon as i finish using it and now all i need to do is down towards the bottom of this ability now where we're generating the html body text i simply need to say ampersand sig text so that will put in the content of what i read out from the file earlier on so just to give this one a quick test if i run it just to see if it will work with an html document we should end up with the personal signature i could do another couple of line breaks there couldn't i but if i close that one down don't bother saving the changes let's add in another br and let's change it to the work version so change it with the word personal to the word work and run it this time we ought to end up with the work signature instead so that's the basic principle although it's a bit rough and ready at this point having added all the code to the same subroutine what might make a better solution here is actually creating a separate function will always return the correct signature text let's start by declaring a new function at the bottom of the the module so i'm going to call this one function get sig text and that's going to return a string as in the past so we've done that now we can take essentially all of the extra code that we added to this basic subroutine we can essentially just cut the whole section of code from where we declare the variable for a new scripting file system object all the way down to where we set that variable to be nothing we can cut that from the subreddit scene and simply paste it in to the new function okay so the next step is then to provide a couple of parameters for the function that lets the user choose first of all what type of signature they want whether it's html or rich text etc and then also which actual signature they want whether it's work or personal or perhaps something else the first parameter that we'll add will let the user choose what type of email they're creating or what type of signature they want so we've got a specific list of those types already haven't we we've used them several times in the previous subroutines where we're setting the body format we have a specific list of options available what i'd like to do for my function is provide exactly the same list of options as the body format property so that involves finding out which collection which enumeration that particular constant belongs to you can find that out using the object browser if i view object browser from the menu and then i'm going to limit my list to just those options that are part of the outlook library and then in the right hand side i'm going to scroll down the list to find that option i was just looking at ol format html that's a good starting point so scroll down the huge long list of various options these are all the constants that are part of outlook and there's one that i want old format html if i select it and look down at the bottom part of this window it tells me that this is a constant we've mentioned that before but it's also a member of something called an enumeration and this is outlook.old body format if i click ol body format that will take me to this specific list and again this is just mention the idea this is an enumeration and enumeration is just a list of constants we've created a video on constants and enumerations previously in the series um so again not going into too much detail here all we want to do is make sure that the parameter that we create for our function allows the user to choose from one of these old body format constants so let's get back to the vb editor let's say well sorry the code let's close down the object browser and for the first parameter we're going to call this one what do we call it let's call it um sig type as ol body format now what this means is when i call on my function in the main subroutine so we're going to call on this function here where we're adding the signature text we're going to say and get sig text and then we'll open up a set of round brackets and that will give us the list of options available from that enumeration which is quite neat what i have to do now is take the value that's passed in through the sick type parameter and convert it into the correct extension text either dot html dot txt or dot rtf so to do that i'm going to declare another variable here i'm going to call this one sig ext short for extension as a string and then i'm going to use we could use a case statement or an if statement let's use a case statement i'm going to say select case sig type and then i can say end select down at the bottom and inside there i can say case and i'm going to use the enumerated values again so i'm going to go for ol format format html and when that's true i'm going to say that the sig ext equals dot htm and then the other two possibilities again will work in a similar way so i can say case ol format rtf or rich text sorry and then the sig ext will be equal to dot rtf and then finally we've got case ol format plane and for that i can say sig ext equals dot txt okay so that will generate the correct extension name what i can do then is replace that part of my signature path with a concatenation so i can say amps and sig e x t to put on the correct extension the next thing that we can do is provide another parameter in the function that lets us specify which signature file we want to use so we'll just use a basic string parameter to do this let's call it sig name as string and then what we would do is in the line which calculates the signature path we can remove the word work there and then simply concatenate in whatever the value of the sig name parameter is i've got my amazon sorry i thought i'd type that in there we go so ampersand sig name ampersand sig x so that means that when i call this function in the other subroutine i'm going to have to specify which particular signature i want to use so i could put in work what i prefer to do however because i'm using work primarily for my emails what i'd like to do is give this parameter a default value and make it optional so to do that i can type in the word optional in front of the parameter name and then after the words as string i can say equals work so that means now that when i call this function i don't always need to specify what the um what the signature name will be if i type in a comma you can see that it says in a set of square brackets the parameter name is optional and has a default value of work so i'd only ever need to provide a value if i wanted to use the personal signature so one more thing that i need to change about this function before it will actually work is to make sure it returns a value because currently it doesn't right at the end i'm going to say get sig text equals so we've got get sig text equals um the result of this signature text variable so that's the variable into which we read the contents of the text stream so i'm just going to say sig text and that will return everything to the function so let's give this one a quick test at this point if we go to the subroutine and run that one we should see that we get an html email with the work signature let's close that down don't bother saving the changes and let's try adding in the personal signature this time i'm going to use personal of course this relies on us knowing the names of all of our signature files which might be a a point of uh of issue if we don't know the names or we'll be typing the name incorrectly but let's run this everything just assuming that we've got this correct and there we go we've got the personal signature in there as well so that establishes that it works for an html email what if we tried to change the type to say plain text i'm going to make a copy of this entire subroutine and paste it in below the function and then we're going to make a few modifications to make it work as a plain text email so that's put in as a an actual keyword in the subject name as plain so we're going to modify the format of the email first of all so it's not going to be an html format it's going to be a plain format and that means of course it will need to modify first of all we're running uh concatenating the break tags we need to replace that with a vba vb new line or vbc rlf and also where we're getting this in signature text we don't want to get the html file we want to get the plane file so we do this in two ways we could actually we could say explicitly a well format plane or it actually makes sense really to actually call on the current body format of the email so ignore the intellisense list what we can do there is say dot body format so it will read whatever we've set that means we only need to change it in one place i'm going to revert to using the work um work uh signature as well so i'm going to remove personal from the call to the function and then let's also modify where we're changing the html body just to the generic body property right so having made all those changes let's try and run this one and see what happens oh yeah that's not very successful so we get the um a slightly weird version of the um of the the signature text from the from the text file that's because there's one extra property or one extra parameter of the open text file method that we haven't set yet so let's close down this email and that's going to resolve that problem with the open text file method the parameter that we're interested in is actually the fourth one in the list for the opentextfile method it's one called format as tristate and unsurprisingly this controls the format that the file is opened in now you can see it has a default value of tri-state false and what this means according to microsoft literature is that this file opens up as in an ascii format we could use tri-state true to change it to a unicode format and that would be perfect for plain text but that would actually mess up the html version of the signature so what we're going to do instead we're going to choose tri-state mixed and that will allow the file to open up in the correct format for whichever type of file we're opening so having done that if i run the send basic email plane and check that that works we ought to get a sensible format for the um for the signature file when we do and then if i close that one down without saving the changes and go back to the html version and make sure that that still works we should get a sensibly formatted um signature for that as well let's just make sure that it works for both both signature files so rather than personal we'll use the work signature for html just make sure that still works and eventually there we go we get the correct results so there's a reasonably useful function for generating the correct signature text for your emails depending on which type of email format you're using and which signature file you want to use now previously i mentioned this function is reliant on us always passing in a correct name for the signature file that we want to use so if i try to call the function and opacity in the name of a signature file which didn't exist so i don't know let's let's misspell the word work for instance um and try to open up that signature file if i run this subtitle now although to begin with it will appear as though the sub scenes worked you get the signature file in place however closer inspection reveals that you haven't filled in the to field or the subject or written out the body text etc so if i close the email down without saving the changes we'll see that the um the subroutine has failed with a runtime error and if i click debug it's failed on the line which opens up the text file because that path doesn't actually exist so what we could do is actually test to see if the signature path that we create exists using a method of the file system object to do that after we've generated the new instance of the file system object we can use an if statement to check if that signature path actually points to a file or not and if it doesn't then we can set the signature path to be equal to a known file path so we're going to say if not fso.file exists and we're going to pass in the signature path then don't forget end if so that essentially says if the um if the signature path doesn't point to an existing file then we're going to reset this to be pointing to a known signature file so i'm just going to copy and paste that line and then take out the bits that concatenate the signature name which we know at that point doesn't doesn't exist and instead replace it with a known file name so that's gonna be the work file so that means that whenever we try to call this one and we search for or try to pass in uh nonsense or at least the name of a file which doesn't exist then it will just revert to using the standard work signature so there we go that's a slightly more robust version of that function if you've enjoyed this training video you can find many more online training resources at www.wisel.co.uk

Channel: WiseOwlTutorials

Views: 257,397

Keywords: Visual Basic For Applications, Microsoft Excel (Software), Microsoft Outlook (Software), email, object library, wise owl, vba, outlook, createitem, olmailitem

Id: f8s-jY9y220

Length: 99min 7sec (5947 seconds)

Published: Wed Mar 26 2014