#1
|
|||
|
|||
Envelopes from Excel without mail merge
I can funnel data on a spreadsheet to create addresses in a printable format and was wondering if there is a way to print in Excel itself onto envelopes without using a mail merge or writing VBA.
Unfortunately my staff is scared of mail merge so I am searching for an alternative. Thanks! |
#2
|
|||
|
|||
Addressing envelopes – Excel kitchenette
You could automate the following process by creating a macro to read each row of data, paste that to the appropriate locations on a second sheet, then print from the second sheet in the macro. Envelope Print - Excel |
#3
|
||||
|
||||
Mailmerge would be the obvious approach and is eminently straightforward if you provide the users with a Word envelope document attached to the Excel data. All they would have to do is open the document in Word and run the merge to the printer or to a new document.
Alternatively, you might like to consider Envelope or label add-in for Word This includes envelope templates and provided your addresses are one to a line in the Excel worksheet, you can print one, a selection or all the envelopes from the dialog.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#4
|
|||
|
|||
gmayor yes I agree with you on that and that is what I currently have constructed. I might just have to go with a Macro or VBA on this one, not going to lie my VBA skills are dated, it's been a good while.
thank you both. |
#5
|
||||
|
||||
If you download the attached template and workbook to your desktop, and open the workbook, you will see a couple of dummy records and a button. Click in one of the records and click the button to create an envelope.
You can of course modify the envelope template in Word if you don't like the layout, or you want to add a company logo and/or return address, but ensure that you keep the content control. Change Envelope Layout If you move the template from the desktop, you will need to change the path in the code, which is reproduced from the workbook below. Code:
Option Explicit Sub PrintEnvelope() Dim wdApp As Object Dim wdDoc As Object Dim oCC As Object Dim oRng As Range Dim sAddress As String Dim i As Integer Dim sPath As String sPath = Environ("USERPROFILE") & "\Desktop\Envelope.dotx" 'change as required Selection.End(xlToLeft).Select Set oRng = Range(Selection, Selection.End(xlToRight)) For i = 1 To oRng.Cells.Count sAddress = sAddress & oRng.Cells(i) If i < oRng.Cells.Count Then sAddress = sAddress & vbCr Next i On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 wdApp.Visible = True Set wdDoc = wdApp.Documents.Add(Template:=sPath) Set oCC = wdDoc.SelectContentControlsByTitle("Address").Item(1).Range oCC.Text = sAddress End Sub Private Sub btnEnvelope_Click() PrintEnvelope End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
How can I make the above to print more records. I just need to print the names in column <A> on envelopes.
|
#7
|
|||
|
|||
Envolope excel data
Dear Friend . please find attachment.
ım trying to print excel data to envolope . At page 3 (Zarf yazdırma) please see Zarf yazdır button . if you push this button ,word page (envolope) is automaticly open.(for this macro ı should appreciate gmayor cause ı copy it from his text) But problem is that ı want to let it do this macro everyline .I mean after ı push button (zarf yazdırma) ı want to let will be ready every line which state on table . if you could help me ,ı appreciate a lot . thank you |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge - Create seperate Mail merge pdf for each sheets of an excel file | sureshbvs | Word VBA | 1 | 03-12-2021 05:42 AM |
Looking for an Electronic Envelopes budget system in Excel | taholmes160 | Excel | 2 | 02-13-2020 04:21 AM |
Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA | RMerckling | Mail Merge | 16 | 05-17-2018 05:19 PM |
Mail merge envelopes are blank when printed | DIYman | Mail Merge | 1 | 03-07-2017 02:30 PM |
Envelopes without mail merge | OnTheCouch | Word | 1 | 04-09-2012 01:49 PM |