Print envelope using selection from listbox

  • Hello excel gurus,


    Today, I'm in need of some code tweaking to print out envelopes based on the selection from a list box. I have created code to do this but as is my luck, it doesn't seem to want to work. As you'll see in the code, I'm trying to call the word.application / word.document procedure. The user will select as few as 1 line in the listbox to as many as all lines to print envelopes. I will actually need to do this with 2 different size envelopes but that will be 2 separate procedures. If I can one to work, I'm confident I can tweak it to work for the other. This code below uses a "Size 12" envelope and the second would have a "Size 14" envelope. Which procedure (envelope size ) that is used will be determined by which check box is marked as "True". I will eventually, before shipping, enable or disable one or the other of the check boxes based on the selection. Below you will find the code I created. I will also upload a small sample book for testing. I will do my best to answer any and all questions regarding my needs and As always, thank you for the help and I look forward to reading your replies.


  • I wouldn't use the RowSource to load the ListBox.


    The data is in an Excel Table, so you don't need a Named Range. I have set a variable for the Table and used it's DataBodyRange to load the data, using the Listbox's List Property which is better than RowSource for this. I also, would not use the Activate event, I think the Initialize event is more suitable.


    Why have you got the first line of the Table empty?


    In your Print Button why are you using a loop to find the Row, that can be done with the ListIndex of the ListBox. Does sAddr work by simply selecting the Row? I would think you need to build the address by taking each cell value.


    Also, why is word necessary, I'm sure that you could print to an envelope from Excel, but I would need to check.


    I've made some changes to the code for now

  • Hello Roy, to answer your questions with a simple answer, nothing is set in stone so if you have better ideas, please, by all means, show me. I'm still learning how to build these things with VBA and have learned SO MUCH from all of you so I encourage and welcome any changes or suggestions. And yes, the intent was to use the sAddr to select the row. If I could simply print the envelope from excel without using word, I'd rather do that but I didn't think that was possible. I'm sure I could "build the form" to print it but because this may be usable by more than my current customer, I figured being able to use the "Size" object would be better in the long run in case a different size envelope is used. I'm also thinking I can make it print "Form Letters" in the future. It's a fairly simple program that doesn't do a whole lot, yet. I'm leaving some room for expansion and different features. Any and all suggestions and changes are welcome, all I ask is for you to "somewhat explain" what you change so I know why and what I'm looking at. Thank you!

  • I just reread your questions and see that I missed one. The first row of the table may be empty only because the "Add" button may have been clicked. The way it is right now, an empty entry can be put in and also, that entry goes in at the top rather than the bottom. I haven't set it up yet to stop an empty entry from happening. That will be one of the things I know I need to fix.

  • OK Roy, we're getting there! lol... as you can see, I've laid it out as it needs to be seen on the envelope. I know there has to be a better way instead of calling ".List" for each item but the vbCR is needed I'm sure to put everything in the correct format. So if there is a better way to build that "sAddr =", I'm open for suggestions. Nevertheless, what is now printing on the msgbox is exactly what I need to print on the envelope. My next question though would be, I need to check to see which check box is checked. If it is the "Print Absentee Ballot" check box that is checked then I would need this block to print on a size 12 envelope in a smaller font in the return address area. If the "Print Absentee Envelope " check box is checked, then I need this block to print in the main TO: area on a size 14 envelope. I hope that makes sense.


    Thanks again for the help and reply!


  • That's what I was going to suggest.


    For the envelope try


  • Got an issue Roy,


    Not sure what the second "Next iX" is for but its throwing a "Next without for" error. If I comment it out, it still won't complete and go to the printer.


    Also, I'm not sure how to format it but in the

    Code
    1. oDoc.Envelope.PrintOut , sAddr, , , , , , , "Size 12"

    line (after the 3rd comma) i can see where i can designate this to be the address or return address but I've never used this procedure before so I'm lost as to how to use it. Can you have a look and see if you can point me in the right direction?

  • It looks like you may have already fixed it. ill try the updated code and let you know the result.


    EDIT 2: It no longer errors out but instead just does nothing. It pauses like its going to send the document to the printer but then just dies. I think I also may have figured out the oDoc. envelope line.

  • Well Roy, it works, sort of.... i got everything to print out and to print in the right places. Here is the problem. If I use the debug and step the code through manually, it will print just fine. If I use the actual print button, it will not print at all. I've come to the conclusion that the problem lies at the very end of the code but I'm not sure how to fix it. I'm guessing that I need to pause the procedure long enough for the events to complete then, run the very last line to quit the Sub. It seems that It Is exiting before the sub can pop a dialog box to prompt the user to insert the envelope into the manual feed tray. What would be the fastest, easiest way to pause this sub to wait for user input or to allow a dialog box to appear?


    I also have one other question, is there a simple way to populate a combobox with a list of the users installed printers?

  • You could use the Wait command.


    Code
    1. ''/// pause macro for 5 seconds(approx)
    2. Application.Wait (Now + TimeValue("0:00:05"))

    If you want attach the workbook and I'll have a look.


    To select the printer it's easiest to use this


    Code
    1. Application.Dialogs(xlDialogPrinterSetup).Show
  • ok.... here is the sample. the combobox im attempting to fill is on the settings userform. I'm thinking the pause will be needed no matter the printer. i know some printers are smart enough to know an envelope will come from the manual feed but the printer i have, for example, needs to have a dialog box that asks for the envelope even if it is already in the feeder. its just a matter of clicking the "OK" button on the dialog. see what you think can be done.


    Thanks again.

  • VERY Cool Roy! That's what the problem was. The short pause gave it the ability to pop the dialog box. It works great now. And the combobox shows exactly what I wanted. I will use it to set a default printer for the application itself, at least that is the thought behind it at this moment. Thanks for all your help. Ill let you know how it all comes out. I'm going to wait to mark this thread as solved in case I have other related questions.


    Have a good day!

  • Just as i thought, after doing some deeper testing, there are more issues. Because im leaving for the rest of the day, ill just leave this here for you to look at and mess with later if you have time and i'll check it later to see if you have any ideas. Everything works fine as i stated before but where the problem starts is if you select multiple records in the listbox to print envelopes for. The code will see all the selections made, (I verified this with the debugger) but when it actually comes time to print the envelopes, it will only print the last record selected in the listbox. "Last" meaning the last alphabetically. I'm not sure on this one. Does the code need to print to the oDoc for each selection found? If so, that could turn into a nightmarish issue. The "sample" book i uploaded is just that, a sample. the actual database of names is in the tens of thousands. its already slow when it looks through to find the selected records, i cant imagine how slow it will be if it has to pause for each record to print. Maybe I'm thinking on too small of scale here? Maybe i need to look into doing this in access maybe? :/