Collaborate on E Invoice Format in an Excel Template for Communications & Media with Ease Using airSlate SignNow

E invoice format in an excel template for Communications & Media

here I'm going to show you how to automatically send an invoice with a click of a button and it works like this we finished our invoice we hit send invoice and we get an email all set up along with our invoice right here and when we open that PDF we can see that it's only the invoice the button and anything else that we don't want included will not be included and I'll also show you how to make it so that when you click that button it will automatically send the invoice instead of just showing you the email and letting you verify everything's okay before you hit send so you'll be able to choose which one of those you want to use and this does require a little bit of VBA but I'm going to go through every line that you need to change to make it work for your situation it's not that difficult I promise and if you've taken my VBA course already you're not going to learn too much that's new in this tutorial but you might get a new way of thinking for how to arrange and organize your code in order to make nice little macros within Excel and that's what you could get out of this and if you're not taking my full VBA course I highly recommend it because it'll help you automate Excel and do really cool things like this and so much more I'll put a link to it below below this video and it might even be on sale so check it out now let's go back here and let me show you what is going on there's really three things that we need to do to get this to work so open up your favorite invoice and the first thing is that we want to name a few of the cells and the cells that we want to name are cells that we're going to use within the code later so notice that what I've done is included an email cell right here you could have it right here in the invoice itself or put it out here to the side so that it will not be included in the PDF it's really up to you but this is the email to which we are going to send this invoice and then we also want to name the invoice ID over here because I want to include that in the name of the PDF that we're going to have to save in order to send so we're going to save a PDF of this invoice on the computer that will be included in the file name and then maybe we want to include the total down here so I've named that as well and to name cell is very easy you just click it go up here and input a name for it I'm going to call this invoice for hit enter and if you want to verify the names are good we can go to the formulas tab name manager and we can see our names right here we have invoice email for ID total and then one for the table down here and once we've named everything that we want to use later in our email then we just want to make sure that this guy has a print area set so we're going to go to page layout and print area and we can click set print area now we haven't selected the area that we want to print yet but that's how we do it so for instance here I want to print the invoice but I don't want to print the send invoice button so I'm going to go like this give it a few extra rows in case we have a few extra line items and then go to print area set print area and if you mess it up just print area clear print area and then redo those steps but once you've done that you are good to go in the worksheet and it's time to go to the code and you can download this workbook and follow along or just pause the screen and type everything out what we want to do from here is hit alt f11 to go to the VBA window and look over here in the left if you don't see it go to view project Explorer and then you'll see that window open up your file go to modules and module one and here you go now I'm not going to go through every line here there's a lot of stuff that I can say and in the full VBA course everything is heavily commented and I explain exactly how all the little pieces work so that you can make your own macros and do all sorts of cool things here I'm going to show you how to change this to work for your situation let's go down here past the variables that helps macro run and go to the make a file name section I'm going to say there are really three sections here file name PDF and email starting with the file name here is our variable that's going to hold the file name but the file name here is more than just a name the first part of it is going to be where you want to save it on your computer then we start with the name what name do we want to give it here I'm going to give it invoice Dash and then I'm going to give it the value from the invoice worksheet from the invoice ID cell so invoice worksheet invoice ID cell the one that we named invoice ID and then at the very end don't forget to file extension.pdf so if we go back here this guy is named invoice ID so ab-123 and be careful with this because you can overwrite existing files but now let's move on to the PDF section you don't really have to change anything here what we're going to do is make a PDF from the active worksheet we're going to use the file name that we created right here and the other important thing for this tutorial is that ignore print areas is set to false we want that so it will respect the area of the worksheet that we want to print and it's not going to print anything outside of that and printing in Excel is how you're going to save your PDF I know it can look a little confusing but that's how it works and now here we've got our file name we have our PDF after this code right here a PDF is going to be saved on your computer so all we have to do is to grab that PDF attach it to an email and send it so we go down here to the email section and here we have some standard variables to be able to send an email from Outlook and then we're going to go ahead and create the subject and the body here and for the subject a simple text invoice from ABC company for email body we're going to have a multi-line message and there's all sorts of interesting ways you can create this including using HTML to make a really beautiful and custom email I show you that in the full VBA course but here let's keep it simple because this works most of the time so we've got text attached is the invoice from ABC by now I want to go to the next line so essentially hit enter so we close up the quote we go for an ampersand and VB new line is the same as hitting enter but within our code to go to the next line we have to use Ampersand underscore it seems a little bit confusing but if you follow this pattern you should be able to customize the message just fine so we have another line right here but I want two enters after it and then we're going to go to the next line so underscore regards one enter underscore ABC company no more underscore because we're at the very end of it we're not going to the next line in the code once you've got that done let's go down here where we're actually going to create the email and send it or show it so what you want to change here is the email section right now we're going to get the two or where we're going to send it to from the invoice worksheet and the invoice email named cell and that is this guy right here invoice email then for the subject we use the email subject that we created here for the body email body that we created here and for the attachments the new file name variable so new file name the guy that we created right here and saved as a PDF or right here and lastly choose if you want to display it like I did at the beginning of the tutorial that's really helpful if you want to make minor changes before you send it or use dot send to automatically send it and that's really all there is to it I know it looks complex but once you understand how to program you're going to think of this in three different chunks we have an email chunk here most of it's standard you don't have to change it that much once you have the base code then we've got a PDF section here same thing pretty standard you can even copy paste it change it as you need to there's many different ways that you can use this I show you so many of them in the full VBA course and then make a file name you can even make this file name Dynamic so this could be a much bigger section than just these two lines but making it Dynamics a little bit beyond this tutorial although I do show it to you in the full VBA course that way you'll never actually overwrite any file on your computer but as long as you change the invoice number you shouldn't have a problem with that and like I said download this file to get this code change it like I told you in this tutorial and you should be good to go and then when you go back to your invoice you've finished filling it out all you have to do send invoice and that's it if you want to learn how to do so much more for automating your worksheets and saving yourself a lot of time check out my full VBA course I'll put a link to it below this video and if you like this tutorial make sure to give it a thumbs up subscribe and hit the Bell icon so you can get all of my future tutorials have a good one and see you next time