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.
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.
Posts Related:
- VBA to List all permissions for the file or folder in Google Drive Office
- SQL Server Queries Part 1 - Writing Basic Queries
- 19 New 3D Text Effect Free Download In PSD Files Sheri Sk Photoshop Tutorial
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.
