Collaborate on Payment Reminder Mail Format for Export with Ease Using airSlate SignNow
Move your business forward with the airSlate SignNow eSignature solution
Add your legally binding signature
Integrate via API
Send conditional documents
Share documents via an invite link
Save time with reusable templates
Improve team collaboration
See airSlate SignNow eSignatures in action
airSlate SignNow solutions for better efficiency
Our user reviews speak for themselves
Why choose airSlate SignNow
-
Free 7-day trial. Choose the plan you need and try it risk-free.
-
Honest pricing for full-featured plans. airSlate SignNow offers subscription plans with no overages or hidden fees at renewal.
-
Enterprise-grade security. airSlate SignNow helps you comply with global security standards.
Discover how to streamline your workflow on the payment reminder mail format for Export with airSlate SignNow.
Looking for a way to simplify your invoicing process? Look no further, and follow these simple steps to effortlessly collaborate on the payment reminder mail format for Export or ask for signatures on it with our user-friendly platform:
- Сreate an account starting a free trial and log in with your email sign-in information.
- Upload a document up to 10MB you need to sign electronically from your laptop or the web storage.
- Continue by opening your uploaded invoice in the editor.
- Perform all the required steps with the document using the tools from the toolbar.
- Click on Save and Close to keep all the modifications performed.
- Send or share your document for signing with all the necessary addressees.
Looks like the payment reminder mail format for Export workflow has just become easier! With airSlate SignNow’s user-friendly platform, you can easily upload and send invoices for electronic signatures. No more producing a hard copy, manual signing, and scanning. Start our platform’s free trial and it simplifies the whole process for you.
How it works
airSlate SignNow features that users love
Get legally-binding signatures now!
FAQs
-
How can I modify my payment reminder mail format for Export online?
To modify an invoice online, just upload or pick your payment reminder mail format for Export on airSlate SignNow’s service. Once uploaded, you can use the editing tools in the tool menu to make any required modifications to the document.
-
What is the most effective service to use for payment reminder mail format for Export operations?
Among different platforms for payment reminder mail format for Export operations, airSlate SignNow is recognized by its user-friendly layout and extensive capabilities. It simplifies the entire process of uploading, modifying, signing, and sharing paperwork.
-
What is an eSignature in the payment reminder mail format for Export?
An eSignature in your payment reminder mail format for Export refers to a secure and legally binding way of signing forms online. This allows for a paperless and smooth signing process and provides enhanced data safety measures.
-
How can I sign my payment reminder mail format for Export electronically?
Signing your payment reminder mail format for Export online is simple and easy with airSlate SignNow. To start, upload the invoice to your account by pressing the +Сreate -> Upload buttons in the toolbar. Use the editing tools to make any required modifications to the document. Then, press the My Signature option in the toolbar and pick Add New Signature to draw, upload, or type your signature.
-
Can I make a specific payment reminder mail format for Export template with airSlate SignNow?
Making your payment reminder mail format for Export template with airSlate SignNow is a fast and easy process. Simply log in to your airSlate SignNow profile and click on the Templates tab. Then, pick the Create Template option and upload your invoice file, or pick the existing one. Once edited and saved, you can easily access and use this template for future needs by choosing it from the appropriate folder in your Dashboard.
-
Is it safe to share my payment reminder mail format for Export through airSlate SignNow?
Yes, sharing forms through airSlate SignNow is a secure and reliable way to work together with peers, for example when editing the payment reminder mail format for Export. With features like password protection, log monitoring, and data encryption, you can be sure that your documents will remain confidential and safe while being shared digitally.
-
Can I share my documents with peers for cooperation in airSlate SignNow?
Indeed! airSlate SignNow provides various teamwork options to assist you collaborate with peers on your documents. You can share forms, define access for modification and viewing, create Teams, and track modifications made by collaborators. This allows you to work together on projects, saving time and simplifying the document approval process.
-
Is there a free payment reminder mail format for Export option?
There are many free solutions for payment reminder mail format for Export on the internet with various document signing, sharing, and downloading limitations. airSlate SignNow doesn’t have a completely free subscription plan, but it provides a 7-day free trial to let you test all its advanced capabilities. After that, you can choose a paid plan that fully meets your document management needs.
-
What are the benefits of using airSlate SignNow for online invoicing?
Using airSlate SignNow for online invoicing accelerates document processing and reduces the risk of human error. Additionally, you can track the status of your sent invoices in real-time and get notifications when they have been seen or paid.
-
How can I send my payment reminder mail format for Export for electronic signature?
Sending a file for electronic signature on airSlate SignNow is fast and straightforward. Simply upload your payment reminder mail format for Export, add the necessary fields for signatures or initials, then personalize the text for your invitation to sign and enter the email addresses of the recipients accordingly: Recipient 1, Recipient 2, etc. They will get an email with a link to securely sign the document.
What active users are saying — payment reminder mail format for export
Related searches to Collaborate on payment reminder mail format for Export with ease using airSlate SignNow
Payment reminder mail format for Export
hello this is Randy with Excel for Freelancers and welcome to the payment reminders with penalties in this week's training I'm going to show you how to create a fully automated payment reminder system in Excel that includes unlimited reminders automated email reminders automated and customizable penalties Dynamic variables with personalized emails and best of all we're going to do it all from scratch every function every format formula right in front of your eyes I cannot wait so let's get started all right thanks so much Jimmy I've got a fantastic and unique training this week payment reminders with penalties a fully automated system to remind your customers of payments that are overdue if that's not enough I have packed this in to an incredible application that we already created in the past few weeks although this is a brand new training and you will not have to know or have seen any other trains before this however it is important to know that there's already in this application in which you can download for free a complete invoice application fully customizable invoice along with a reminder we have complete payments here invoice and payments manager that was last week so those trainings were completed this one's going to be on payment reminders we're going to be able to automate this payment and we're going to do it from scratch so this is a sample I'll be closing this and opening up a new one after I show you the details of this so we're going to be able to create this now you're going to be able to take an invoicing application along with payments I'm going to wrap this into automated reminder so that your customers get an automatic email so when we click check and send it'll let you know if there's any reminders and of course that means that there's no reminders going to be sent out however if we delete something from the log here and that means that payment can go out so we're going to check and send an email and gets automatically created now that email is fully customizable lets them know that there's an overdue notice for what invoice it lets them know visit a customer name that there was an invoice number six for 974 dollars how much the penalty was it has been assessed and the totals that they now owe how many days overdo that invoice so that's fully customizable fully Dynamic emails we'll be able to create unlimited emails and we'll be able to create unlimited reminders now this is all customized inside the admin screen so we're going to have this brand new penalties now keep in mind the other information the atom screen was from previous trainings but if you catch those I'll leave the links down below ones for the invoice ones for the payments but we're focused on penalty fees assess on over two so certainly this is not a part two this is not a continuation this is a brand new training so you won't have to have seen those before so what we can do is we can create additional penalties we can create as many penalties as we want now I've got here a list just about a table that we've set about 10 or so and of course these are the types of penalties I can create it for example we want to let the customer know for five anything five days overdue but there's no penalty on that maybe something that's 30 days we do want us as a felony and that penalty is going to be based on the percentage of the balance it can also be assessed as a fixed amount so we have a fixed amount here so twenty dollars and fifty cents on this one however the percentage of balance so we get to create a fully customizable penalty type based on a percentage or a fixed amount along with the number of days overdue and then when we create a brand new payment run add new we can then set a name here and then we can set the penalty type here so this is what we're we're going to be creating we'll create an email subject and an email message and those include these variables here and we can let them know that we want to send this we want to create this monitor let's say within 30 days after the due date for balance is greater than whatever you want to set maybe anything over greater than zero balances so we can create that and save that automatically so this is what we're going to be creating this is how that works we'll be able to save them add new delete and of course check and send for reminders that's what we're going to be building this is the sample I'll be closing this right now and we are going to be saving it from a blank worksheet now keep in mind this workbook itself has that payment reminders that screen you just saw which we're going to be building from scratch however this particular workbook does contain the admin and it does contain the invoice so keep that in mind it does contain all that so we don't need to necessarily recreate the entire workbook we're going to focus on this fully customizable payment reminders okay so the in this particular sheet as with many we're going to dedicate the first two calls columns A and B to admin we'll be using just a few cells of those but not too much so we're going to call that gray we want the first row we can give that call we're going to hit that the title payment reminders with penalties and we'll also give it a little bit of a format here all the way to let's say n or o something large I want to format that nicely with some blue colors just as you saw in the sample so we're going to fill that and I'm going to give it some fill effects we're going to use this blue color many of you like to see these now this is a relatively simple application there's not a lot of macros therefore we can create this from scratch however keep in mind that larger applications with a lot of them will still be it's probably a little bit big for this screen 22 we will have to create those and then I'll go over the code so it really depends on the type of training of course this training is for you so if you want me to create types of training you want to see more from scratch or you want to see bigger applications in which I go over the code let me know in the comments below this training is for you so I always appreciate your feedback and it is your feedback so don't forget to go ahead and subscribe to this channel if you will please click the like button that helps and go don't forget to click the notification icon Bell that is going to help us and help the algorithm so that you get alerted to when I have brand new trainings all right all I did was cover the background while I'm talking away here I'm just giving it a nice color that's going to give it a nice format though so that's kind of the basis of it I'm gonna probably save row two that's going to be for our button set so our button set is going to go right here and I want a list of reminders here so inside let's say right here I'll drop it down to four before let's go ahead and D4 I think GeForce we'll put reminders in here and then our first one will be in let's say row five we'll drop it down reminders and then five will be our first row of reminders and I've got this zoomed in a little bit so I want to all italicize and bold that and I'm going to Center that also what I want to do is I want to have the reminder name here I guess two and three is fine here we'll use reminder name here we've got some the reminder name we'll put the name I want the first field called the reminder name and then I'm going to skip down I'm going to go to row six we're going to call create reminder I want to know how frequent how often should we create it as you saw in the sample below that I want to know what penalty type penalty type so that is going to be a field we need to assess that because there's different penalty types next up in row 10 we'll do the email subject that'll be a full row and then down below we will do a large field for the email message customized message that we want to send okay so for the reminder name now next up I also want to have a status so we'll put in column J here we'll put the status here so that means only those that are active status and there'll be something like active or inactive or something like that okay next up I want to do in this particular row I want to put days after so here we'll have a long merge cell here create a reminder let's see number of days after so I'm going to merge and center this we're going to put days after due date for balances greater than and then the user put in a mount in here so let's say 30 or whatever and then I want the penalty type let's say we'll put a drop down list of all the penalty types those are the same penalty types that you saw in here we're going to be putting a drop down list I've got a named range for that if we go into the formulas name manager and we look down here and we have something called here under penalties this is an offset named range let's move that over and zoom it in you see admin using offset if I tab into that it's going to surround those so penalties is the one that we want to add as a data validation for that field because we want the user to choose from one of those penalties so when I click in here I'm going to click in data and then data validation we can also use F3 on the list to select it or we can just do equals and then I've already copied that so penalty so that's going to be our penalty so we have a drop down list of penalties the email subject is going to be the full row will go all the way to K on that one I'm going to merge and center that and left justify that email message we can use is a larger field and perhaps we can go all the way down to let's say 33 on something like that so we want to be large so that's going to cover that or just uh let's see yeah probably 33. so I'll extend this down a little bit and we'll go to a large row so we get everything covered that way and that way it allows the user to put in larger emails so 33 all the way to from column K 33 and then all the way up to this one I'm going to merge and send to that I'm going to left Justified and then I'm going to use the upper and then what we're going to do is we're going to give it a white so that's going to be our email message also going to give this white okay so for our Fields the penalty type and then what we want to put is how many days overdue and then what I'll do is I'll put the amount so I'll do something like and I'll have VBA take care of this 90 days overdue at and then how what is the amount right so the amount would be something like 30 or and then this will be either dollars or percent so this will be taken by VBA so VBA so as we change this five days or 15 days this will change and this will change so what are we going to base that that information on it's going to be based on here so if it's five days over dude that means 30 days overdue it's a percentage of the balance and obviously zero in this case but we could change it or for a fixed amount we could change that to ten dollars or whatever so this is the information when I select 15 days that I want to apply here so how many days overdue and whether it's 30 or whether it's a percent so in this field VBA will say dollars or percentage based on what I choose here the email subjects so this case we will just put this is the only one that the user is going to be entered in this one everything else is going to be automated so what I'll do is I'll hold down the control and just for those fields that I want the user to be entering I am going to color those and this one the number of days here so which is actually this one here okay so that's exactly what I want I'm going to give those color white and I'm going to format those I'll continue to hold down the control of this and this and what I'll do is I'll drop it down here and we'll just and right click on those fields and we're going to do is we're going to go to format cells once we format those cells I'll give it a border and we'll use a darker color within our theme this one and I'll go all the way around with the solid and then on the left side I'll give the dotted line here okay and then very something very very similar on the label so what I'm going to do is I'm going to hold down the control here on all the labels here and then what I want to do is I want to right justify it so I'm going to right justify it here by just holding that down right here and then also I need to actually merge and center these I want to make these a little bit bigger and this one too it should also Encompass this so let's shrink it down a little bit so we can see everything and then I'll merge instead of this twice that's going to call for that coloring it back white and I want to do the same thing with this to make sure that we cover that okay that looks pretty good I like the way that that looks now what we're going to do is we're going to continue with this so we're going to have the number of days the penalty type also super versions that include that here as well so that looks good that's the drop down list here good so we've got our drop down list let's reformat these particular borders giving them the same borders I want that dark blue to go and then we'll zoom out a little bit all the way around left side dotted line looks good now on these particular ones I want to bring that back to that solid line that's for that larger field here okay that's kind of the basis of our form here we can add some more borderline to keep things integrated here as we move across and then also we want to add some borders here here and here that's going to look really good with our same border so formatting those cells now we can also of course color the Border up here and use this instance up here we're going to do it right with this one here and then just surround your book borders okay that's looking good our forms good let's go ahead and save our work we want to add our button sets and our icons and then we'll get into some VBA but everything looks pretty good just the way we want it I'm going to format these two Fields they're going to be a mount so we're going to set those two amounts and then also what I want to do is this is the number of days we got 90 days after due date for balances greater than and then the 690 days overdue at okay so it's looking really good as we select these things we want to make sure this is customized as we choose a different one now the reminder nameless merge and center that as well left Justified we want to put in that name here so we're going to put in whatever name let's say we want to put in 120 days overdue as we as we add that a unique name for those reminders here's a list of our reminders as I select a reminder I want that particular reminder to load up anything previously saved so what we want to do is we want to add some conditional formatting here all right so we're going to do is we're going to select on a bunch of rows here we're going to go into conditional formatting manage rules we're going to add a new rule we're going to use a formula which can be based on two conditions the first condition is so we're going to use and the first condition is we want to make sure that there is actually a value in column D and any row Associates we're going to put V and there does not equal and then double quotes the second condition is we want to make sure it's based on even rows so even rows only so modular 2 equals zero zero means that even row I'm gonna give it a format of that it's going to have a border it's gonna have a lower left we're going to use the same color that we need so we're going to use a dotted line on the left the lower and the right side here clicking ok I'm going to copy that rule I also want to give it actually a fill color so we're going to fill it with a similar color that we've used previously and that's going to be located right here it's that light color so we're going to use fill effects for that that light blue clicking ok now I also want to create a similar so we're going to copy that Ctrl C then what we're going to do is we're going to go in and we're going to add a brand new rule we use a formula again the only time is difference we're going to make that change to odd row so this is going to be odd rows equals one you can give it a format here it's going to give it that white color and then what we're going to go into the Border we're going to give it the same border that we did that dark color here and then again on the left the lower and the right clicking OK and clicking OK clicking apply to make sure that Excel has the correct Fields applies to Fields D starting D5 all the way to 29 clicking ok now what we need to do is just check here as we add it we can see that automatically the conditional formatting we're going to be adding one more rule in there in just a moment okay so what we want to do now is I also want to highlight the selected row we're going to put some information in here I want to know what that selected reminder row is and I want to put that row directly here so if it's 7 I want to color that row I also want to put whatever selected reminder ID each individual payment reminder does have its own ID so I want to put that ID I'm going to put it in B4 I also want to know what the row associated with that reminder call it database rows what is the row that's associated these reminders are saved in this list here we have the reminder ID the name the status the days the balance threshold the penalty name the subject and the message so we've got some reminder IDs I want those IDs to know I want to know the next reminder ID okay so the payment reminder database row that's associated with that I also want to know the next reminder IDs and the penalty row what is the row that's associated with the penalty the select penalty row okay I'll give those a unique color and some borders so we're going to do is we're going to call those yellow and give those borders all the way around then we'll start adding some information I will left justify that and then what is the selected reminder let's say we have a reminder ID 3. I want to know the database associated with that so that means 3 is on row five here so what I've done is I've created a name brace called reminder ID inside the formulas name manager we can locate that under reminder ID so right here is where a reminder idea is it's an offset formula here and it's going to Encompass all the data so we can use that to determine what row our first one starts on row four so we need to compensate for that so if we want to find out which one so we're going to do equals if air and then I want to look at that I want to use a mash to find the row so we're going to match what are we looking up we're looking up the reminder ID here we're looking it up inside the particular name range called reminder ID and we want an exact match once it's found I want to add 3 because I want to know the row number that's associated if it is not found it will create an error we're going to trim that blank so that means we know that number three is on row five I also want to know the next available as we add them equals if air in case there's no D there could be an error Max of a minor ID I want to know the maximum plus I want to add one available for the next one and of course if there are none that means there's no data we'll just set the default to one and the penalty row what is the row that's associated with this if we look in the admin we see our first one is available on row 19. so if I know the first ones on row 19 if it's found we have a named range for that called penalties as you saw and so what we're going to do is we're going to make sure we know the row associated with that so again we're going to use equals if error we're going to run a match and again I want to match what am I looking up I'm looking up this value and I'm looking at based on the penalties I want an exact match and I want to know what roads Associated so I'm going to add 18. I know our first one starts on row 19. so if I add let's try to get that plus 18 here it's going to set up that row once I know that if there's an arrow I just want to return empty okay all right so that means our first one our 9 90 days overdue our first one five days is going to appear on row 19 and that is correct 19. okay so that's it that's all we need for that saving our work okay let's give this a theme color and I also want to add another one here called variable so both of those are going to take on that theme color so I'm going to format those cells here and then we'll just do border we can do all the way around border using our theme color here all the way around with a full line and also I want to give it a fill effects ing to our theme here and just give it a colors because that's going to be the header of both our reminders and our variables here and here okay and then make sure the font is bold and italic all right so that looks good so now this one I'm going to put in variables and I'm going to Center that also and so we can go in Center that and our reminders will go here so I like the way that that looks now I want to make sure that this also has some conditioner I'm just going to copy that and I'm going to paste it right here because I want this to have a conditional formatting as well and but I want to make sure that it takes on the rules differently so we're going to manage those rules and this one's going to be based on column M so we just need to change this D here to an m and it's going to apply to that okay so that looks good for that one and I'll change the other one for the even rows here and I want our variables to take on that conditional formatting as well we'll be adding some variables and those are going to be used for our email so what are those variables well the first thing I want to have the customer name because I want to make sure that's included inside the emails the second variable that I want to have is located the invoice number here so that invoice numbers can be included in the emails and I also want to know the invoice balance so we'll use brackets around those so invoice balance will be our variable here and we'll use those inside the code I'm going to show you how to do that I want to know the penalty amount so the customer knows the penalty amount so we're going to put penalty amount as a variable in here these will be replaced with the actual values I also want to know how many days overdue so that we can let the customer know how many days overdue their invoice is and lastly we want to know the total do that's simply going to be the balance along with the penalty amount total due okay so that's our last variable and you see how the conditional formatting applies as we add those as we add the reminders they will do that the macro will take care of adding these reminders for us so that looks pretty good I like the way that that looks let's go ahead and add in our payments and add on our buttons so that we can add it so what I'm going to do is I'm going to insert here and then we'll use a shape and I'll just use the rounded rectangle here that's pretty standard shape here and this one's going to be for save reminder so I'm just going to add that right here call that save reminder once I get that just the way I like it I'll go ahead and duplicate that giving it a format based on the theme here you can use this theme here okay I'm going to put it here and here okay I like the way that that looks and then what I want to do is just go ahead and give it a little bit darker color ing to our theme the shape form I'm give it that Shea fill and give it that theme color right here okay so we've got that that looks pretty good and what I'm going to do now and we got save reminder I'll duplicate that this one's going to be for add new so I'm going to place that here I'll duplicate it one more time this will be for the delete and one more time for the check and send reminders so we'll call this add new this will be so we can add brand new reminders with just a click of a button and then I want to be able to delete the reminder along with the warning letting the user know that of course if we delete it it's gone forever so we'll add that and then this one we're going to call check and send reminders as you may have seen from the sample if not no worries okay so check and send reminders let's take a look payment reminders with penalty save reminder add new delete reminder check and Center manage so this is the macro that we're going to send I'm just going to check to see if there's any reminders sent and if they have not been set for particular over the invoices they will go ahead and send them we can now add in our logo so what I'm going to do is I'm going to insert picture and our shapes and icons and I've got all these saved in a folder if you do want to get all these along with any resources along with all the updates that are associated with these applications updates are based on your feedback you would want to join our patreon campaign patreon is a great way to get more content everything for a very low cost and of course it also helps support our channel so I would definitely appreciate that if you can do that I would appreciate it starts at just three dollars a month so it's a great way to support Excel for a freelancer and also make sure that you get tons more content at a very low cost and it's customized just for you all right I've added these logos while I talk away here save reminders and the icon here I should say that okay what we're going to do is we're going to Center and group that and I'm gonna do the same thing here this is a little bit big for this particular icon so we'll set that to 0.18 the rest of them are standing 0.12 I'm going to use the control I'm going to group them together delete the reminders also here Center and group and then we're going to do thing for check and I'm going to make this a little bit larger checks and check and send reminders here so again doing the same thing and grouping it now what I also want to do is I also want to make sure they're all lined up horizontally and vertically so I'm going to make sure they're in line I'm going to distribute them so in the shape format align I want distributed horizontally so that's it then what we're going to do is going to group them together as I possibly adjust these column widths I certainly don't want to adjust the size of those buttons so we want to make sure that we're setting the shape options to move but don't size so that will take care of that and that's going to go for any shape okay very good all right I don't think we're going to use row two so we can just move that up a little bit and so it's exactly the same as my sample that's always a good idea all right so everything is looking good we're ready so first thing what I want to do is I want to do some automation stuff when I make a change to here whether it's loading or not I want to automate this so that means if it's five days overdue I want to say nine to five days overdue at whatever it is so I want to pull this information however many days over there so this will be five this would be 15 let's make sure it's accurate so percent is balanced then I want to change that so I want this information to be updated automatically and how is that going to happen what's going to happen on change event when I make a change to this particular cell G8 that's when I want to automate this information so this could go to percent or dollars based on that so how are we going to do that well it's going to happen with the change events we're going to go into VBA and start with that code developer visual Basics it is their alt f11 pay reminders as you see this is the sheet that we're focused on pay reminders and we're going to look into change event so we're going to go into worksheet not selection change at least not yet and go into change events so what do we want to focus on I want to focus just on that cell g8's the one if that there's a change to that something's going to happen so if not intersection is nothing I want to make sure the ga contains a value so this is going to be changed to G8 not h12 that's just some automated I use Auto hotkey to automate that and we also want to make sure that G8 contains the value range G8 dot value is not equal empty if that happens then we want to do something well what do we want to do well I want to make sure that we have a particular correct item here how do we know if it's correct we know because B6 is an M if they put in a correct information of course our data valve station is going to tell us but also we know that on empty or anything like that B6 is going to be empty so as long as B6 contains a row then we know that it's correct so we can put if range B6 dot value does not equal empty then we can proceed so what is it that we want to do well what I would like to do is I want to pull that row so we're going to mention that I want the penalty row so we'll Dimension penalty row as long I also want to know the type Dimension the penalty type as long is it a percent or is it based on a dollar amount penalty type as string okay so what is that penalty row that penalty row is based on B6 equals range B6 dot value okay dot value that's the penalty the row that's associated with that inside the admin screen penalty row once we have the penalty row I want to see what type is it how do I know well I know that if this is the percentage of a balance I'm going to copy this then I know it's a percent based other otherwise it's a fixed amount dollar amount so we can differentiate because the penalty type is going to differentiate based on that so to do that we're going to make an update using a little if then statement they called if that's going to be located in admin dot range and where is that information located that we want to extract it's located in column n and the penalty row and and the penalty row dot value equals and I've already copied it percentage about then we know the penalty type we'll set that penalty type goes percent penalty type equals percent that's what I want to put in that field percent else it's going to be a mount right so else we can simply put in dollars else penalty type equals dollars because that's the information that I want to store it on Okay so we've got that there so now what I want to do so basically what I want to do is I want to take that penalty type whatever it is percentage and I want to put it directly inside K8 so that's where it's going to go either percent or is going to go directly there continuing on now what I want to do is I want to automate in information additional information Insider so what I want to put I want to put days overdue so this however many days overdue and then the text days overdue and where's that going to come it's going to come directly from M so we could say something like this range I8 is the range that I want to put that in let's double check that to make sure that dot value that's where I want to put it inside that's the cell that I want to put it in inside the payment and reminders located right here in I8 equals and it's a combination so coming directly from the admin dot range anytime you misspell something and you try to do that you know but as soon as you add in the right spelling and you hit the intellisense it's going to come up so that's a great way you can tell that's how I knew I spelled it incorrectly right away okay so it's going to be the admin what's in column M and the penalty row dot value and I want to add something else in I want to add the text the other text so and space days overdue at and that's good enough then what I want to do is I want to add inside whatever's in J so hey what is the ad what is this inside J is it it's a percentage or is it an amount it's going to go in j8 and where is it going to come directly from it's going to come directly from o right here so we're going to add that in here so I'm just going to copy this here and we can actually duplicate it and make the adjustments so but it's not going to be I8 this time it's going to be j8 so inside j8 what do I want to put I want to put whatever's in O and that is going to be the percent or amount percent or amount so we'll put that in percent or amount okay that looks good so now that's in K what do I want to do I want to put in that type K8 is going to take on the actual percent the text percent or amount so equals the penalty type we've already put it inside this particular named range right here so this particular string here penalty type so we're going to put equals set depend penalty type percentage or amount okay so that's pretty clear now the only thing I want to do is I want to format that right so if we're putting in a percentage or a format I want to format this based on the number or an amount and now what do I want to format I want to format this particular cell that's located where the amount is located right here inside here so j8 is going to be formatted either it's going to be taken on the format or it's going to take on a percentage so we want to format it based on the type so range j8 dot value so it's going to be probably an if then so if penalty type is equal to percent then I want to make that change then what's it j8 not the value but the number format DOT number format equals what we'll set that as a percent so it's going to be something very similar to what we would find in percent zero point let's say 0 0 and you could put in as many decimals as you want so we'll do one like 8.5 or something percent so that's the formats can take on else the number format is going to take on a dollar amount so copy be this else the format is going to be equal to we can put something like the dollar you can put whatever currency or I'm putting dollars I put in a pound sign here for the format Dot and then 0 0 for two decimals okay so it's going to take on that format for dollar sign so that way we differentiate and then what we'll do is we'll just clear the additional details we will save our work before running our code and that's going to be based on a change so we can check it now so if we make changes to 60 days overdue we say 60 is already 20 50 so let's double check that for 60 days 2050 that looks pretty correct so let's change it to 50 and make sure that that works inside here just double clicking that will automatically okay so that's good looking good so it changed to fifty dollars but what about if it's a percentage let's take a look we have 120 days is five percent a percentage of balance so when we change this to 120 days here we want to make sure that the percentage update it's now in a percentage format and this change to percent 100 overdue okay at five percent okay so that's looking really good I like the way that looks a little code automatically and now when I load in these reminders I want to load that in but we're going to get to that inside a minute that's going to be on selection change one other additional code I want to add in here is basically the conditional formatting for the selected row so we're going to go into the home conditional formatting we're going to manage those rules and probably just copy the applies to here and then I'm going to click a new rule let's use a formula and this can be based on the row here so B2 equals the row I want to color the selected row so row beating in parentheses I'm going to format that I'm going to do the fill effects here and then what we'll do is we'll just do that darker color ing to our theme that we're using here I also want to give it a white font so that it stands out I want to make sure it's bold so the font's going to be bold and white and I'm going to click OK and click OK and click ok so now you see that row 7 colored row eight and so as we select it that row is going to go in here so as we add them so now what I I want to do is I want to take all the reminders that we've saved that I want to load them here I want to create a Mac that's going to put all the reminders names so it's going to be here so I want to take these reminder names here 30 90 and 60 days and I want to add them directly in here but I don't want to just add those names I want to add the IDS in here and this is going to be hidden they'll be hidden but I want to put the IDS in here and the idea is when I select a reminder I want to know the name and the ID that's located here I want to put directly inside B3 what that's going to do is going to automatically calculate the row that's associated and I can load that on so that's going to happen so the first thing what we want to do is we want to create a macro that's going to actually load that list up and one unique list and I want to put that list right here so I want the reminder IDs and the days that I want to determine how many there are and I want to bring them directly over inside this screen okay so let's write a macro that's going to do that so we're going to go inside the module called payment reminders here and you see I've got some macro the types of macros that are loaded here here that we're going to be covering today so we're going to be doing these and this one I've just mentioned some variables to make things a little bit quicker last row last results row results Row the reminder row and the reminder column these are going to help us as we move through the macros so the first one we're going to be doing is called pay reminder load list this is the macro that's going to determine all the reminders that we've created and we're going to add them in so every time we go ahead and save a reminder it's going to automatically update this list the first thing we want to do is clear any details out inside that so this sheet is called payment reminders so we're going to focus on that she pay reminders dot range I'm going to clear any details out so what is it D5 all the way through a large row but we're actually going to use C5 because that's where our IDs are located so C5 through D and then just use a large row here dot clear content so clear out any previous reminders and IDs so the ID is located in columns C so once we have done that we can then add in the additional information so first thing I want to focus on is the reminder row so with the reminders list that's the list of reminders that we have that's the sheet reminders list is this sheet right here I'm going to run an advanced filter really focused on these two columns here that's all I really need and I want to determine the last rough the last row is less than four we can exit the sub there's no data if there is data we're going to run Advanced filter no criteria all I just want is unique values here reason we want to do this is we can then sort them and work with this data if we as opposed to just copying them from here so it's kind of a nice way to do it it gives you the ability to sort them maybe we want to sort them alphabetically or numerically or something like that so I like to bring them in a different area Okay so we're going to do that with reminders list I'm going to determine the last row what is the last row well the last row is going to be based on column A so if the last row is less than four we can exit this up if it's not then we can move on to the advanced filter okay so what I'm like to do is I want to get the unique names now when we run an advanced filter without any criteria meaning we're just bringing the data over and I just want the unique data I want to make sure that any criteria that got created previously or maybe in another macro gets deleted that's very important so the first thing what we want to do is run on air resume next and delete any criteria that may have been created before so we can do names criteria criteria is the named range that gets created automatically when we create an advanced filter that contains criteria and on air goes to zero if we look in the name manager formulas name as you will see several name range based on criteria so these are all get created automatically when we use Advanced filters this is what I'm deleting and usually there's one per sheet any sheet that we create an advanced filter that contains criteria VBA will create this name range automatically so it is this what we are deleting because when we run an advanced filter that has no criteria it is used and it's hidden which we don't want to use it so we basically just have to delete it okay so that's going to delete any criteria if it exists if it doesn't exist it would create an error so therefore we've wrapped in an honor Zoom next and on our go to zero once we have that what we can do is we can run an advanced filter now this Advanced filter is not going to have any criteria here so I'm going to take this and I'm just going to clear it out no criteria what is the range that we're going to be focusing on we're focusing on just here reminder ID A3 through B3 so A3 through B3 because that's the only one and the last row whatever the last row is and what do I want that I want to bring that information over we can braise this up now we're in like a museum I want to bring that unique over to both M2 through N2 so M23 and 2 is where our results are going to go so I'm going to change this to N2 okay so now what we want to do is we want to determine the last results row so the last results row is equal to based on column M so Amazon last if the last results less than four we have no data so last result so is less than three we have no data right so if it's less than and three we got nothing if it's not then what we would do is bring it over now to notice our first results row starts in row three and our first results row starts in row five so we need to compensate for that difference when we bring it over assuming that we do have data we can then bring it all the way over but maybe we're going to do it sort so why don't we sort that first if it's just one row of data we don't need to sort it it would be nice to sort it alphabetically so other things look in order if last result row is less than four that means we have only one row of data then go to no sort we'll skip down here and go to no sort so that's what's going to happen as long as there's just one row of data if there is data whoops let's put a colon there I want a colon there here okay so if there is data we're going to run a sort so we'll sort I got that automated a little bit to make things faster we're going to update the sheet this sheet is reminder list so I'm going to copy that here instead of tasks I'm going to put it here and I'm going to put it here so focus on that so we're going to clear the fields out do the reminder list now what field are we starting we are sorting it based on let's use the name okay so that name is going to be located right here in N3 n3's word name so I'm going to put N3 that is the field that we're going to be here that's our key field so and three now what is the range is going to be based on M through n so m 3 through n and I want it sorted ascending so that's nice okay so that's going to sort them automatically I'm going to save our work and then what we want to do is once we have it I want to bring that range over go down here I'm just simply going to bring it over into our payment reminder Fields okay so it's going to be pay reminders dot range we know it starts in C is where we want to put that reminder ID C5 through D and the last result row plus two because we need to compensate for that different dot value equals dot range M3 through n and the last results row dot value so bring over unique remote okay very good what we're gonna do is we're going to save our work I'm going to run that code and then what we're going to do is we're going to check for this error so Advanced filter copy to range M that's not going to work and two okay so continuing on okay that looks pretty good our ID right here but I don't want to show this ID I know it exists but I don't want to show I want to hide it so I'm going to go into home I'm going to go into the formats more number formats I'm going to choose custom I'm going to choose two or three semicolons two for numbers three for Alpha in America so what that's going to do is hide it now if I select on the cell we still see it it's located but it's not there okay great so I like that next up what I want is I want to macro when I select something I want all the details of that particular reminder to load up into these fields now I've created something called Data mapping already to help us move things along and it's data mapped here so that means that reminder name is located in G4 if you've seen any of my videos before this is familiar but repetition will help status is located in K4 so K4 you see is the status here k6 is going to be located the greater than amount here so it's all mapped to this k6 G8 and G10 so all I need to do is loop from 2 to the last column what is that last column that last column is eight I'm going to loop from 2 to 2 8 and as we then bring it so that's all we're going to do on load and simply look determine the row bring whatever is found in that row and put it into G4 whatever is found in the row and put it in Decay for and just Loop through that automatically so that's what we're going to do on loaded the mix things a lot quicker okay so the first thing what we want to do is write the macro before we run it save it and load it so the first thing we can do is using save and the second we'll do is load it so when we save it we need to make sure that it has a few things so focus on with pay reminders that's the sheet that we're focused on notice I always put that dot just to make sure I spell it right when I put that in the cell intellisense comes up I know that I've spelled it right if I spell it wrong and nothing's going to happen Okay so to do that now what I want to do is I want to just make sure on the first one when we add that save I want to make sure that it contains at least a name so I want to make sure that G4 is not empty if G4 is empty I want to let the user know if dot range G4 dot value equals empty then prompt the user then prompt the user to let the user know message box please make sure to add a reminder name okay exit sub okay so now what I want to do is I want to determine is it a brand new payment or is an existing one how are we going to know the difference we're going to look directly to before we know that if B4 contains a value we know is existing however as soon as we clear that reminder ID we know before is empty so we're going to determine that okay so B4 is going to let us know if it's new or not so to do that we are going to make the update ingly based on that pay row okay so let's write that up right now if dot range B4 dot value equals empty then it's a brand new one else it's an existing else existing so we know if it's a new one we need to assign a brand new row new payment reminder so we need to sign a row so what is that row going to be so the reminder row that reminder row it's gonna be the first one available on the payment list equals reminder list dot range a 999 dot end Excel up dot row plus one first available row once we have assigned that row I want to then add in that reminder I need the next available reminder ID so that next available one is going to be located right here in B5 I want to place that directly inside B3 so dot range B3 dot value equals dot range B5 dot value okay so once we have added that we also want to add that same one inside column A so that's going to be next reminder ID I want to take that same one and I'm going to put it directly inside here reminders column A and the reminder row and the reminder row dot value equals whatever's in B3 B3 okay so we have that available to us that's going to be the next reminder ID same item okay once we have that what else do we want to do well that's it for the new ones what if it's an existing if it's an existing all I want to do is extract that reminder wrote directly from B4 so the remind finger Roots the only thing we need to do for example render row is equal to whatever's in B4 I'm going to call that existing reminder row so that's it everything else is going to be regardless if it's a new or an existing so what I want to do is I want to save it for using a loop for a reminder column which is a long variable equals two to eight this is what I was discussing before next reminder call so what do we want to do well basically I want to take when we're saving it I want to take whatever's in the cells dot range and I want to place them directly inside the database so where are those cells located it's located inside the reminder list that dot cells we're using cells because both calm and the row are variable Row one and the comminder column dot value so that's the cell right there dot value so that's cell G8 whatever it is K8 is going to be the value so whatever that is going to equal we're going to put that directly inside the database because we're saving it so where's that database it's the reminders list dot cells whatever row so we've already set them reminder row what is the column reminder calm dot value equals so that's it so basically whatever's located in this cell it's going to be put directly inside the database add data to database so that's all we have to do for the loop relatively easy there fix this here okay so I also want to make sure that when we added we're using large amounts of data and we're saving it let's see what happens I want to wrap the text right if I have a large amount of data I don't want it to do this what I want to do is I want to wrap the text so basically inside VBA I just want to do that so we can do that with the entire row so we can do that inside VBA simply something like this reminder list dot range then we can just add the row and reminder row and colon and the reminder row dot wrap text equals false okay so that's going to just keep that from growing we don't need that in the database it's not important okay so very good so we've added that in and now what I want to do is right after we save it I want to run this macro in case it's a brand new one or in case we've made any change I want to run this and the reason that's important is if we add a new one we want to make sure it's included inside the list so we're going to do end with okay so what I'm going to do is I'm going to save that okay so very good now let's take a look at what we have here we already have data 1 3 and 4 30 90 and 60 day okay so right now we're on 120 days if it's a new one or an existing one so let's go ahead and make this change this to let's say 120 days I like that 120 days so we're just making the updates here and then let's change this to 50. so this is going to be the existing one we're changing the existing one and let's add in I'm just going to copy whatever we have over here into the subject a little bit quicker so I'm going to take that subject and I'm going to paste it directly in here I'm also going to take whatever's reminder located here Ctrl a control C back inside here I'm just going to paste it in here okay so what I want to do is I want to left Justify both of these holding down the control and I also want to make sure that this particular one is upper all right so vertically up okay so I like that now what I'm going to do this is an existing reminder here on row five I'm going to save that so I want to take this button here hold the control and assign that brand new macro and we're going to go all the way down here to payment reminder and then save and update and select that that's going to be the one we're safe clicking save is going to update that okay so let's take a look and see how we did there 120 120 everything got saved inside the reminders list okay we do need to wrap the text up I'll make sure that that's right that looks good so this is the one we saved and then we'll just update that and then change that I like that okay good all right so that's looking good it's saved 120 120 50. everything got saved into row five just as we thought perfect so I like the way that though it's now saving but what I want to do is I want to load it now I want to bring the information back into this form when I make a selection on one of these I want to take whatever's there and I want to bring it back into this form so how are we that's going to be on a macro called sub payment reminder load so when I make a selection I'm going to take whatever values here that's the idea I'm going to place it directly inside B3 I'm also going to take whatever row selected I'm going to put it in B2 then I'm going to run the macro to load it so the first thing we want to make sure that we have an accurate row located here in B4 so first of all we're going to do with pay reminders I want to clear out some cells that's very important that we're going to clear out some cells and with okay so when we have that I want to make sure that we are going to clear so what are the cells that we need to clear out first thing would before we clear out we want to make sure that we have the correct row Associated first thing I want to do is clear out all the cells except I don't want to clear out anything Associated here I don't want to clear out B3 and I don't want to clear out the selector of B2 so but I want to clear out all these cells so what are those cells so the first thing I want to do is dot range and then we're going to Cloud Associates those G4 through H4 I also want to clear out K4 I also want to clear out G6 and k6 as well as G8 through and I'll bring this down here H H okay so let's take a look at that so we can get the right cells bringing that down here so we can take a look at both okay so G8 through h8 and then I also want to do K so we'll take a look here K8 we also want to do so comma K8 also we want to make sure that we're going to bring inside G10 through K10 G10 through K10 that's going to be for our email subject and then lastly the message here so with that message is going to be g12 all the way through k33 so we need to clear out those through k33 dot clear content so clearing out those fields clear out form Fields okay once they're cleared out we can then determine the row so we can check that if dot range B4 dot value equals MD then message box please select a correct reminder although it should never happen exit sub we certainly need before the value so that reminder row is going to be based on B4 equals dot range B4 dot value once we have the row all we need to do is just do the data mapping just like we did here except it's going to be in Reverse so I'm simply going to copy that and then reverse it up I'm going to paste it down here except this time we're taking data from the database and bringing it into that so taking the data from the data is bringing it into the form here so all we need to do is reverse this and put that equals here so bring data from database so add data to form field from database okay so I like that there so basically it's coming from the database here into the fields here so once we do that that's pretty much it that's all we have to do for the load I'm going to save our work as we should and I want to add this macro so I'm going to copy this macro and then we're going to write a little code when do we want to happen when we make a selection change on D5 all the way down and I want to make sure that there's actually a value in from D so that's going to happen on selection change when we go into the payments right so pay reminders this is the worksheet selection change this is where we're going to write our code first of all if Target dot count large I'm just going to write this code it helps keep errors is greater than let's say one then exit sub that means if the user selects a large number of cells it's just going to exit Okay so if not I want to make sure again it's going to focus on D5 starting at D5 all the way down D5 through a large number d99 that one comes to all ones is nothing and range D and the target dot row dot value does not equal empty then we want to do something what is it that we want to do I want to make sure do a few things first thing I want to put that row whatever row we've selected I'm going to put that directly inside B2 so taking that Target that's going to trigger a conditional formatting so range B2 dot value equals the target row okay that's going to trigger trigger conditional formatting next up I want to take whatever ID and I want to put that into B3 so range B3 dot value equals where is it located it's located range C and the target dot row dot value that is our reminder ID reminder ID once I have the reminder ID I can then run the macro run macro to load reminder that's all we need for that relatively simple that okay again always saving our work before we move forward especially when we're clearing our contents selecting on sale o
Show moreGet more for payment reminder mail format for export
- Get Better Proposals Pricing for Communications Media
- Better Proposals Pricing for Construction Industry
- Better Proposals Pricing for Financial Services
- Better Proposals Pricing for Government
- Better Proposals Pricing for Healthcare
- Better Proposals Pricing for Higher Education
- Better Proposals Pricing for Insurance Industry
- Better Proposals Pricing for Legal Services
Find out other payment reminder mail format for export
- Streamline your workflow with enterprise document ...
- Insert digital signature into Excel with ease
- Transform your workflow with electronic document ...
- Create signature in spreadsheet for seamless document ...
- Unlock the power of enterprise digital signature for ...
- How to add my signature to an Excel file with airSlate ...
- Experience the best free PDF sign tool for Windows
- Unlock efficient e-signature solutions for human ...
- Download our free digital signing program for seamless ...
- Explore the ultimate online signature solution ...
- How to create e-sign in Excel for seamless document ...
- Put signature on spreadsheet effortlessly with airSlate ...
- Explore top e-signature service providers for seamless ...
- Put signature on Excel spreadsheet seamlessly
- Generate electronic signature application for seamless ...
- Easily insert signature in Excel Android for seamless ...
- Transform your workflow with our mobile document ...
- Unlock your potential with the free PDF editor with ...
- Get your free download for signature reader software ...
- Discover free digital signature applications for PDFs