We want to supply links we want to give the user the ability to click links that we supply we also want to upload large applications but we can't do that in email if it gets beyond 10 15 or 20 megabytes it's too large to be emailed so what's the solution how do we send large files i'm going to answer that in this training and we're going to show you not only how to create that in the link automatically with just a few lines of code we're also going to teach you how to know when that link has been clicked and how many times a link has been clicked also we're going to show you some really amazing training on how to pull that data extract from all the links you have and automatically update the table we've got a linked data that's going to show all of our links and all of our short links our long links our short links our link id and the number of times.
It's been clicked we've got some customer lists and we're going to wrap that all up into a really cool email this particular training this screen itself is pretty basic there's not too much going on with this screen so i built it already for you you don't have to watch me format there's not too much going on in fact there's nothing in columns a and b nothing is hidden everything is uh here visible so it's a pretty simple training and it's pretty simple but the concepts that you're going to learn and the third party tools that you're going to learn are going to blow your mind because they've never been this easy to incorporate and it's really important we're solving some really fantastic problems here that we've had in the past how do we use multiple attachments if we're going to browse for multiple attachments how do we add those attachments into an email as an attach file.
And what if they're large how do we add that attachment as a link so customer can download it automatically i'm going to show you how to do that and of course how to get the data it's going to be a great training the best way to get these trainings is to subscribe to our channel i create these free absolutely free and always going to have an available workbook for you absolutely for free all you got to do is click the links down in the description best way to know about these trainings is to subscribe if you can click that link down below to subscribe and the notification icon bell i'm going to make sure that you get alerted each and every training there every single tuesday without fail i make sure i get those for you and if you do like these trainings and you love to learn scale my goal is to teach you not just excel but how to be successful with excel i.
Want to get you passive income and help you reach your own personal dreams with excel as a tool to do that just as i've been doing with my own applications selling them on the market for many years now i want to do that for you and that's just what i'm doing inside the mentorship program if your skills are up to par and you want to join us in the excel for freelancers mentorship program i'm going to show you exactly how to define your own applications how to design them how to develop them and finally how to deploy them on an internet so you can get passive income from your application and i'm going to do that all while building an incredible accounting application.
That's yours to keep of course during the program that mechanic program is going to be complete with inventory invoicing purchasing a full dashboard complete sharing in sync and email and automation and full user security so it's going to be great great application mentorship program myexcel mentor.com if you want to get in i'll have it open for a while so go ahead and click the link below alright let's get to this training i've got a ton to show you and we're going to walk you step by step through every single macro through every single feature it's going to be a great training we're going to use a third party tool called integra matte it's something that we've used in the past if you've seen my videos if not no problem i'm going to walk you step by step and.
I've done is i've created a unique list i want to know all the different customer types and that way i can have a drop down list and i also want that list to include all types notice that we have in this we have a data validation let's take a look at the data validation that i've created here inside the data validation data validation it's going to be called customer type it's a relatively simple data validation and it's based on this list here so let's take a look inside that and see just how we created that the first thing if you want to get a unique list obviously you can if you've got a large list the best way to do is through an advanced filter so if i just want an advanced.
Filter i can do data all right all i need to do is select the data here including the headers and all the data go in data to get your new list advanced and what i'm going to do is i'm going to i want to select the list range in this case of course it's going to be customer type so i'm going to select that and then i don't want any criteria in other words i only want a unique list so if there's no criteria i'm just going to click clear this and then i want to copy this to a new location i want a unique list in a unique location the best way to do that is just to select that so if we scroll over here let me scroll over here and go into the unique we don't need that.
Inside this all i need to do is select customer types that is where i want that destination to come on that copy to right there and i want unique records only so i'm going to click ok what that's going to do is going to bring it so if i let me delete that so you can see how it is then i'm going to do that one more time so you can see that otherwise it's too fast for it going to copy to another location and that location is going to be n2 and i want unique records only i'm going to click ok it's going to populate that list that's perfect i got my unique list but what i really want is i want that unique list to include all types so on this i simply put all types here now what i want to do is just simple link so it equals n3 and equals n4 so all i did was just link.
And then drag it down well that's great but now how do i get that now we notice that each one of these are links right so what i want is a dynamic named range based on only the existing files if the value is zero or nothing i don't want to include it so if we go into the formulas back into our name range the only named range we have the others are criteria and extract those are both created automatically when we run an advanced filter so we're going to tab over here we're going to see that that data only includes the actual data not the links how do we do that especially when we have formulas in there well again we can use count if in this case i'm going to use an.
Offset so our starting point is going to be in this case let me zoom in to that so you can see it our starting plane says o2 so that's going to be the first one where we've placed all types next we're going to combo we don't want to go any rows down over or any columns over in this case what we want to do is the number of rows what is the number of rows in this case we want to know the number of rows we're going to use count if and this one i only want to know those that do not include zeros so we're going to count the entire you could go down as far with your range as you want of course you can go 50. so basically we're going to count every single cell in that range that does not equal zero so this does not equal within quotes and the zero that's.
Going to get us and we only want a single column to return so in this case a single column that is our offset formula so we use the tab key go tab out and we use the shift tab to tap back in we do
That it's going to highlight that formula and then you're going to see the dancing ants around that so all we need to do is if we were to add one more here it would automatically populate here this is now the list that i want to use in cyber and emails and now speaking of that advanced filter we're going to run that advanced filter and run the macro and run the email market so why don't we just go over it so now i've got some criteria this is a fixed criteria fixed meaning the vba doesn't run this criteria this criteria is based on a formula if we set all types basically inside.This criteria i want it to show up blank but if they've selected something other than all types in that case let's say existing i want it to show that value so existing so how do we that's just a simple formula if in the email screen e4 it's equal to all types then show nothing otherwise show what's in e4 relatively and of course i only want to send it to those customers who have opted into emails if they know if they do not want email sent of course that's going to be just a different filter they're all yes few knows here so basically i want to give that these are the criteria so when.
I run my advanced filter i want to know who to send that email to so it's going to be all those customers so for example if my customer type was new when i ran this if we click emails and we click only new we only have a single customer that is new we go ahead and click send email it's going to run that macro we're going to go through step by step on that macro it's going to attach those files and if we see here in our customers we see that we have new and we see that single customer new that's the only customer and that customer of course has to be opted in so it's going to be only new customers that have opted in this case is just one okay great but if to take a look at this i also want to show you how to add files attached not just the single files we've done.
In the past but what about multiple files what if i want multiple files so let's say i want to send this file and this file and this file i'm going to hold down the control we've got a multi-select i'm going to walk you through that code and i'm going to click ok what that's going to do is going to add all those in right here so if we drop this down you see that now all of those files have been out of here let me show you that again i'm going to clear all this out and we're going to show you once again okay so now the cell's cleared now if i want to add files i'm going to click on attach i'm going to hold down the control here and then i'm going to click ok and now you'll see.
Those three files have been added in and they've been separated by they have a separator on them there's a comma right here that's separating each of the files that's going to become important then when we click send email those individual attachments are all going to be sent separate so we're going to walk through that macro and show you just how we did that now let's take a look at a few things we also have the ability to create a short link and so let's say i've i've already copied a paste i'm going to create a short link i want to add that short link in email i don't want to send the customer an entire link i want to know if he's correct so we're going to show you how to enter a discount first you're going to enter a long line i've copied my excel for.
Freelancers fan page here on facebook and click ok what that's going to do is automatically going to create a short link it's going to send it and here we have our short link right here here's a short link here is the main link right here we also have that added to here notice it's been added here i've got a long link i've got a short link and i've got a link id and we're gonna also know if it's been clicked well that's great that's how we create a short link from just any link but what if i want to send a large file what does let's say i want to send one over 100 megabytes how do i do that i can't do that in email i'm going to show you exactly how to do that let's click add a file and pick a large file i've got one here 167 megabytes way too big to be able to send over an email so i'm going to show you exactly how to do it click ok it's going to generate a short link and we're going to be able to copy that short link right inside the email there it is there's.
Our email now all we need to do is copy this and paste this right inside our email and we can email to the customer it couldn't be any simpler than that i'm going to show you exactly how to do that inside this training that's great okay but what about that now we have our emails but what i want to know if they actually click them how do we do that all we got to do is click get link data and it's going to get our data from us it's going to tell us how many how much data we got and how many links we have and just like that eight links were imported four links have been updated just.
Like that now when we go over to linked data we see anything that's been clicked so notice that nothing has been clicked but all we need to do if if somebody clicks on this let's go ahead and click on this we're gonna click on that it's going to bring us up to this video here that we have this large file it's going to start downloading it but i'm going to cancel that we don't need to so that's how they have to do now if we go ahead and look in i'll show you that screen in just a moment i got a lot to show you on that can't wait and now if we go ahead and click that we notice we just click we're going to get get link data one more time and now we see that eight links and four links have been updated we take a look inside our link data and we see that it's now been clicked once because we just clicked on it i'm going to show you that we're going to get into that right.
Now because i cannot wait there's so much to cover in this so many really cool features that you can start using today and to generate this and of course we have another one that's going to clear our email we're going to go over that as well so we got a few macros so let's get into those macros and see just how we did that inside the developers to get into the vba you'll have the visual basic here for some reason you don't have the developers tab available you can just find that depending upon your version of excel wherever it is found inside the options you will find it and look for customized ribbon regardless of the version and select the developers option here that's going to display it you'll also have a shortcut if you want alt f11 will get you inside.
The developers we've got two modules here just two modules we've got email macro it's gonna we got sub clear i'm gonna be creating that didn't create that yet and we have that's pretty easy and
We have send email so i'm gonna do that send email and then of course we have link macros we have some ad file attachments it's a little bit quicker these videos tend to be a lot longer so i'm going to i wrote this i'm going to walk you step by step because not only do i want to show you how to create these macros i want to show you how to work with integra map that third party tool to help create this automation so let's go over the simple one first and that's called the add file attachments that is the same macro that we use when we add a file attachment i think i should.Clear this before we do that so i want to add some code in there so make sure to clear out e6 so let's just delete that right now add file so all we need to do is add multiple files holding down the click we're not we don't want that 167 and then clicking ok and what that's going to do is add those files in there so it's going to add all the selected files in there just like that so that's going to be an e6 so let's go ahead and do that right now inside the vba the first thing we want to do is with attach file let's go ahead and clear that out i want to make sure sheet 1.range e6 is going to be clear we want to clear the contents so that way you don't necessarily need to you keep it open it'll continue to add to whatever's there so if you want to keep adding and.
Adding we're just going to clear it for now.clear makes it a little easier for the training dot clear contents okay so we've cleared the contents of e6 on baseline and now what we're going to do is we're going to set the attach file we've got some file attached file as file dialog we're going to use that we need to know that one we also want the file attachment that's going to be a stream and of course the attach file as application file dialog we want a file picker we're going to picking individual files if we want to browse for folders sometimes we're going to use folder picker so keep that in mind then of course with this file dial like i want to set a title select select.
File to attach i'm going to change that just select files because it could be multiple so files to attach okay so now multi-select we're going to make this true normally it's been false but i want to give the user the ability to select multiple files so multi-select is true and then if show negative one if it does not equal negative one means they have found something they've selected at least one item if that is correct dot show will be negative one but if for some reason it is not negative one then they have not made a selection so in that case we want to skip all this and go to no selection assuming that they have selected one or more files what i want to do is i want to loop through the files in those selected items so the best way to do that is use a for each next loop so the best way is for each attach file we define that as a string.
In the selected item so for each one of those what are we going to do i'm going to take whatever's in e6 and i'm going to add to that i'm going to add whatever's existing already i'm going to add the file attachment which is going to be the full file path and i'm going to add a comma on to that so once i do that it's going to add every single full file path and then it's going to be separated by a comma once we are done with that then i want to just remove the last column because the last one is going to also have a column a comma on the last item i don't want that comma i want to remove that comment the best way to do that is take whatever's in e6 and on the left we need to determine the entire string here the left portion i want the left.
Portion and the length in this case the length of whatever's in e6 the length minus one so i only want to keep the left portion i want to keep everything but the last character so that's going to do using the left is going to allow us to keep everything in there other than the last then the length of the left plus the last character that last character is going to be that comma i want to remove that that's it that's all i have to do to take those attachments inside and of course when we email and actually email those i'm going to show you how we loop through those and and.
Start adding those attachments based on that that comma is going to help us out as our separator okay great but how do i add a files link i want to add a file as a link actually let's do this one first i want to create a short link we'll do add a file second create a short link that is the macro that we did here create a short link this is the same one where we pasted it in and then added that short link automatically inside here so how do we do that well again we're going to use introgrammer integrator is a third party tool it's just like zapier but i find it a lot better a lot easier to use and a lot more better ui so let's go into that and see just what instagram is here is instagram it is this application i'm gonna include the links down below so you can sign up it's absolutely free up to like 1 000 different automations so it's it's a great tool and you're.
Allowed to have two on at the same time so two so if i try to create a second one it's going to let me know that i've exceeded the maximum so keep that in mind that two i'm going to create three different ones for you for today and you can use end them interchangeably you probably won't you use them all at the same time but we're going to create three different automations three different animations we're going to be creating a short link we're going to be creating a shareable short link and we're going to create link data we're going to use a few different third-party tools we're going to be using dropbox if you don't have dropbox and great free tools so i'd like to get you on that and we're going to be using another link shortener i've used bitly in the past but i found something really really cool we're going to be using something called as you can see here there's.
An automation called re-brandly it's a really cool tool i like it as a link shortener a lot of possibilities it's free and let's just take a quick look at re-branding is a great tool it's basically just a link shortener tool but it's got a fantastic api with a lot of flexibility again it's free and all you need to do is just create links we're going to use it through through its api we're not going to be going on here but basically this is all where our links are tracked the links that we just made notice the excel for freelancers that we built notice it lets us know that there's one click and that's the one we just made so it's really great so here it is so everything we're going to create is going to end up on rebranding and we're going to use this tool inside instagram so instagram so how do we create what's a scenario let's take a look at a scenario.
A starter is some type of an automation that you want so for example if i create a new scenario here and i want to do something what i want to do now when it comes to excel the best thing about what i've used to do a lot is just create a web hook because excel can do lots of things on automation so there needs to be that instigator that causes things to happen and we use a web hook to do that and basically inside a web book it looks something like this so you'll have a url like this looking like this and you'll have some information some that you want to send some.
Not always but some information that in this case we want to send a long link and we want to get back something so basically we're sending something and we're getting back something in this case we're getting a response back the response is basically the short link so we're sending the long link and we're going to get the response so how do we do that well in intro format it's really simple the first thing we want to do is create a web hook so and to do that we need to search web hook right so web hook we find one called web hook not necessarily wp web books this is the one web hook.
We want we want to use that one we also want to use of course re-brandly we want to use re-brandly so and you can add as many websites so let's say we're going to add a web book let's click here and we're going to create a custom web hook and what that's going to do is it's going to give us and we give it a name so let's use a name we're going to call this just test because i've already created enough and we'll walk through what i've created so in here is called test i'll just create a test you can give it any name you want as long as it's not the same as the other ones and click save what that's going to do is generate this web hook and you're going to take this web hook and you're going to copy it to the clipboard then what you're going to do is you're going to bring it inside your vba code and you're going to paste it inside your code we're going to go over this longly so basically inside this what i'm going to do is i'm going to create a long.
Line i'm going to create an input box remember that input box that you saw when i clicked that that's this input box all i want to do is just say please enter it's not very pretty but it's very basic it's fine for training purposes and all the user's going to do is enter that long link right here that way we can place it into a variable so once the user's entered that link we're going to place it into that variable that variable's going to be called long link that's a string variable and of course if it's empty we're gonna exit the sub because when i canceled it it was empty so we just wanna make sure it's empty next up we're gonna set the ottp create object we wanna create an http object this is gonna allow us to send information over the internet then i want to use a web hook this is where you paste in that code i'm not going to paste it in because i've already got it all set up so this is where all you need to do if you're doing this yourself.
Is go in here again inside here you want to copy this to the clipboard copy this web book and place it right in here just paste it up to the question mark make sure you have that question mark next up is where you want the information to send this name here file to share can be anything you want anything you want we can change it to whatever it doesn't matter as long as you recognize what it is this is our variable but make sure that we have the name then it's equals right make sure this is part of the quote then our variable is and we want to test that variable in the long link so this is what we're going to send we're going to send out this information and this once it gets then we're going to send otp patch it's going to be url that's just some information.
For the otp then i want to set the content type as an application json that's in the headers then basically we're going to send the information when we send it we're going to get back a response so let's take a look at that as soon as i'm going to make sure this one is on now i've created it so what's our next step so once we've created i'm going to go back into our next step now i'm going to add another module in this case what do i want i want to get something else in this case i want to create a short link so i've created a rebranded account so re-brandly i'm going to type in because.
I want to create that short link right so click re-brandly now notice it pops up right from you but for you it's going to ask for authorization in this case what we want to do is want to create a link so we're going to click create link and then what we want to do is we're going to get some options here the first thing we're going to see is main workspace if you have several workspace you can check which workspace you want we want a destination we want a destination now what is that destination we only have one option file to share and if remember correctly inside our object the link that we sent this web book that we sent it only contains one item if we send more it'll contain more we only have one file to share and that is the long link that is the long link that we want to put in here that is our destination so we're going to put that in here just click it.
Once or drag and drop either way that's going to bring in now file to share now the rest of these options just optional we have domain id if you have a specific domain name id or slash tag that's pretty much it all we want to do is pretty much get this long link and put it in click ok all right so we've reset the information rebrandly is going to take care of the business and bring it into a short link now i need to basically extract that short link but i need to do just more than that what i need to do is when i bring it inside i need to i want to only more information than other than just the link i want the link id because that's going to help us get the data so every link we have a long link we have a short link we have a link id and then i want to get.
The number of clicks so i need both the short link and the link id and inside our code what we're going to be doing is we're basically going to be parsing it so we want the response to be both of those we want the first thing is the short link and the second is the link id i want both of those inside the response that's the response that we're going to get back so let's create a response we're going to click add another module click web hooks and the only thing that's left is the red book response because we've sent web book now we're ready what do we want inside the response status is fine as 200 what do we want the body we've got a lot of options but i really.
Just need two things first thing what i want is that short link called short url so we're going to click that and then what i want to do is i also want to put in the bread the short link id that's a unique id just for that link but i want to separate that so i'm going to put a dash in there and why am i doing that because inside our code what i'm going to do is i'm going to use this dash as our separator and figure out the difference between the short link and the link id i want to separate those basically parse those from text so we can do that inside that so all we need to do is just create that and then next up i'm going to place that link id so we have the short url and the branded link id that's it that's all i need to if we see if we have some advanced.
Settings we can add custom headers and things like that but we don't need that for now all we need is that that's it so that's going to be in our response so we're going to click it's basically on we're going to save our changes but although there are no save changes and so that's all we need to do we're going to double check that our link here remember our link is going to ending in in this link it ends in 5 lw so we want to make sure we've got the right link that's situated same thing here 5 lw it's the same link great so we're ready to go now all we need to do is click the button that's been assigned to the macro and that is our create short link i'll just pull up a link right now we can pull up any link i'll just get this link here because that's fine any link is fine and then we're going to go back in to the application itself we're going to click create.
Short link and what that's going to do pasting that and clicking ok it's going to get both of those information and now what we do is we've created a message box that's going to show that just we added we've got the short link we have the dash right here and then we have the link id so that's what we put in the message box here i just put in a message box to show you what it is message box response so that is the response that we get so once we have that response we can get rid of it now i want to take that response and i want to parse it so i want to get the short link and that short link is basically the left position of the response and we're going to look for we're going to use in string of the response and what i want to do is i want to look for this dash once that dash is found i want to get only to the left of that and i want to subtract one.
Why are we subtracting one because i don't want to include the dash in that i only want the link itself that's going to get us our short link our actual income is going to put it in a variable called short link next up i want the link id in this case we're going to use the right because i want basically the right the right of what i want the right the last number of digits but i want the right of in this case everything after the dash mark so to get that all we need to do is determine the entire length of the response and then subtract out the whatever is in up to that point up to the point of the dash and that's the only we only want the right portion of that that is going to extract the link id those are the only information i need now i've got it i've got.
Everything i've got i've got the long link which is defined in the variable i've got the short link and i've got the link id now all i want to do is i want to put it in two different places i want to put it inside this table here because this is on a per email basis it's really easy we can once we clear out the email we're going to clear this table out this is only to be used inside the email so then i also want to save it this is temporary it's going to be cleared out when we clear the email this is permanent because it's inside our database so now we've just added this and now it's here so now we have the three things the long link the short link and the link id so we're gonna put that inside the table and we're gonna put this inside the two emails but all we.
Need to do is find the first available column in each and then put it there so that's just what we do for the remaining lines of code the first thing is we want to add the link table details and that's based on sheet three the link data and so the first thing i want to do is get that first available row and put that into a variable called link table row this line of code will do just that sheet three a nine nine and excel plus one plus one is going to guess the first available row then all i'm gonna do is place the data in column a the long link in column b the short link and then column c the link id very very simple and then same thing here just on sheet one we're focused.
Of course that's email sheet we're going to focus on j column j that's the one i want to get the first available in this case the first available 17. once we put that into a variable we're just going to put the long link and the short link in there that's going to add the email details that's it that's all we have to do to get that short link the macro takes care of the rest our integromat takes care of all the hard work just a simple macro and that's going to automatically then all we need to do is put that link inside our email here and we're ready to go next up is the ability to add a file as a link this is a really powerful feature as explained earlier when we have large files we can't possibly attach them inside an email so the best thing to do is create a link and dropbox does a pretty good job of this if we have a dropbox file let's just say this is.
Inside our dropbox this is a link text you know we do know we can inside dropbox what you can do on a right click on this with dropbox is basically copy the dropbox link and then paste it in but i want to do that so that's basically what i'm going to be doing is again let me just show you that again any link that you have in dropbox dropbox is like a file sharing that you can do use anywhere and basically just like google drive or onedrive you can share it so with dropbox basically all we're going to be doing is just copy the dropbox link and then of course we're going to be pasting that in but basically what i want to do is i want to create that dropbox link automatically.
And i want to convert that into a short link it's a very long link if i paste that link down here you're going to see it's a very long link and i really don't not only do i not want this long link inside an email i want to track it if it's been clicked i want to know about it so i want to shorten it up and i want to track it so i want to do all that automatically so how do we do that and so to do that let me show you how that works i'm going to turn it on first i think it's turned off we need to make sure that it is on and we have this get share blink this is the one we're going to be going over and i want to if i refresh this i believe it's turned off so i want to turn it on so anytime it's off it's not going to work right so let's go ahead and make sure it's on clicking on down here it's going to turn it on it says i i got to turn another one off what i would.
Do is i'd create multiple accounts if you want to use the free just create multiple accounts don't tell them i say that or of course you can pay for them i'll include the link down below so now what we have is get shareable links so we've got that only two of them so that's the one i'm going to be using that's what i'm going to be going over with you so again all we're going to be doing is we're going to be adding a file to the file link i'm going to click on this larger file here click ok and it's going to create that automatically link and it's going to place it right down here and just like that here's our short link that we have now here's our long link and of course it's in the database so how do we do that well let's take a look at the macro that's been assigned to this add file as link and we're going to go inside the macro here.
And again it is one more type of web hook again in this case what we're going to be doing is we're going to first determine we need to open the application file dialog right we want the user to select some type of a file we also want to make sure that that file is already in a dropbox location right it really should be because dropbox is the ones can be shared so we need to ensure that it is in a dropbox location and we want to put that dropbox location somewhere so i'm going to take my dropbox file link and this is where my main dropbox folder is now the file that you're sharing can be in any dropbox folder and you know is embedded as many folders you want but i want to.