Be sent to specific types of customers and i'm going to show you how customers can opt out that's a tall order we have a lot to cover today it's going to be brand new record-breaking training so i want to get it all in before i do i want to make sure you understand that we have these trainings each and every tuesday absolutely free the best way to ensure that you're going to get these free trainings each and every week is to click that subscription below and make sure you click icon the like icon and you also want to make sure that you get the notifications bell on.
That rung that's going to help you it's going to help our algorithms if you comment below let me know what you think of these videos i always appreciate it it's always free always each and every week and i include a free application in the download links in the description below so make sure you get that and also if you do like these applications and you do want to support our cause here i've got an incredible deal 150 of my best applications all in one zip file and include a library where you can single click to open any.
Application or single click to open the training video that's associated with the application that's 56 dollars right now at least for this week and that's only 37 cents workbook so make sure you pick that up that's going to help us out let's get started we've got so much to cover so basically what i want to do is i've got a list of customers on this sheet here i've got the campaign that i want to build out for you here so we got a lot to cover basically i'm going to use this header row i want to build this in front of you and show you every line of code so we're going to do that right now i've just pre-filled it.
With just a few little samples i want a customer list type here because we already have some customers listed and they're listed by type the reason for this is if you want to send your campaigns your sms or text message campaigns to a specific type of customer or all types you can do that and i'm going to show you just how to do that in this training it's going to be a relatively simple on the code it's not too heavy on the code this does involve a third party as of course we're sending sms messages to anybody in the world and.
The best way to do that is use a third party app because they're going to take care of the setting for us and i'm going to show you just how to code for that so we can get that done and so that app is called tweelio twee leo i'm gonna fill this in and talk a little bit because i talk a lot usually and so twilio is going to help us out with an api now i've gone over this api i've done all the hard work for you and figured out all the complexities of this so i'm going to be able to share that with you today actually i'd like a lighter green color let's go with a.
Lighter green color something a little bit lighter in the mood because we've done this color so much so we're going to use fill effects here and i'll use the turquoise which is one of my favorites the pos software was on this color if you haven't seen our pos we just reached over 1 million views with the pos point of sale software uh make sure you check that out you can search my channel for that that was a really amazing application if you haven't seen that before make sure you catch that up all right just going to give this a fade effect and it's going to be a very basic screen.
All i want to do is be able to see our campaigns through this okay so we're going to call this give this a title called this send i've already got send sms see i've already got the font and text trying to trying to do some shortcuts here because these videos tend to be longer so messages that's going to be the title send text and sms messages we're going to merge and center that just so we can get that centered over i think that's okay let's just not include lk through k.
That'll merge it okay so send sms and text messages let me make sure i spelled that right because otherwise you're gonna have to stare at a misspelling for the next hour or so okay send sms and text that looks good all right so basically we're gonna have a button set up here and then i'm going to have a small form and i just want to color all these cells just like that light turquoise that's just the basic background something pretty simple so what do we want here well basically what i want is i want to have a button set like i said up here that's going to allow us to create new.
Campaigns delete campaigns or save campaigns i'm going to have a list of campaigns here on the table and just a small form here so we can start out by in d we'll call this campaign let's see campaign details i'll put that in capital letters campaign details and i got to keep an eye on my spelling here because otherwise okay so then below that i want the campaign name below that i want the campaign name.
And then below that i want to send on i want to know when it's been sent and then i want the message text i want to keep track of the message check so we're going to put in 160 characters and then probably here in column g we're going to put i also want to know the send to are we going to send to all customers or just a specific set of customers so we want send to and send at want a specific time that we're going to set it because we're going to be able to schedule these so i think that's going to be really helpful okay so then we're going to cover these.
Fields let's just say white because these are going to be our our form fields where the user is going to be able to fill it
In and then also on this text message i want a larger field so we're going to merge and center that say down to row 11. so i'm going to merge and center that color that white also and bring it to the upper left okay so this one i think we can merge and some of this across this is going to be our entire camp so this is basically the form that i want to create and then these also i'm going to merge and center this one.And merge and center this one actually and then just left justified that's a little bit easier color these white this is already a drop down list from test i've already set the data validation let's just show you that just a little bit quick so we've got a name manager some criteria that we can delete that's not important because that'll be recreated i've got one really important uh basically customer type this is a drop down list based on this list here it's going to include all list and then four that's just a little way we can jump ahead and then i've created a data validation here so.
Data data validation here is just the customer type here that's all it is just to save us a little bit of time and then uh that way we don't have these records okay so basically what i want to do is just color these give these all a green border that's within our theme so i'm going to format those cells go into the borders choose a color that's in our theme which is this dark or turquoise or dark green run the outline and then there and then i just want to basically highlight these and do the.
Same thing with these except this one i'm going to put a border of a dotted line on the right and then we're going to fill it in with the solid on the left the top and the bottom okay so we've got that that looks pretty good that's good enough for our form here i think it's sufficient so again we have send on let me just add the upper and lower buttons here because we want to put that in too so basically it's just a very simple form we're going to keep track of all and then i want the table down here.
So here is where we're going to have uh also the table that we're going to track those campaigns so i'm going to put campaign name here i'm going to put send on here send at we need to track which campaigns that we've saved send to type why are we going to send it to all send to type are we going to send it to all customers or just a portion message text here and i'll probably put that in a larger field text.
And then maybe ascend on i need to know if it's been sent so sent on and this is going to be a little bit larger field message text so i'm going to merge and center that i want to put in some details here so message details right messages have a maximum of about 160 you can actually send more than that but it'll be in multiple messages so i want to keep track of how many characters in a specific message so let's call this message characters and then i want to know let's say the message limit let's just.
Put in the limit and i'm going to put in 160 there generally that but you can send more i think up to 1600 but it would be in multiple messages and so available characters okay and let's see here so now i've got this that's what i want that's pretty much what i want now let's uh add in um actually let's bring this down one row i'm going to bring this down one row we need a little bit more and then i'm going to just going to put actually so that we have a title here.
Called we'll call this message campaigns instead message campaigns that way we can track a nice little table here so the message campaigns are going to go up here i'll merge and center that and then i'm going to hold down the control actually i want to merge and center this too so now i can hold down the control and what i want to do is i want to color all of these the same so let's give it all format those cells and we'll give it just a fade within our seams within our theme so i'm going to fill the effects and then we'll just go to.
Again fill effects i'll use a little bit darker color here and then the medium and then hopefully you can see that's off the screen okay that looks good that's what i want to see and a border around there too format so that way we're consistent i'm going to drop this down it's a little bit off the screen border using the same color there's not a lot of design on this so we're going to go through the design pretty quick okay and so that looks good okay so now we have the and then just the border here and then that's going to be our table.
I've already set up some conditional formatting which i'm going to show you of course keep it simple colors are
There that looks good and then i'm just going to fill this a little bit lighter than the upper one which is going to be the two lighter colors which is going to be this and this that's going to give us our fade effect which is the light turquoise okay so that looks good so basically our campaign name send on the text that we're going to apply this message and then we have our message characters here let's uh format those cells but one more i think i'm going to put in one more table here i just want to have some variables this.Is going to be very easy variables and variables are we're just going to put two in but you can put more in your application i just want to show you the idea so first name and last name why do we want variables variables are going to help us so if we want to send and we want to send something more personal to our customers we can replace this what's called first name with the actual first name of the customer or the actual last name of the customer so by using these variables inside of text for example we can do hello.
And then first it's something like and then alt enter to go to the next line if you would like to do that alt enter will get you there our new summer sale has just launched please join us tomorrow okay something like that so you might want to keep it short but i also want to know the limits so how many characters are in this i really want to know that so equals the length you can use length and then the text of here good so so we've got 78 so we have.
Available characters how many available we have equals 160 minus 78 okay that was going to keep us you can put some conditional formatting in here okay let's just hold down the control format these cells now we have our two helpful tables i'm going to format those cells so go into the border using our same color and just use inside out okay that's going to cover it that's pretty much it so and all we have to do is add our button sets in here and let's just center this i want to merge and center this this should be merged and centered because it's a.
Larger and then that looks good and then we're going to i've got some conditional formatting in this table already actually so if you see that isn't that nice let's just go over that with you we've been over that a few times so if you've seen my videos if not no problem you can always download this workbook for free manage the rules i've got several rules set up first one i've got a right border this right border will give a border on the right and that's only applies to column k and basically if d15.
Just take notice that there's no dollar sign below the uh to the left of the 15 and that is so it can apply to every single row in our applies to range so we can do that the same thing and what that's going to do is going to carry throughout i've got the same thing for the left border except this is going to apply to column d and column k right border applies to column k left border applies to column d and then i've got a dotted line in the center that's going to apply to the whole table now theoretically if this is up make.
Sure that this dotted line are below your your right and left border so that the dotted line will not take precedence otherwise you could have to put an interior range notice how it covers the entire range you could also only cover this range too right because we're only focused on that that would work also as well so we can do that we can update that to 53 a few ways to do that right so because click apply and then we're going to get that dotted line what other rules do i have i've got the selected row i want to know what row we've selected when we select a campaign i want those campaign details.
To load in our table here so when we select a campaign that campaign row is going to go in b3 it's called selected row so that means whatever row we select it's going to be highlighted this color and i'll show you that in a moment and then i think we got two dotted lines probably don't need that i'll remove one after the training and then um we've got uh also i've got a white we're going to color this white with a border or i also want to call it the bottom row how do we do that if there is let's take a look at this.
Rule edit this rule okay so basically if the upper upper upper row of any row above contains a value is not empty and the row below is empty in column d notice we're focused on column d in that's case then color the bottom row basically give it a border give it a border and then i also want to color opposite rows this one if we add a rule this is going to be even rows notice the mod of two equals zero it's going to color all.
The even rows this lighter blue assuming that d15 does not equal empty this one right here let's pull up this one right here i believe this one is just the opposite this one is going to color oh we've been over that before actually this one should be format we're going to give it the white color that will color automatically right and we apply that it's going to go away okay great so we've got that we've got them all covered that's it for the conditional formatting.
To give us our table this training is not necessarily about that so we're not going to spend too much time on it but i wanted to give you a brief overview of it okay so how do we do it so how do we get that let's create our button sets right now now that we have the basic structure and we understand that we have i'm going to create a button set i want to be able to save load delete and send campaigns so i'm going to insert some pictures on that i've saved some pictures here into our specific week here i've got some pictures say we're going to use all these and click insert okay and then.
I'm just going to reset the height to 0.25 or something like that keep them smaller i don't want them so big and the first thing we've got is a logo we're going to use this logo right here that's going to be a nice logo here and i'm going to duplicate that because i need to create that for another button too but make it smaller and then go back to .25 on that one as well all right so what do i want i want a a new campaign i want a delete actually we don't need to.
Cancel probably not the cancel in this one i want to send this is going to be our send i want to delete it and i want to save it we're going to use this for this save okay so let's create some buttons now so i'm going to insert a shape i'm going to use this rounded corner here and i'll create a button shape something like that but i want to give it the same theme as our which is going to be this turquoise here i'm going to give it that color i think that's a applicable color for us and let's set a size on this probably about something like probably a.
Height of that should be good that's pretty good as it is right there okay so i'm going to duplicate that we need about four buttons so let's uh two three four okay oh that's too many okay so four buttons but i actually i want to move these on top so i want to make sure these shapes are going to be all on top so i'm going to highlight those format those and bring to the front that way they're going to be on top of these buttons and then we can unselect it okay so the first thing is we want to add a new button.
Okay so let's just call this new campaign i'm going to move it over here i'm gonna probably make that a little bit lighter that's hard to see against that so we can make it a little bit lighter of it like that and then we're gonna call it new campaigns new cam pain okay and then i want to make the font a little bit bigger so i'm going to select the button itself and then make the font bigger i want to right justify that that looks pretty good and okay so what we'll do is we'll bring the button.
A little bit smaller all right that's it that's for a button we make sure we want to center it and then we just have to center using that great and i'm going to group it and i'm going to call this new campaign button okay and save our work always save our work as often as we can you can use dropbox for that it's very handy it'll save every version same thing for this i'm going to this is going to be save campaign now new campaign and save.
Campaign will not be displayed at the same time right because we're either going to have a new campaign or a existing campaign they both won't be on so we're going to call this save campaign actually it's probably just easier to to duplicate this button here and this we're going to call this save campaign here save campaign and then there we go okay so that's it that's it all again we're going to apply the same right and then increase the font here this font was 14.
This font is 16. let's make them both 14 okay and then do the same thing center and then center and then right okay good so now we have save campaign now what we're going to do is we're just going to center this hold down the control put them both in the middle group them we're going to call this save campaign button and again i'm going to put these both on top of this actually it looks like i had a button already there notice that i'm going to delete that we had a button there with the same name from my previous test so i'm going to add that name again save.
Campaign button okay now we've got save campaign button and what i'm going to do is i'm going to put these right on top of each other that is because both of them are not going to be visible at the same time okay and then this one we're going to call this delete in fact let's just go ahead and hold down the control right justify both of them put them both in the middle make them both 14 and now we got it okay so this one's going to be called delete campaign delete campaign we want to be able to.
Remove campaigns if we create them okay so we're going to get a training on forms a little bit and we're going to get a great training on sending sms's all right that's it that's it pretty pretty simple for the delete button not too much there again control center group them call this delete campaign cam button and then the last one is i want to be able to send the scheduled campaigns and that one's going to be going to put that over here i want this icon showing up here and then i want this one so we get the.
Send look this can be smaller okay great i just want to move that one to the top because that's more important so bring to the front okay that looks good too small all right so now this one's going to be called i'll size that send scheduled campaigns we'll increase the button width on that so we can accommodate all the text okay that looks good it's going to be it's the most important button because this one's going to be sending and only sending those that are scheduled.
And then this one this thing is way too big this little icon here we'll drop that down to 0.15 okay so that's the icon that we're going to use to send it we're going to group and center that as well that looks good right there so we can see both of that okay holding down the control for each of the icons grouping them and we'll just call this send campaign button but actually i don't want the one that's going to send it i wanted to check to see if there's any schedule because we're going to schedule those campaigns right so i really just want to check.
To see if there's any that are scheduled because they're going to be based on the date and time okay now that we have everything what we can do is use our selection tool again highlight all this and make sure they're all in the center so we can use this one shortcut right here okay now everything center now we've got our pretty much screen this is how i want it to look that's all i need and one more thing on the table i just want to put in a row this would be one of those hidden fields i want to keep track of the row so i'm just going to put row here we'll hide that eventually all right it looks good that's all i want so again i.
Want this to be notice that this is highlighted green if we change this to 16 it's going to change we've already added that conditional formatting in because this training we're not focused on that i do have trainings that are specifically focused on conditional formatting codes i want to know the send row that's going to be the campaign roll let's focus on the save campaign we're ready to write our first macro and i'm going to call this summer sale okay and we'll send to guess we can send in this case we'll just do all types but we'll show you that and then let's give it a send on i just.
Want something in the past so we'll call january 10th this i've already formatted as a launch date and then send out i want to time i've already formatted this field as a time so let's just say 10 30 a.m that's fine okay so we've got our campaign we've got you now what i want to do is i want to click save and i want to have this to save to the first available row we're ready to write a macro on that i'm going to save the work i'm going to drop this up because we we're mostly done with the formatting portion of it let's get into the vba and show just how we did that clicking on.
This campaign i want to take all these details and save them down here but what's the best way to do that we can write six or eight lines of code to save it or i can use data mapping for this why don't we use data mapping so what do i mean by data mapping i know this is an e4 so if i put e4 in this call in this row here and then we hide it using changing the font colors or hiding the row then we can map the data e4 goes in the campaign sendon which is e5 goes on the send on send at right in this case h5.
Would go here so we can use data mapping i'll walk you through that if you haven't seen it yet e4 e5 h5 you get the idea send to type would be h4 message text of course that's based in e6 so e6 and then we don't the la we don't need the last one because one there's no send on field and two vba is going to take care of this when we send a campaign it's going to mark this ascent that way it doesn't get sent again so don't worry about this when we're only going to map we're only going to.
Run our code from the first two i believe the let's say this is column we're going to run it from column equals column 4 2 column what column is this this is column equals column all the way to column 8. so from 4 to 8 we're going to run a loop and basically what i want to do in our code is whatever is in e4 put it in the first available row whatever's in e5 put it on this row whatever is in h5 put.
Here so that's how we're going to fill in the table so we're going to run a loop from 4 to 8 to do just that and let me go ahead and merge and center these because we want merge actually i want to merge and let's left justify them so we'll just do the first few because we're not going to focus on too many and then i'll just go in home merge and center and then one more time on the left justify that's going to get our message text okay so that's what i like so far let's get into the developer if you don't have your developers tab here you can find it located in our options and then if you look in.