Send Recipient Name with airSlate SignNow

Send recipient name

hey guys welcome to the video number 417 my name is Ozzie and in this video today we are going to talk about how to sync the Outlook email or when you are basically recipients name you know the main which you have defined you know in your Excel in the form of drop-down or maybe in column it gets changed right so automatically the email should trigger and your you know emails will go from your Outlook so everything has to be done in action but if the change is happening next and then the email should go now this basically I'm going to record this video as usual you know I received an email from mr. Buller I think he is written actually emailed this you know on the 2nd of June and as I said that guys I was actually not uploading any videos from the last two weeks and this happened for the first time then I took such a long break so it's just that my basically the laptop wasn't working and I was also busy in some other you know the external project so I couldn't get the time but I'm back now and I'm going to answer your all critics one by one so mr. Buller thank you so much for surfers writing but I would really appreciate if we can also share these your questions are directly on my youtube channel just post your comments there so then everybody can see that right so what actually he said is hello how can we automatically send an email on the name of the employee makes it changes right now I really didn't get much of the detail I mean you didn't even share any sub short or kind of you know sort of stuff that how basically where Excel looks like so I'm going to assume that somewhere here maybe you know you're trying to create a drop-down let's say a way you have all the Nemo you know so for example if I just guys create here let's say this email id or maybe an e let's say that's my name super mark and let's say this is one morning we can add let's say Sharon Cheryl s and then let's say Robin Robin K and then gives let's say wing kit then click the star and then let's say mehboob allah so let's say these are the names now what we are going to do is I'm going to simply go ahead and create the drop down here so I go to the data and in the data validation guys I just could you know create this so I'm trying to you know record this video from the very scratch so that you should also have your idea it does help to how you can actually create this kind of fun you know some readable dashboard it's all of that so now you have all the names coming here right so what you can do is maybe you can go in the insert and you can put some kind of an arrow here you can create an arrow for example maybe sort of form yeah that's that that's the kind of a little arrow you can create here right and then further you can just go in the Home tab and you can just put this in the middle and in the center and I can change the point of it I can make this Arial black and you can go in the view underneath the grid line so once you have that it maybe if you want to just give it some color sort of this color you can do that right so that's how it is and so this is how basically now the user you know it's going to walk he's going to change these names the moment the link gets seems you want that the image should go and the message box should come right no great our photos or to this you can you know write your that who should be kept in the - and then you know CC and then in the BCC and subject also you can right here and you can also create the body and that action so whatever you want to do you can do that so I simply go and I see that if it is going to be let's say you know this discourse involving teen then the emo should go to the Robin King so maybe if you have any generic idea you can do that as well for example you can write here that team team at irate maybe let's say Oh calm the city of the team or cook add in the CC or you would like to keep yourself right so what you can do is um you can further go and you can create their email ids for these people you know suppose you can create their images here selected this is AP a dorito outlook.com and further then you can right here share so here you can write a share at the range outlook.com and for the wrong minute Robin at the rate outlook and therefore we beat VP at the rate of lucam and for this image AVL whatever right so what you can do is you can simply go and put the vlookup here so that whatever the name the user is going to select you know it should come from this table so I'm just going to select this table and second then the zero right that's all and then then you can actually you know go ahead and hide that only you can give it a white form so that users should not be able to see anything here right so that's all the email would come and let me close this I don't know how it got opened anyways so that's how your this is how the table looks like and I'm just going to actually zoom in a little yeah that's how it is and then what is the next thing you want to do one in the BCC of course you can write whatever you want so I just write a team let's say at outlook or maybe something else let tech team put two in after days of calm right so I'm trying to actually just you know put all the things in proper order so that once we make that code we should pick up you know everything so subject has to be whatever the user subject you know he's going to write here she's going to write this at the baby - water it's a daily dashboard and you can combine that with the data as well so you can write here create whatever the date you have and just guys write equals two here and put it within the quotes so daily - would you know and you can you will have the date here right now the date is not coming in August a big estate this is four to eight nine which is coming in the numbers we can write you can add this with the text format and you can write here like a mm DD and then M right that's it guys okay so that's how it is and you can create the space here is word like this okay so that's it daily - for cutting June and the body whatever you want to write right let's I write hi you can write here hi and then this is this is this is for your information let's see that's it and in the attachment or you can add whatever - well you want so suppose I'm just going to picked up maybe this text fine let's say I go to the properties of this and you just need to go to the security and pick up this complete part right so we're going to put that in your excel sheet guys over here there we go right now I think we have got all the information with us and that's how it's going to look like and users so what the user is going to do user is always going to actually see this information and edit this information anytime you know if the user wants to edit this information as simple as that right so that's how it is and if I just go and change any name for example I change this to a day per man you see that the email is going to change and similarly guys you can change the entire stuff as well it's not just that you know this particularly CC part you need to change you what you can do is you can further go ahead and create more columns so if I just made this black you know what you can do is after this email you know if if they have their own specific managers then you can write that in the C column as well right so you can write here for example or in for the case of the Aussie it has to be a different lien for example it has to be Dean one at Al Kooper right and similarly the subject has to be different so I mean you can write all these things yourself right so if I just go and let I copy this copy this copy this it is and if I just go let's say this is 14 11 and this is going to be my let's 1891 and this is going to be my team underscore s 1 and this is going to be team 1 for example that's how it is right so you can further go ahead and use the squeeze box we can again use the vlookup you can pick up this item from the top down the user is going to choose and then you can just you know use the third column so this will also change automatically and then of course as usual you can hide these columns right so now when the user is going to pick up any name from here this will also change and this will also change and similarly the body attachment you know whatever you want to do you can do that right I will not get into that because this video is more on the output part so now what is the how we're going to do this so basically here we have the two challenges one is how to send an email by the way if you are watching this I mean if you haven't see my the rest of the videos then what you can do is you can simply go to the channel and you can watch all those amazing four hundred and you know fifteen sixteen videos and that these all the 45 categories the playlist right now this is not a new thing which I am doing right if you're following the channel then you know what I'm saying guys we have already I think alluded 2526 videos on you know this output part so I just show you the in this my on this at the playlist on the front page there is a playlist called excel vba connecting outlook with excel let me show you that um it must be somewhere here okay I can't see that conditional formatting d4 so basically these are all the playlists which I have created and you just need to go there and you know you need to you can see those of the particular videos if you're interested in watching index which is a very powerful function and very much you seen some reports - quotes you can go and check out these four videos right now that this is what I was talking about so in the excel will be connecting outlook we already have 34 amazing videos on this every video is unique everybody is different right so we know that how to make sent the email but still if we do not know I will talk about that but the important point is how you're going to ensure that every time when the change happens here the email should automatically not treasure so what you need to do is you need to go to the visual basic and this is my sheet 3 so I just go and I click on that sheet 3 and because here we are going to write an event right now what are the events now even events are actually self written macros whenever the condition satisfies these are on themselves they don't wait for your button they don't wait wait for your instructions right now on this even's also I have actually a lot of videos starting from the scratch so what you need to do is you need to find it out here up there is called Excel VBA event right so if I just scroll it down and if I can find it out for you there we go so xov be events when you click there you will see 8 videos if you are a big nerd don't watch the videos randomly what do you need to do is just go by the title and there is there are some titles which says that the basics of the event right so go and first watch that right so even so nothing they are says figured Mackall so we're going to say that anytime the change happens in this e one cell okay if this if the seller this is e 1 e 1 and the change is happening just run the macro so what you will do is you will go into Visual Basic you double click on the sheet 3 because I am on the sheet tray as you can see and from here just choose that worksheet and a lot of events would come here if you just click here so you have a selection change you have a table update to remove pivot table update I never use that right so there are technically not a lot of things here so what you need to do is you need to basically use a sheet tint even this one change use this and I just go and delete this one this is not required so what do you mean you made a change so change means at any time the value changes in excel in this particular sheet 3 because I'm writing this coding sheet 3 so this is not going to work in any other shape guys right and within this sheet also we have a condition and the condition is that the change should happen in even cell only it will not happen anywhere so what you're going to do is it's simply going to see that if the target address if my target address is going to be equals to your send which is e1 so that's how you write a dollar 1 and just to fix the dollar here as well if that is the cell address and what you need to do is then just call the you know macro which is which we are going to create so put it within the double quotes and just call the macro box and that's it else else you don't have to do anything else else just go in the end if and stop the macro okay so now I go and you know insert the module and here I'm going to create a mask upon change give me that's all you're going to create the macro so here because we are going to work with the output part we are going to connect this excel with the output users go to the tools and the reference and is ensure that you should actually have the Microsoft Outlook library also just scroll it down and these are all alphabetically sorted libraries so I'm just going to see that where is the oh so they will go Microsoft Outlook yeah to somewhere here it has to be there so I'm using the 16 2016 so you see 16.0 make sure you come back and see that once you check mark that it should be there right so this is the end it's coming perfect celebrity is on and I'm going to make the quote so this is always we're going to mean the boredom or at our group dot application that oh it was too new output product edition now this line what it does it creates the object in the VBA memory which is going to be now considered as outlook as so so that you can use the outlook classes and further in the other classes if you want to use their classes methods of properties you can use that right so because we want to send the emails unbroke read one more variable only as of loop dot mail item and then set only equals to this all dot know you do you see that o is created as an outlook object it's not a you know any any other normal variable it's a proper object right you can even write anything here it's not mandated to write oh right if you have watched my previous study tree 3034 videos you know what I'm talking about right so this is just the object which we created and now I'm going to actually create the item them you know we're going to use the mail item class now what is the mail item class so guys whenever you deal with the email to use this class like in Excel VBA when we talk about the Excel VBA we have a excel objects like we have a excel application and then in that application we have a book on sections and worksheet object and then the worksheet we have the cell which is again as an object you know so similarly guys when we talk about the output VBA they also have their own object hierarchy right and they have a they also have their own classes if you want to work with the folders they have given you a class core namespace there is a another class called for the outlook dot folder if you want to work with attachments they have given you outlook talk attachment class like similarly when you want to send emails or when you want to actually you know maybe you want to transfer the emails from one folder another folder so in that case also because you are dealing with the emails you have to use this one may like in class right so we have created this now what I'm going to do is I'm simply going to say that this Oh mail that's what I'm going to write and with and we are going to send that email right so how we going to send the email obviously in the - I will write other cells so the cells are in the - I am going to write it till the III so on so maybe you can write simply here range a three top value right and then similarly for C you can write range click Add right efore dot value and then draw a CC can write range e v dot value and then you can write dot subject what is my subject subject is whatever we have here in the e6 and you know that these cells which I'm referring right now you can use the vlookup in every cell and they're going to change right so they will automatically change once the change happens in the top down so that's how it is so let me just quickly finish this off and there is all a lot of things I want to do it that's when you want to advertisement this is how you write what is the attachment you need to provide the path and path is already there in your range e8 that's how it is right dot value and then you're going to write your display let's just write the display board here there we go so now let us go ahead and let us see if we are able to do that I'm just going to put the brake code here remember this worksheet change will walk whenever the target address is going to be each one otherwise suppose if I come here and I see that I do some change right of the cell is empty I write it seven I press Enter look at this guy's so automatically the macro is running it's not waiting for your pattern right know where I clicked on the button I just entered some value an automatical micro is drawing now because I put this brake code that's why you see this yellow mark because this is a step by step you know we are going to run it but if I just remove it you will not be able to even know that this some macro is running right now you don't know this is not the target it is because the target is address is eat when we get the change in the eater right so obviously this is going to be in that go in the elsif and so finish there's nothing which will happen right but if I do the change here for example I'm just going to make this to Robin kay now look at this everything changed in the two you have now team 91x Outlook and the CC you have Robinette Outlook and rest of the things also I mean you can change subject body as I was telling you but the important point is now what is the target data is the target address Ashley is e1 because you take the change in the e1 so this condition satisfies and automatically now it will go into then and send email now from here guys the email is going to be forwarded look at this now I'm going to press f8 again it takes a little time because the object is you know being created and there we go there we go finally you have the display you can use a dot send as well it's just want to send the email directly then you will not be able to see this display which and which is which is you know right now coming so let me just maximize this look at this guys this is how it is yukata you know all the two CCBC subject and in the attachment the file and then of course hi this is for your information that's it guys so if I just remove that pretty good from here now see what's going to happen I just remove this so the user is going to actually change this to eject or not for example like this look at this and now the email should have gone by now I think so yeah there we go right so you can see that the email is drafted right if you just go and use the Dotson method email would go directly so you can use this you can instead of this dot display you can use the tops and s well and then of course you can write the message box st. you know so that the user should be able to know that it's actually sent so if I just go and click on the rocking K you see that the maybe is going to be drafted it it's going to be sent in fact you will not be able to see anything there we go st. now if I go and open my outlook and if I go in my sent folder I will be able to see that email right so sit guys this is how you know you can create all that stuff let me just unhide these things so that we can if it's just a beginner in Excel or in VBA maybe you require this kind of a you know these columns I mean you should may be needing it so I'm just writing it so this is how you know I oughta mated everything with this drop-down and then in the visual basic code of course this is the code it can pause the video you can see this code this is a very simple code the important point was in the sheet 3 basically these are the three or four lines won't she change even that's what we created right so that's it for now guys I hope you enjoyed this and keep commenting on the channel and keep liking the videos and do subscribe to the channel to support me in the next video we are going to talk something more exciting thank you so much for watching thank you so much for your lovely support I'll see you so thank you bye bye