Create Your Blank Invoice PDF for Export Effortlessly

Blank invoice pdf for Export

in this series of videos i want to show you how to create your own invoicing system i'll start off by describing how to create the invoice itself in terms of formatting and structure also how to keep a customer database and how to keep a record of invoices and you'll see here that wherever we have an invoice that's overdue it appears in red but if we say it's paid then the red background disappears so it's a good way of tracking your invoices you can also see over here when the invoice was emailed and you've got a link to the invoices as well so you can view them now in other videos i will also go through these macros that i've created you've got a macro that will save the invoice as an excel file a macro will save it as a pdf file a macro that will automatically email the invoice to the customer a macro that will add the invoice to the record of invoices and the last macro here will clear the current invoice so that you can start a brand new invoice and it will also automatically generate the next invoice number for you if you want to learn how to create this invoice from beginning to end including all the macros run by these buttons then follow the link in the description of this video to the playlist that contains all the videos in this series okay in this video we're going to look at creating this email as pdf button it's obviously going to email the current invoice but it's also going to record the details of the invoice in the record of invoices sheet it will give a link to the pdf and also a date and time stamp of when the email was actually sent okay let's look at how we can create this button so if you've been following along with the other videos in this series this is how far we've got we've created the invoice and we've created these four buttons now the new button that we're going to create is going to require us to write some vba code so you need to show the developer tab on your ribbon it doesn't appear by default so if you right click on another tab go to customize the ribbon you need to tick this option here on the developer tab go to your visual basic button and if you've been following along with the other videos in this series you already have a module with these sub procedures in if you haven't been following along just go to insert module and create yourself a module and i'll start the code so we'll call this sub email as pdf now we need to declare quite a few variables here the first is for the email application you're going to use so i'll call this e-app as object and then i'll set that variable create object outlook application i also need to declare a variable for the email that we're going to create i'll call this e-item as object and i'll set that equal e-app dot create item now the other variables are the same variables that we had in the previous videos but i'll walk you through them in case you haven't seen those videos we need to be able to store and transfer the invoice details over to the record of invoices sheet invoice number company name amount date issued date due and that's why i've created these variables here you can see the invoice number picks up its value from range c3 this is a merged cell and the value looks like it's in d3 but it's actually in c3 you've got customer name which is in b10 you've got amount which is in i-41 and you've got term 30 days 60 days 90 days which is in c6 so all of these variables pick up the relevant values from the invoice then you have path now this is where we're going to save the invoice enclosed in quotation marks and you've got that closing backslash at the end of the path file name is a concatenation of the invoice number and the customer name so that's what we're going to actually save the pdf as nyx rec i'll come on to that later but the first thing we're going to do is actually create a pdf from the invoice now in previous videos i set a print area if you've not done that before what you can do is select an error of your sheet and when you go to page layout print area set print area if i then go to print you can see that only prints what i've selected there's no other pages so it ignores these buttons over here now i'll also paste in the code that we used previously for creating the pdf so you say active sheet export as fixed format now i've used that underscore there to create a new line you could put everything on the same line so type is the first parameter you use and then you specify that you want to export the file as a pdf excel type pdf and then the second parameter you use is ignore print areas and we say that's false because we don't want to ignore print areas and then the final parameter is the file name where you're going to save the exported file we're saving it to path concatenated with file name okay so that will export the pdf we then need to save the details of the pdf the email on the record of invoices sheet we need to find the next available row so if you go to the very bottom of your sheet and then do control up arrow key you find the last entry and then you just need to move down a row now we also covered this in previous videos i'll paste in the line of code we created so next wreck that's the variable i've declared up here equals sheet 3 the sheet 3 is the code name for the record of invoicesheet range this is the last cell in column a dot end excel up so that's the equivalent of control up arrow key and offset one row zero column so you're moving down one row to find the next available row and once you've done that once you've found that cell we can say next rec equals invoice number so that would be that cell there a 12 in our sheet at the moment and then one column across would be the customer name two columns across would be the amount then the date of issue and then date you which is date of issue plus term now we're also going to store the date and time stamp when we sent the email and we're going to store that eight columns across that's this column here column i and to get the date and time stamp we can just use the now function to create the hyperlink again we've looked at this code before say sheet3 hyperlinks.add first parameter you use is anchor that's where you're going to store the hyperlink that's in six columns across from column a and the address is the address that the hyperlink is going to follow okay so a lot of that stuff we've covered in other videos but we now need to create the email so don't forget the variable for the email object is e-item so we'll say with e-item i want to set the to field and that's going to equal the email address of the customer obviously and i'm storing that in b16 in my invoice b16 then i want to set the subject line and i'm going to just say invoice number and then whatever the invoice number is so in quotation marks invoice number concatenated with the invoice number variable then in the body of the email keep this simple please find invoice attached then importantly we need to add the attachment and that will be the path of the attachment that you need to specify it's a path specified up here in this variable file name the concatenation of the invoice number and the customer name dot pdf is the extension then you can either say that you want to display the email which is what we'll do for the meantime or send it if you display it pops up on the screen and then you can send it in with now i'm just going to check for any errors debug needs an s at the end of attachments and you can see it creates the invoice email for me and if i look in the record of invoices sheet this is the invoice that's just been created so you've got invoice number company name amount date of issue date due got a link to the invoice so to make this really clear let's make another invoice 3020 is the invoice number today's date that's correct we'll keep this customer we'll add some more stuff okay run this macro it's created a pdf with the correct invoice number i'll just close that down i don't want to send it but if i go to the record of invoices sheet there's the details of the invoice with the date and time stamp and a link to the invoice okay let's create a button developer tab insert form controls button button draw the button [Music] email as pdf edit the text email as pdf right click on the button format control we'll align the text to the left and we'll have a margin of 0.5 on the left side moves the text over then i want an icon insert illustrations icons email i'll do that one it's a bit big change the height to 0.85 now i'll resize this before i put the icon on the button so right click on the button shape format height 1.5 width 4.5 drag this into place now that icon's already selected if i hold down control and right click on the button we can group it let's see if it works let's create another invoice i'll just change the invoice number email as pdf you can see it generates the email for me i'll send it let's see if it appears here on our sheet it does there's the link now what happens if i replace display with send so it doesn't display the email but if you go to the out box you can see i've deliberately set up outlook so it doesn't send the emails but you can see it's ready to go in the out box of outlook okay that's all i wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and i'll see you next video [Music] you