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 offline Envelopes from Excel without mail merge Windows 10 Envelopes from Excel without mail merge Office 2007
Expert
 
Join Date: Jan 2017
Posts: 385
Logit will become famous soon enoughLogit will become famous soon enough
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: 3,555
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
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: 3,555
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
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, 1 views)
File Type: xlsm Example.xlsm (20.2 KB, 2 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
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 06:56 AM.


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