Mail Merge Excel With Word

[sg2209]

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

 

[Macropod]

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:

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

 

[sg2209]

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

[TABLE="width: 416"]
[TR]
[TD]Name[/TD]
[TD]Street[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]Mary Johnson[/TD]
[TD]12 Main Street[/TD]
[TD]New York, NY 12345[/TD]
[/TR]
[TR]
[TD]John Cena[/TD]
[TD]Po Box 515471[/TD]
[TD]Los Angeles , CA 90051[/TD]
[/TR]
[TR]
[TD]Nancy Meeker[/TD]
[TD]1430 Decision Street[/TD]
[TD]Vista CA 25652[/TD]
[/TR]
[TR]
[TD]Mary Guzenski[/TD]
[TD]15 South Street[/TD]
[TD]Tampa, FL 98652[TABLE="width: 416"]
[TR]
[TD]Name[/TD]
[TD]Street[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]Mary Johnson[/TD]
[TD]12 Main Street[/TD]
[TD]New York, NY 12345[/TD]
[/TR]
[TR]
[TD]John Cena[/TD]
[TD]Po Box 515471[/TD]
[TD]Los Angeles , CA 90051[/TD]
[/TR]
[TR]
[TD]Nancy Meeker[/TD]
[TD]1430 Decision Street[/TD]
[TD]Vista CA 25652[/TD]
[/TR]
[TR]
[TD]Mary Guzenski[/TD]
[TD]15 South Street[/TD]
[TD]Tampa, FL 98652[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]

Appreciate your efforts .

thanks

 

[Macropod]

Try:

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

 

[sg2209]

Getting the blank Print not sure why ?

 

[Macropod]

Oops! The line:
sAddr = .Cells(r, c).Text & vbCr
should have been:
sAddr = sAddr & .Cells(r, c).Text & vbCr
Fixed in both.

 

[sg2209]

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

 

[Macropod]

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.