View Single Post
 
Old 05-05-2021, 10:13 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,584
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