Microsoft Access Letter Writer. Store, Print, Email Correspondence Without Using Word.

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video we're going to build a letter writer inside microsoft access we're going to see how to write print email and store letters in your access database without using microsoft word carter from kansas city missouri one of my gold members writes my office sends a lot of written correspondence to our clients we can't just use email is there a way to store all of this in my access database i know you frown upon attachments so storing word documents in the customer's record is a bad thing right yes generally i recommend against storing anything in your database word documents pdfs images any of that stuff access databases are designed to store data information but not necessarily to store objects like word documents and stuff like that i've got whole other videos that discusses this topic in detail but to answer your question you don't want to do that so what i'm going to suggest is unless you need some crazy options that are in microsoft word why not just use access as your letter writer you can write all of your correspondence in your database print it right from there email it right from there if you want to and it's all stored right in your tables so unless there's some specific need that you have to use microsoft word don't use it if all you're sending is basic correspondence written letters welcome information collection notices that kind of stuff just do it all in access let me show you how i would do it before we get started a couple of prerequisites if you haven't watched my free blank template my tech help free template video go watch that blank okay then the contact manager where we learn how to store contacts for each customer that'll teach you basic relationships too go watch that one and go watch invoicing only because i show how to set up a basic report the way that i like it even if you're not going to be doing invoicing go watch that video too these are all free they're on my website they're on my youtube channel go watch these and then come back here so go on go watch them go okay so here i am in my tech help free template and by now you know what this is and how i built it we've got our customer list right open this up we've got a customer form in each one of our customers we've got contacts and we've got orders all right under the orders we've got the invoice report that's what i wanted to learn how to set up that report okay now in the contact manager right now we're just using this for every time we talk to them on the phone maybe or if they come in and we have a chat called about a job came in for an interview and there's notes down here we can use this to store our correspondence too it's not just necessarily for tracking every time we talk we can put whole letters and written correspondence and stuff in here so what i'm going to do is redesign this a little bit so it's better for writing letters let's just make it a little bit bigger right let's make this a little bit larger slide this out like so i'm going to move the follow-up up here it can be in the form footer or the form header it doesn't matter it's still bound to the record that you're on okay and uh you can make the description a little bit bigger if you want to notes we'll make this larger because we're gonna be writing our letter there in the notes section maybe a little bit taller too and uh since it's gonna be a letter writer now i'm gonna put it back to basic white it just looks more like you're writing you know correspondence on a white field like that maybe a little bit bigger than that let's go down to about there okay looks good all right close that save changes yes open it back up again all right now we come down here and we'll put in here collection letter all right now you just come down to the bottom right dear who am i on let's see here oh that's me okay so dear richard it has come to my attention that you owe uh ninety dollars on your account please pay now you have to excuse me i just spilled coffee on my laptop while my dog spilled the coffee on my laptop yesterday and so my space bar is sticking i got a replacement coming okay and then cordially you want to leave room for a signature there that's fine collections department or whatever department there you go okay so this is now where we're going to be putting our written correspondence and you can make this bigger if you want to if you want to make this you know make this this big let's see here so the whole thing fits in there you can make a separate form for this too if you want to you can make a small one that's formatted for just contacts and make a bigger one for writing letters and whatever you want to do that's why you're using access right so you can build your own database all right but if i click on collection letter now there you can see the collection letter now i want to print this so we need to make a report out of it but before we make the report i'm going to make a query because i'm going to include some other fields on the printed correspondence right i want the customer's name i know i typed richard there but i want first name last name i want their address on the report right so let's make a query that's got all of the information that i want to put in the report together in one spot so create query design and that came in messy didn't it all right i hate that all right so i'm going to bring in the customer table and the contact table let's do contact first and then customer okay let's bring in the contact id let's bring in the notes because that's the letter that i just typed out right let's bring in the contact date we'll use that for the date on the written letter so if you print it up again in the future it'll put on there the date that you actually printed it the first time right you'll know when you sent it too okay now the customer name i'm gonna put first name and last name together i'm gonna use string concatenation i should have put this in the prerequisites right string concatenation that's how you put two or more strings together into one all right if you haven't watched that string concatenation video if you've never done that before go watch that now pause this one come back all right so right here in this column i'm going to zoom in shift f2 i'm going to make this cust name my customer name is going to be colon first name and quote space quote and last name okay that's how you do concatenation i'm gonna hit okay let's just see what you got let's run this guy right real quick all right there we go i'm seeing all there's the customer name now it'd be nice for my report if i only saw the one contact that i'm on i don't want to print all of these out right i just want to print out just the one okay so to do that we're going to put a criteria under contact id and that's going to be equals let me zoom in again shift f2 that's going to be equals forms exclamation point contact f exclamation point contact id that's how you get the contact id off the contact form that's currently open okay again i should have probably put this in the prerequisites too right there's another video for you to go watch get a value from an open form all right you got a format's open you want to grab a value from it like the contact id okay okay so i'll hit okay down here and now when i run this you can see i'm only seeing the contact that's open right the one that's open here in the background just this one the collection letter okay back to design view let's save this guy while i'm at it real quick ctrl s to save let's save this as my contact letter q contact letter q and now let's put the address fields together now address one is just going to be address i'll come right here let me close this box i'll come right here and go address one colon is just address if you have an address two add that i didn't bother adding an address to in mine i usually put if it's a two line address i just put it together in the same box i don't care for me address two is going to be let me zoom in again these are really small to see address two is going to be city ampersand and a space and state and space and the zip code hit okay and then address three is going to be the country so address three is just country okay now if i run that you can see i got all the address lines right here customer name address one address two edges three address three is blank for me because in my database i'm in the us i don't bother putting us in the country field okay so this is all set to go this has the one record in it that we want to print and it's based on the open forum back here so now i can make my report off of that query so let's go over to our template over here i got this blank r that we set up in the invoicing video right i'm going to copy and paste this guy copy control c paste ctrl v i'm going to call this my contact contact letter r and let's design this guy okay now it already goes out to eight inches i think i got margins set to narrow let's see yep narrow margins so it's a quarter inch on each side you can change it if you want to so eight and a half's the actual physical edge of the paper you want to go just shy of that i don't think we're going to need a report header inverter so we could turn those off for this one right click turn report header and footer off we will need the page header and footer though i want to put the uh the return address and my company logo on the top of each page all right so let's go to report design just grab a label i'm going to put the return address over here all right computer learning zone shift enter one two three main street shift enter fort myers florida three three nine six six okay i'm going to go to format let's make that a little bit bigger maybe 18 point and right justify it right align it i should say i always say right justify it's right on line okay let's make it black so you can actually read it change the font if you want do whatever you want right let's make this access learning sound okay let's put the company logo over here now normally i preach against putting images and graphics and stuff like that in your database i'm against storing them in records okay if you've got like a customer profile picture or product pictures don't store all of that those tons and tons of pictures but if you've got a company logo and it's a nice little small graphic file feel free to include that in your reports that's in the design of the database that's different than storing images inside the records of your database okay so i got a nice little tiny access learning zone logo i'm going to put right there so let's go to report design insert image browse all right i got a nice little tiny guy right there look it's only 27k it's tiny all right pick that guy hit okay put it right there there we go see there's nothing wrong with that okay don't be storing you know 5 000 different two megabyte gif files in your database that's what i'm talking about when i say don't store images and files in your database like your word documents carter right you don't wanna if you've got you know five thousand customers and each one's got ten letters that you've written and each word document is three megabytes that's a lot of space access isn't designed to store that kind of information okay okay in the detail section so we're gonna put the information for our letter and let's go to the add existing fields and oh i didn't bind this report to a table yet did i let's come over here open up this guy the reports properties go to record source we're going to bind this to the contact letter q that we built that query that has the one record in it okay now i can add existing fields let's start off with the customer name and the address just grab all four of those things together let's click on the first one shift click on the last one click and drag and drop right there now we can get rid of these right goodbye we're going to make these wider like that and you can snuggle them up next to each other if you want make them a little bit bigger if you want whatever you want to do now i keep this one around because it's got the format that i want so i'm going to double click on the format painter and go format format format format because by default it brings in that that border around it and all that and makes it gray no i don't want that now i can get rid of you goodbye it's the only reason i keep him around slide this over here like so wherever abouts you want the customers to address going and you might have to kind of guess where you want it i like to line this up i use window envelopes right i got window envelopes that are printed with my return address on them and i three-fold the paper and i stick it and i want to get that address right inside that window so if i save this now if i go right-click print preview right that might have to go down a little bit lower maybe down to about here you get the point right you gotta you gotta line this up exactly where and you can measure it if you want to or you can just kind of eyeball it and guess that's up to you don't forget to include your margins when you're measuring them all right then and this here is an inch here's another inch you got a quarter inch margin on the top you'll get it though you just gotta print a couple and waste some paper okay all right back to design view let's save what we've got going on here save control s next up is the contact date at existing fields we're going to need the contact date and the notes let's bring these two guys in drop them right down there and we don't need the id on this so we'll get rid of those labels all right contact date's going to go next right about there and notes is going to go below that slide that down a little bit contact date on on printed correspondence i like a nice long full like full length uh date i don't want just you know one slash one slash 20. i want wednesday february 14th 2020. i want to look nice it's written correspondence right what does it look like right now right click print preview yeah that's what you got there and we got our borders back on there that's the default but i don't want that on a printed correspondence right so first let's deborder these things click click ok let's left align this oh nope format left okay and let's put a custom format on this date let's go with uh dddd that is the day day of the week spelled out like wednesday comma space my spacebar is not working again all right the full month name mmm space the day like 20th right dd comma space yyyy tab now access puts little quotes and spaces around the commas there but that's the format code that i'm using right there okay see the day month day year that is all covered in my which video let me go see well i cover it in a bunch of different classes a bunch of different videos like date add date diff that kind of stuff but here's all the codes i'll put a link to this down below in the link section the date codes all right but if we take a look at what we got now you can see that that's pretty right monday september 20th 2021 see okay and you can see there's our body right there all right now we've got to make that notes feel bigger close that slide this all the way over here all right horizontally it'll go as big as it goes there's a can grow can shrink property now by default each of these fields if you go under format find can grow can shrink where you can grow can shrink is both set to yes that's fine okay there's also a can grow can shrink for the section too right there all right by default it's yes can grow no can shrink so it can't get any smaller than that but it can get bigger if it needs to and that's fine okay so save that let's take a peek again there we go looks good right want to put some stuff down here in the page footer all right design view let's come down the bottom page footer i like to put like a horizontal line going across let's grab the horizontal line tool right there click and drag right about there looks good maybe some additional contact information down here right something like this all right uh i don't know two three nine five five five two two two two uh you know amicron gmail.com whatever you wanna put down there and then we'll format paint to get rid of that gray maybe align that to the right see i wasn't clicked on it there we go that was clicked on you no go back there okay want to put page numbering over here sure why not grab one of these boxes or whatever copy paste slide it down here right there all right open it up come in here name we'll just call this pgnm whatever page number the control source is going to be equals page ampersand of ampersand pages just like that that puts the page number there and the total number of pages next to it here i'll show you what that looks like save it right click print preview there we go right one of one and your contact information over there looking pretty good now right this will get as big as it needs to get if you've got more stuff in there let me close this let me uh just copy and paste this here we'll just go copy paste paste face paste all right now if i go back to the we got to be on this one remember now if i go print preview this guy see it'll get bigger okay looking pretty good okay now we need a button that we can click on down here to just quickly easily print this i don't have to go back to my navigation pane and find the report right on a button and click on to print this so let's do that but first a word from our sponsor who's the sponsor well that's me if you're enjoying my videos check out my access expert classes expert five six and seven do a lot more with this letter writer that we're building in this tech help video expert fives over an hour long we'll set up something similar to what we're building in this video there's some extra additional features in here though level six takes it another step we do mass mailing so instead of just sending correspondence to one person at a time we send it to a whole bunch of people a collection letter to everybody who owes you money we'll have different collection letters people that are less than 30 days late get one 30 to 60 get another more than 60 get a final reminder right you could specify the dates that you want to send them out for people then in lesson seven will create different groups let's say you want to break your customers down into what they're interested in right hardware service software training whatever they will be able to say okay i want to send this particular letter to everybody in the hardware and software groups and then we'll print the letters and the envelopes so that's my access expert five six and seven classes check them out on my website there's the link i'll put it down below in the link section too okay commercial over i gotta i got you know gotta put a little i gotta put a plug in there here and there right okay let's make our button right click design view i'm gonna make some room down here for a button put it right there now the wizard does make this button for us that's nice and easy here's the button drop it there this is a decent wizard i don't like a lot of the wizards i do like this one report operations preview a report you can just go right to print if you want to send it right to your printer i don't like doing that i like to preview it first preview report next which report contact letter r next i'm going to put on here print report i know it's preview but the user is not going to know the difference print report next name the button print btn or whatever you want to call it and then finish okay let's slide you over there and close it save changes yes go to contacts pick the collection letter print report there it is pops right up you want to print it you hit the print button right there it goes to your printer okay want to export it as a pdf file click right there now there's one more thing i want to teach you real quick actually there's two more things i'm going to teach you i'm going to teach you one more thing about this then we're going to do a bonus which is sending this by email if you make changes to this let's say i come in here and get rid of all this stuff here all right delete all that okay so that's all we got left now all right notice the record is still dirty step pencil that means this record is in the middle of being edited and those changes have not been committed or saved to the table yet so if i hit print report now look it's still got all that extra stuff that i just removed because this is pulling off the query which is reading the table but the table hasn't been updated yet so we have to refresh this record before we print the report all right and you can do that by either leaving the report and coming back to it and then hitting that now it's been refreshed now you can see there's only a little bit of stuff there but that's a pain so what i want to do is i'm going to add a refresh command to this button how do we do that okay now normally i preach vba i love vba go watch my intro to vba class it teaches you all the basics of programming in vba it's real easy i love it but this is a case where the button's already made it's a nice simple button we only have to add one little command so let's right click design view right click on the button and go to build event it's way up top here let me move this so you can see it here sign that up right click build up it's almost off the screen a little bit more get up there okay right click build event there it is okay build event brings up the macro builder okay this is what the wizard actually creates for us it's an open report macro all right we're going to add a command down here where it says add new action and all it is is literally just refresh that's all all right it's run menu command refresh but we have to make sure this runs before open report all right so i'm going to close the action catalog over here all right see this little red uh red yeah see this little green arrow there it says move up click on that that will move the refresh command above open report we have to refresh it first then open report all right save that close it close this save changes yes all right ready contacts collection letter i'm going to come in here and put a bunch of x's in there okay notice the record's still dirty i haven't left it yet print report it refreshed it and there's my line of x's see that that's important gotta refresh the data which basically saves it to the underlying table before you can open up that report or another form okay that's something you gotta do okay now carter i know you said that you don't rely on email but i'm sure all the good people at home would like to know how we could very easily email that now i've got another video where i cover sending email to microsoft outlook using access and it'll actually just send the text but since we have a nice printed report here we can send this as a pdf attachment to an email how do we do that well let's close this one more button there's a wizard for this one too all right let's go to the button guy right there here over here okay it's report operation it's mail report why they didn't make it email report i don't know they should have it should be email report but they made it mail report which is stupid all right next contact letter next all right we're going to email report next all right email button is the name of the button and then finish now same deal we're gonna have to put the refresh in there but let me show you how it works first close it save it open it up collection letter hit the email report button all right now first thing that comes up is send object as what format do you want to send it as i'm going to teach you how to get around this in just a minute also i'm going to mention you may get the outlook security warning popping up at this point and i cover this in my other email video i'll put a link to that down below if you get the message up let me see if i can find a picture of it okay here's my other send email video and i talk about this let me get up yeah let me open this guy up here all right if you see this guy right here the microsoft outlook security warning that says a program is trying to send an email message on your behalf this is just outlook saying hey something's trying to send an email to me you may or may not have allowed this are you sure you want to let this happen well you can circumvent this window appearing by updating your windows defender okay windows defender is an anti-virus program that comes built into windows windows 8 9 10 11 probably when did i just say windows 9 windows 8 10 11 i'm sure is going to have it but you can go down to your task bar and then go to security and then just update your virus profile and it will it will get rid of that message until the next time there's an update available so like once a month you gotta update your virus definitions but as long as your virus definitions are updated you won't see this guy okay okay i talk a lot more about this in the send email video i'll put a link down below go watch that if you want step-by-step instructions okay but for us right now i'm going to pick pdf hit ok it'll make the attachment there it is okay now it puts you here and it wants you to type in who do you want to send it to type in a subject line put something down here okay it's working but i want access to fill all this stuff in for me okay so let's cancel this and let's edit this button so right click design view right click this button go to build event again and now here we are inside the button you'll see all of the object options in here the parameters okay you can see this report that's fine the contact letter r the output format we're going to drop this down and pick pdf you can send it as a text file you can send it as an excel sheet you can send it as html i like pdf all right the two assuming you have the customer form open behind the contact form you can go equals forms exclamation point customer f exclamation point email because we have their email address on that form okay subject line let's make this the description up here right so in here we'll just put equals description like that about the description field message text you could put in here please please see attached right edit the message means do you want outlook to pop up that send window or do you want to just send it right off the bat you just click the button and the email just goes out i like to open up the send window if i'm doing individual correspondence if you're doing a mass mailing note turn that up set that to no okay we also need to put that refresh command in here because we got to refresh the data before we create oops someone just beamed in we have to refresh the data before we can generate that report so put the refresh in there and then move it up to the top and again this would be two lines of vb code i really prefer vb but since the macro is already made we'll stick with it all right save changes yes close this save changes yes one more here we go contacts collection letter let me get rid of this stuff here delete and email report here it comes boom look at that it grabs the email address off the customer form because the customer form is open back here right it's got collection letter as the subject there's my attachment it's got please see attached down here you can put whatever text in there you want all right if you want to click on this it'll open up the pdf there it is looks good all right i'll close that and now just hit send and there you go that's how you do a basic letter writer in microsoft access you can store all the correspondence is stored right in your contact table you can print it you can email it and you don't have to use any other programs you don't have to use word right you can do all everything right inside of access if you want to learn more in the extended cut for members i will show you how to create letter templates these little things here where you can say dear first name it has come to my attention that you owe balance do we'll include merge codes i'll do two i'll show you a first name and balance due and then when you pick them from a list down here hit the load button that'll pull that information up into the letter fill in first name fill in balance do and you're all set then you can do different parts too if you have document parts certain paragraphs that you use all the time a closing paragraph for example a greeting all right pick that pick closing boom it goes right up in there do that then you print it email it you go and you're on your way 16 minutes extended cut silver members and up get access to all the extended cut videos gold members can download these databases how do you become a member click the join button below the video after you click the join button you'll see a list of all the different types of membership levels that are available silver members and up will get access to all of the extended cut tech help videos live video and chat sessions and more gold members get access to a download folder containing all the sample databases that i build in my tech help videos plus my code vault where i keep tons of different functions that i use platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses these are the full-length courses found on my website and not just for access i also teach word excel visual basic asp and lots more but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like this video please give me a thumbs up and feel free to post any comments that you have i do read them all make sure you subscribe to my channel which is completely free and click the bell icon and select all to receive notifications when new videos are posted click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link to join my mailing list now if you have not yet tried my free access level one course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just one dollar and it's also free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there click here to watch my free access beginner level 1 course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com you

Channel: Computer Learning Zone

Views: 3,311

Rating: 4.9615383 out of 5

Keywords: microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, letter writer, Me.Refresh, SendObject, Print Letters, Word Processor, Microsoft Word, Written Correspondence, Templates, Generating a Letter, Mail Merge, Merge Codes, Email Attachments, Send Attachment in Email, Send Report in Email

Id: QHQYqg8chuc

Length: 32min 46sec (1966 seconds)

Published: Mon Sep 20 2021