Print my pivot table as PDF and attach it to Outlook

[griffin]

Hi Experts,


I have got a requirement where i have a pivot table with the report filter(vendor name).When i change the filter selection obviously my data changes and i need to convert my result into PDF format which im doing manually using PDF creater in printer option but i want it to be done using vba also the PDF should get saved in some location in my local drive and get attached to outlook email.


Please help me on this.


Thanks and Regards,

 

[Dhamo]

Hi Griffin,


Try this.


I do not aware your pivot table size, assuming you may have the pivot table in empty sheet. Creating the whole sheet as pdf and sending through outlook.


Sub CreatePDF()

Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte

Dim Name As String


Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _

ThisWorkbook.Path & "" & wksSheet.Name, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

False


Name = wksSheet.Name

Dim OutApp As Object

Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)


On Error Resume Next

' Change the mail address and subject in the macro before you run it.

With OutMail

.To = "aprankd@gmail.com"

.CC = ""

.BCC = ""

.Subject = "Latest Pivot Table PDF"

.Body = "Hyla, Here is your information!"

.display

.Attachments.Add (ThisWorkbook.Path & "" & wksSheet.Name & ".pdf") '"C:test.txt")

.Send

End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing

End Sub


PS: If you don't have saveaspdf add-in, please download and install from Microsoft site.

 

[griffin]

Hi Dhamo,

thank you very much for the above code.im using 2003 and 2010 version of excel in my system for the different users.If i save the above code in 2007 xlsb format its working fine but when i do the same for 2010,xlsx format then its saying on the line below that document not saved.Also,i dont get save as pdf format option in 2003 vs but i get it in 2010 version (can you tell me why so as both versions are in my local machine)

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _

ThisWorkbook.Path & "" & wksSheet.Name, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

False


Thank you for your time.


Regards,

 

[griffin]

Also,the code sometimes runs very weired,very slow i mean.Any reason?


Thanks,

 

[griffin]

Hi Dhamo,


Sorry to bother you,i have rectified it my own now.Just let me know why my 2003 excel is not showing save as PDF format whereas 2010 vs is showing.


Regards,