Mail Merge in Excel (Not Word document)- VBA Macro Help!

[RSidhu]

This relates to Mail Merge Letter in Excel and not Word. So we need a Macro to print the letters from excel.

I have this macro (PrintForms) that works to print the letters (in excel) to the printer. in other words, I can mention the number of records and it will print to the printer, as many letters with different names and addresses.

However, I want it all letters to print to a single combined PDF. This way i can review all the letters in the pdf and save it before printing. Can someone please help?

I have copied the email in the attachment below. Please let me know if you need me to send any other info.

Thanks
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

What version of Excel do you use? If 2007 and up, replace "Sub PrintForms()" with below.

If earlier, you need 3rd party software to export as pdf.

Sub PrintForms()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Msg As String
    Dim i As Integer
    Dim ws As Worksheet
    Dim tempName() As Variant
 
    With Sheets("Form")
    StartRow = .Range("StartRow")
    EndRow = .Range("EndRow")
 
    If StartRow > EndRow Then
        Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
        MsgBox Msg, vbCritical, APPNAME
    End If
    Application.ScreenUpdating = False
    ReDim tempName(StartRow To EndRow)
    For i = StartRow To EndRow
        .Range("RowIndex") = i
        If .Range("Preview") Then
            .PrintPreview
        Else
            .Copy After:=ThisWorkbook.Worksheets(Worksheets.Count)
            Set ws = ThisWorkbook.Worksheets(Worksheets.Count)
            ws.Name = "Temp" & i
            tempName(i) = ws.Name
        End If
    Next i
    End With
 
    ThisWorkbook.Sheets(tempName).Select
    Worksheets(tempName(1)).ExportAsFixedFormat _
              Type:=xlTypePDF, _
              OpenAfterPublish:=True
           
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(tempName).Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
    Sheets("Form").Activate
 
End Sub

Basically, in order to export all into one pdf, you need to store it as separate sheets temporarily.

 

[Chihiro]

Oh forgot one thing. Place "Exit Sub" below ".PrintPreview" and above "Else".

 

[Deepak]

With 2007 at least SP2 or SaveAsPdf add-in from Microsoft itself is required..