Excel Pivot table & Mail Merge

[sunilkovid]

Hai There,

I'm using a code where I can able to send the pivot table in the body of email using mail merge. But I'm getting a lot of blank space between my pivot table and signature.

I had hidden all the rows by taking the referance of Grand total in the pivot but still i'm getting the blank space underneath.

Macro :

Sub Kovid()

Application.DisplayAlerts = False

Dim r As Range
Set r = Sheets("Pivot").Cells

Dim i As Integer

Dim last_row As Integer

last_row = Application.WorksheetFunction.CountA(Sheets("Mail ID's").Range("A:A"))

For i = 2 To last_row


Range("B1").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("AIDebitAccountNo"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("AIDebitAccountNo"). _
CurrentPage = Sheets("Mail ID's").Range("A" & i).Value

ActiveWorkbook.EnvelopeVisible = True

With r.Parent.MailEnvelope.Item

.to = Sheets("Mail ID's").Range("b" & i).Value
.cc = Sheets("Mail ID's").Range("c" & i).Value
.Subject = Sheets("Mail ID's").Range("J2").Value
.send
End With


Next i


End Sub


-----------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Range("theGap").EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub
-------------------------------------------------------------




Thanks in advance...