Idiocy abounds on the Internet. I went looking for a template for an Access 2010 report that would let me print #10 envelopes. I found shit. One site suggested “use Word”. Another site gave a set of instructions for making a custom label that didn’t work.
So I just did it from scratch. Note that most of the below assumes that you already know how to use Access. I’m not going to explain basic database crap for the uninitiated — go buy a book and learn it like I did 20+ years ago. (Yes, I hosted the first Microsoft Access discussion list on the internet, back when I worked at IUPUI, and I still host it where I work today. And I did this because the docs for Access 1.0 pretty much were the sux0r.)
1) Create a blank report
2) Page Setup/Print Options, set margins Top 0.25, Bottom 0.375, Left 0.25, Right, 0.5
3) Page, set Orientation to “Landscape” and set Size to “Envelope #10”, which you will probably have to create on your printer (in its property sheet, there should be a way to create custom paper sizes). I’m not going into that* because it’s not likely to be the same for all printers…but also note that some printers won’t let you define custom sizes, either. I use HP and Brother printers exclusively so I don’t have that problem. I leave “Source:” set to Automatically Select because I think I specified the bypass tray in the custom size dialog. Anyway it makes it require that you stick an envelope in the bypass tray.
4) If you are using a printer other than your default printer for envelopes, choose it (“Use Specific Printer”) in the “Printer for Envelope #10” box.
That takes care of the overall formatting.
5) Create a text box** for the return address in the upper left-hand corner of the report. You can populate that with something like
=”Your Organization” & Chr$(13) & Chr$(10) & “John Doe, Secretary-Recorder” & Chr$(13) & Chr$(10) & “123 Main St” & Chr$(13) & Chr$(10) & “Anytown, NY 00000-0000”
6) Create another text box (or text boxes; however you decide to do your data pull is up to you) at Top = 2″ and Left = 3.75″. This makes the Width = 4.8875″ and the Height = 1.4063″ (if you are using a single box, which is what I do; I use a query to build a variable that populates the box).
7) Voila you’re done. If you want to call this report from a command button on a form for the record currently being displayed (i.e., print a single envelope for a particular customer), go to the On Click event for the command button, choose “Code Builder” to build the code behind the event, and write your code there. In my case the code looks like this:
Private Sub cmdPrintEnv10_Click()
Dim txtSQL As String
txtSQL = “SELECT * FROM tblMembers WHERE Id = ” & txtID.Value & “;”
DoCmd.OpenReport “Envelope #10”, acViewNormal, txtSQLEnd Sub
txtID is the text box on my form that holds the customer’s unique ID. Note that if you prefer to preview the envelope before it goes to the printer, you can change “acViewNormal” to “acViewPreview” in the code.
Good luck!
_________________
* Other than to point out that a #10 envelope is 9.5×4.12 inches in size and prints in Landscape orientation. At least on an HP or a Brother laser printer.
** Be sure to use a text box, not a label, because labels don’t let you use carriage return/line feed combos [Chr$(13) & Chr$(10)], or for that matter, anything that you’d try to generate on the fly. Text boxes are much more versatile.