Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-04-2021, 10:46 AM
Old Old is offline Envelopes from Excel without mail merge Windows 10 Envelopes from Excel without mail merge Office 2007
Novice
Envelopes from Excel without mail merge
 
Join Date: Nov 2016
Posts: 12
Old is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 05-04-2021, 01:03 PM
Logit Logit is online now Envelopes from Excel without mail merge Windows 10 Envelopes from Excel without mail merge Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Reply With Quote
  #3  
Old 05-05-2021, 03:32 AM
gmayor's Avatar
gmayor gmayor is offline Envelopes from Excel without mail merge Windows 10 Envelopes from Excel without mail merge Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #4  
Old 05-05-2021, 11:19 AM
Old Old is offline Envelopes from Excel without mail merge Windows 10 Envelopes from Excel without mail merge Office 2007
Novice
Envelopes from Excel without mail merge
 
Join Date: Nov 2016
Posts: 12
Old is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 05-05-2021, 10:13 PM
gmayor's Avatar
gmayor gmayor is offline Envelopes from Excel without mail merge Windows 10 Envelopes from Excel without mail merge Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Attached Files
File Type: dotx Envelope.dotx (21.9 KB, 18 views)
File Type: xlsm Example.xlsm (20.2 KB, 20 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 03-03-2023, 11:16 PM
alex_bla alex_bla is offline Envelopes from Excel without mail merge Windows 11 Envelopes from Excel without mail merge Office 2021
Novice
 
Join Date: Mar 2023
Posts: 1
alex_bla is on a distinguished road
Default

How can I make the above to print more records. I just need to print the names in column <A> on envelopes.
Reply With Quote
  #7  
Old 10-11-2023, 06:42 AM
Kartalkadir Kartalkadir is offline Envelopes from Excel without mail merge Windows XP Envelopes from Excel without mail merge Office 2010
Novice
 
Join Date: Oct 2023
Posts: 3
Kartalkadir is on a distinguished road
Default 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
Attached Files
File Type: xlsm Çalışma.xlsm (132.1 KB, 0 views)
File Type: dotx Envelope.dotx (21.9 KB, 0 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Envelopes from Excel without mail merge 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
Envelopes from Excel without mail merge Mail merge envelopes are blank when printed DIYman Mail Merge 1 03-07-2017 02:30 PM
Envelopes from Excel without mail merge Envelopes without mail merge OnTheCouch Word 1 04-09-2012 01:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft