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

aarondesin91

New Member
Joined
Jun 23, 2013
Messages
7
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
 
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.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:
Rich (BB code):
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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,
 
Upvote 0
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. :)
 
Last edited:
Upvote 0
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.


Code:
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
 
Upvote 0
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
 
Upvote 0
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.


Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top