Mail Merge Excel With Word

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
117
Office Version
  1. 2016
Hi Friends , this is very first macro for Mail Merge here i am trying to learn the mail merge however getting an error
I have an excel workbook where the first sheet is a Data and there is a Data From A1 to C5

Sub AllEnvelopes()

Dim oTable As Range, r As Long, c As Long, sAddr As String
Dim oWord As Word.Application, oDoc As Word.Document
Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.Documents.Add
With ActiveCell.CurrentRegion
For r = 2 To .Rows.Count ' Using R=2 to Skip Row (Headers)"
For c = 2 To .Columns.Count
sAddr = sAddr & .Cells(r, c) & vbCr
Next c
sAddr = Left(sAddr, Len(sAddr) - 1)
oDoc.Envelope.PrintOut , sAddr, , , , , , "Size10"
DoEvents: sAddr = ""
Next r
End With
oWord.Quit False
End Sub
when i Run this , it gives me an Error Type Mismatch here ""oDoc.Envelope.PrintOut , sAddr, , , , , , "Size10"

please help
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
What you're doing is not a mailmerge; it's simple Word automation.

As for the error, that's because your code is missing the required ', ' for a missing optional parameter.

In any event, your code could be simplified:
Code:
Sub AllEnvelopes()
Dim oWord As New Word.Application, oDoc As Word.Document
Dim r As Long, c As Long, sAddr As String
Set oDoc = oWord.Documents.Add
With ActiveCell.CurrentRegion
  For r = 2 To .Rows.Count ' Using R=2 to Skip Row (Headers)"
    For c = 2 To .Columns.Count
      sAddr = sAddr & .Cells(r, c).Text & vbCr
    Next c
    sAddr = Left(sAddr, Len(sAddr) - 1)
    oDoc.Envelope.PrintOut Address:=sAddr, Size:="Size10"
    DoEvents: sAddr = ""
  Next r
End With
oWord.Quit False
Set oDoc = Nothing: Set oWord = Nothing
End Sub
 
Last edited:
Upvote 0
thanks for the response , just getting the Print in PDF format , why not on MS Word also only the last column is getting Printed , i mean the state and Zip Code only
Below is the data Excel

NameStreetCity
Mary Johnson12 Main StreetNew York, NY 12345
John CenaPo Box 515471Los Angeles , CA 90051
Nancy Meeker1430 Decision StreetVista CA 25652
Mary Guzenski15 South StreetTampa, FL 98652
NameStreetCity
Mary Johnson12 Main StreetNew York, NY 12345
John CenaPo Box 515471Los Angeles , CA 90051
Nancy Meeker1430 Decision StreetVista CA 25652
Mary Guzenski15 South StreetTampa, FL 98652

Appreciate your efforts .

thanks
 
Last edited:
Upvote 0
Try:
Code:
Sub AllEnvelopes()
Dim oWord As New Word.Application, oDoc As Word.Document
Dim lRow As Long, lCol As Long, r As Long, c As Long, sAddr As String
Set oDoc = oWord.Documents.Add
With ActiveSheet
  With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
    lRow = .Row
    lCol = .Column
  End With
  For r = 2 To lRow ' Using R=2 to Skip Row (Headers)"
    For c = 2 To lCol
      sAddr = sAddr & .Cells(r, c).Text & vbCr
    Next c
    sAddr = Left(sAddr, Len(sAddr) - 1)
    oDoc.Envelope.PrintOut Address:=sAddr, Size:="Size10"
    DoEvents: sAddr = ""
  Next r
End With
oWord.Quit False
Set oDoc = Nothing: Set oWord = Nothing
End Sub
 
Last edited:
Upvote 0
Oops! The line:
sAddr = .Cells(r, c).Text & vbCr
should have been:
sAddr = sAddr & .Cells(r, c).Text & vbCr
Fixed in both.
 
Upvote 0
thanks you but still only street and city name is getting printed not the first column which is names..

got the names too appended the line c =1 earlier it was too.

Hats off to you :)

thank you

if i need letter to be printed.please assist
 
Last edited:
Upvote 0
thanks you but still only street and city name is getting printed not the first column which is names..

got the names too appended the line c =1 earlier it was too.
the code you posted had:
For c = 2 To .Columns.Count
Since I assumed you knew which column you wanted to start from, I didn't change that.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top