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

 

[Peter_SSs]

Anybody wishing to respond to the previous post, please do so in this thread.

@ Hazelnut
We don't want to end up with 2 or more sets of people trying to solve the same problem, not knowing that their time may be wasted if the problem was already solved in another thread. Please refer to #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

 

[Hazelnut]

Sorry, I was just trying to get an answer to the coding question. Oh well.

 

[Hazelnut]

When I use this code I receive a Run-time error '70': Permission denied. It creates the pdf and sends the email, but not sure why I'm getting this error. If I click on debug it takes me to the line "Kill PdfFile". I've tried removing that line and all the lines below it other than End Sub and nothing fixes it. Could you provide a fix for this issue? Thanks much!

Try this:

Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  Title = "Request Form for " & Range("A1").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"
 
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = "..." ' <-- Put email of the recipient here
    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "See the attached requiest in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    Application.Visible = True
    .Display
  End With
 
  ' Quit Outlook if it was not already open
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[ZVI]

When I use this code I receive a Run-time error '70': Permission denied. It creates the pdf and sends the email, but not sure why I'm getting this error. If I click on debug it takes me to the line "Kill PdfFile". I've tried removing that line and all the lines below it other than End Sub and nothing fixes it. Could you provide a fix for this issue? Thanks much!

Hi,
Such error can happen in case PdfFile has been previously open in Acrobat because Acrobat locks that file and file can't be deleted/updated. Close all Acrobat windows with PdfFile before running the code. Or add timestamp to the name of PdfFile to guarantee its unique name.
Also be sure that parameter OpenAfterPublish:=False is present in the code line with .ExportAsFixedFormat

 

[imatjazz]

Hi Michael,

I tried to run your code but got a compile error for the PdfDistiller: "User-defined type not defined". Do i need to install a third party PDF export plugin?

Thanks,

 

[Peter_SSs]

Hi Michael,

I tried to run your code but got a compile error for the PdfDistiller: "User-defined type not defined". Do i need to install a third party PDF export plugin?

Thanks,

Welcome to the MrExcel board!

With over 250 posts in this thread, about 80 different posters, and the thread being 5 years old, it isn't easy to know which post(s) you are referring to or whether the particular member is still active in the forum. You would be well advised to point out which post(s) you are referring to.

 

[imatjazz]

Hi Peter, thank you for the note.

I tired to run the script below to create pdf however I got a Runtime error 5. If I change the xlTypeXPS then it worked. I run this on my work laptop and not sure if there is issues with the xlTypePDF engine or my work laptop restriction.

Sub SavePDF()
    Dim Path, FileName1 As String
    Path = "C:\temp\"  '<--- edit path as desired
    FileName1 = "TestPDF1"   '<--- change file name as desired
    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
End Sub

Thanks

 

[ZVI]

Hi Michael,

I tried to run your code but got a compile error for the PdfDistiller: "User-defined type not defined". Do i need to install a third party PDF export plugin?

Thanks,

For me it's tiresome to read further the first page of this huge thread too
Seems the question relates to the code of post #2 .
It is necessary to set reference via VBE-Tools-References to Acrobat Distiler

 

[ZVI]

Hi Peter, thank you for the note.

I tired to run the script below to create pdf however I got a Runtime error 5. If I change the xlTypeXPS then it worked. I run this on my work laptop and not sure if there is issues with the xlTypePDF engine or my work laptop restriction.

Sub SavePDF()
    Dim Path, FileName1 As String
    Path = "C:\temp\"  '<--- edit path as desired
    FileName1 = "TestPDF1"   '<--- change file name as desired
    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
End Sub

Thanks

For early version of Excel 2007 you need to install "2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS", the download link is in the post #35

 

[shawn1983]

Hi,

I was looking at this post and its something similar to what i need as well. can i check how do i assign this code to a button? In which event display event for outlook will occur when i press the button.