VBA code to convert excel to pdf and email it as attachment

[aarondesin91]

Dear Forumers,

I really need your help. I am new to this whole VBA coding thing have no basic at all in programming and stuff so please help me out here. I am currently assigned a project where I have to create a excel sheet which act as a templete for sending request. The requirement of the project is that I need a vba code for a button when i click it, it will convert my active sheet alone to pdf, automatically save it with the title captured from a cell in the active sheet which is entered by the user. Email this pdf as a attachment to the specific person. Please help me out, my job depends on this project please guys out there.

Thank you

 

[Michael M]

Change the line of code that is

.Send

AND REPLACE it with 


.Display

 

[jsgaggio]

New to the forum - what is the syntax for 2003

 

[Michael M]

Hi and welcome to the Forum, but in future please start a new thread, if you have a question.
To naswer your current question, the code will work in 2003

 

[ZVI]

what is the syntax for 2003

See Michael's code in post #2 and the top 2 lines of my comments in post #4

 

[gopipallan]

Dear ZVI,

Please help me to convert excel chart into pdf file by using macro .

 

[ZVI]

Dear ZVI,

Please help me to convert excel chart into pdf file by using macro .

Hi,
Create chart on a sheet, save workbook in any folder and use the below code to export active sheet to the PDF file.

Sub SheetToPdf()
  Dim PdfFile As String
  
  ' Check compatibility of Excel version
  If Val(Application.Version) < 12 Then
    MsgBox "Export to PDF requires Excel 2007+", vbExclamation, "SheetToPdf"
    Exit Sub
  End If
  
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".xlsm", "") & "_" & ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Report
   MsgBox "PDF file:" & vbLf & PdfFile, vbInformation, "SheetToPdf"
 
End Sub

Regards,

 

[gopipallan]

Thank You for your prompt response.

but still no luck i'm getting an error of invalid argument on below line

.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Thanks & regards

Raja pallan

Hi,
Create chart on a sheet, save workbook in any folder and use the below code to export active sheet to the PDF file.

Sub SheetToPdf()
  Dim PdfFile As String
  
  ' Check compatibility of Excel version
  If Val(Application.Version) < 12 Then
    MsgBox "Export to PDF requires Excel 2007+", vbExclamation, "SheetToPdf"
    Exit Sub
  End If
  
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".xlsm", "") & "_" & ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Report
   MsgBox "PDF file:" & vbLf & PdfFile, vbInformation, "SheetToPdf"
 
End Sub

Regards,

 

[Michael M]

You are using Excel 2007 or higher ???

 

[gopipallan]

You are using Excel 2007 or higher ???

Hi,

I am using excel 2007.


Thanks !!!!!!!!

 

[Michael M]

Try this one, using the old .xls file extension

Sub SheetToPdf()
  Dim PdfFile As String
  
  ' Check compatibility of Excel version
  If Val(Application.Version) < 12 Then
    MsgBox "Export to PDF requires Excel 2007+", vbExclamation, "SheetToPdf"
    Exit Sub
  End If
  
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".xls", "") & "_" & ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Report
   MsgBox "PDF file:" & vbLf & PdfFile, vbInformation, "SheetToPdf"
 
End Sub