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

 

[zoetwodot]

I figured it out by adding this; not sure if it needs to be it's own instruction

'Export to network folder as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:="P:\Finance\Trade Tickets Scanned\" & Range("B8").Value & Format(Date, " yyyy.mm.dd") & Format(Time, " hh.mm.ss")
End With

 

[MilkyTech]

there you go. i saw your code on your other post. near the beginning you defined the pdf path as the current folder and gave it a filename of the ActiveSheet:

[COLOR=#333333]' Set pdf filename and path to folder where excel doc is currently saved[/COLOR]
[COLOR=#333333]With ThisWorkbook[/COLOR]
[COLOR=#333333]PdfFile = .Path & Application.PathSeparator & ActiveSheet.Name & ".pdf"[/COLOR]
[COLOR=#333333]End With[/COLOR]

That is some older code you are working with and can be cleaned up a bit.

 

[zoetwodot]

there you go. i saw your code on your other post. near the beginning you defined the pdf path as the current folder and gave it a filename of the ActiveSheet:

[COLOR=#333333]' Set pdf filename and path to folder where excel doc is currently saved[/COLOR]
[COLOR=#333333]With ThisWorkbook[/COLOR]
[COLOR=#333333]PdfFile = .Path & Application.PathSeparator & ActiveSheet.Name & ".pdf"[/COLOR]
[COLOR=#333333]End With[/COLOR]

That is some older code you are working with and can be cleaned up a bit.

Thank you for reviewing it.

In my case the PDF needs to be saved in a different location than the Excel file.

 

[Historik]

Hi,

This code is working perfect for me, but my colleagues receive a "run-time error '-2147018887 (80071779)': Document not saved error" when they try to run the macro.

It stops at .Attachments.Add PdfFile which is highlighted in yellow.

Any ideas? Both versions of Excel are the same (2013 Professional), so that couldn't be the problem I think.

Sub AttachActiveSheetPDF()  Dim IsCreated As Boolean
  Dim I As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Title
  Title = Range("F17")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  I = InStrRev(PdfFile, ".")
  If I > 1 Then PdfFile = Left(PdfFile, I - 1)
  PdfFile = ActiveSheet.Range("F18") & ".pdf"
 
  ' Export activesheet as PDF
  Worksheets("Estimator").Rows("34:35").EntireRow.Hidden = False
  With Worksheets("Estimator").Range("A1:E40")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  Worksheets("Estimator").Rows("34:35").EntireRow.Hidden = True
  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 = Range("F15") ' <-- Put email of the recipient here
    .CC = Range("F16") ' <-- Put email of 'copy to' recipient here
    .Body = "test"


    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

Thanks in advance.

 

[MilkyTech]

Are you sure you didn't add a line of code before giving it to your colleagues?

Here you have a path to save to:

PdfFile = ActiveWorkbook.FullName

Then you take away the path and just give it a filename here:

PdfFile = ActiveSheet.Range("F18") & ".pdf"

When you export your pdf, you are using the variable 'PdfFile' for the filename which needs to include a path:

.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile...

 

[MilkyTech]

Try this: (I've changed '.Send' to '.Display' for testing purposes. change it back when you are ready to automatically send the email immediately. Also, if you want to save a copy of the pdf, comment-out or delete the line 'Kill PdfFile')

Sub AttachActiveSheetPDFMilkyTech()
  Dim IsCreated As Boolean
  Dim PdfFile As String
  Dim OutlApp As Object
 
 
  ' Define PDF filename
  With ThisWorkbook
    PdfFile = .Path & Application.PathSeparator & _
              ActiveSheet.Range("F18") & ".pdf"
  End With
 
  ' Export activesheet as PDF
  Worksheets("Estimator").Rows("34:35").EntireRow.Hidden = False
  With Worksheets("Estimator").Range("A1:E40")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  Worksheets("Estimator").Rows("34:35").EntireRow.Hidden = True
  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 = ActiveSheet.Range("F17").Value
    .To = ActiveSheet.Range("F15").Value ' <-- Put email of the recipient here
    .CC = ActiveSheet.Range("F16").Value ' <-- Put email of 'copy to' recipient here
    .Body = "test"




    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
     ' MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[Historik]

Hi MilkyTech,

The PdfFile variable was, indeed, a duplicate. I've removed the first one from the code. This was not giving any errors though.

The recommendation you gave results in no success unfortunately. The strange part is that the unmodified code (before your recommendation) also works at another colleague of mine.

I'm kinda lost in this one..

 

[Michael M]

what value is in Range("F18")

 

[Historik]

The name of the pdf file:

20160510_Recurring Support Costs Quotation- Name of service here - Start date 5th May 2016

Maybe also good to know is that the code below, so only displaying the PDF file, is working for my colleagues.

Sub Preview()  Dim IsCreated As Boolean
  Dim I As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Title
  Title = Range("F17")
 
  ' Define PDF filename
  I = InStrRev(PdfFile, ".")
  If I > 1 Then PdfFile = Left(PdfFile, I - 1)
  PdfFile = ActiveSheet.Range("F18") & ".pdf"
 
  ' Export activesheet as PDF
  Worksheets("Estimator").Rows("34:35").EntireRow.Hidden = False
  With Worksheets("Estimator").Range("A1:E40")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
  Worksheets("Estimator").Rows("34:35").EntireRow.Hidden = True
  End With


End Sub

 

[MilkyTech]

@Historik, you removed the wrong one. The first one had a path, the second one doesn't. without a path, the pdf will probably be saved in your Documents folder but Outlook won't know it's there to attach it to an email. This is why your "Preview" Sub works but your full code doesn't.

The "Title" variable is unnecessary code and should be eliminated (as I have done in the code I posted for you). The filename manipulation is also unnecessary as long as there aren't any illegal filename characters in cell F18.

Start with the code I posted for you, I have tested it several times and it definitely works as is. Try it without modifying it at all, then tweak it from there as needed. If it works for some and not others, then the others have something wrong with their computer setup.