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

 

[ZVI]

Very thank you again Vladimir

Just a last question: and if I want to use on the subject, or email body, how can I use the script to take a cell content?

Thankyou!

Assuming the text of Subject is in the cell A2, then you can put it to the email like this (see in red):

  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
  
    ' Prepare e-mail
    .Subject = Range("A2").Value '<-- This copies text of subject from A2 to email
    .To = Range("A1").Value

Regards,

 

[CarlosPetersen]

Vladimir, thank you so much ofr your generosity for sharing your knowledgement with us.

now its ok!

 

[ZVI]

Vladimir, thank you so much ofr your generosity for sharing your knowledgement with us.

now its ok!

Nice to know it works for you Carlos, welcome to the Board!

 

[kratz101]

Hi Everyone,

Appreciate everyone's work thus far in this thread it has got me 90% of the way into completing my project.

What I need to do now is be able to hide the sheet that is being converted to a PDF, is this possible?

Sub AttachActiveSheetPDF()  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("I3")
 
  ' Define PDF filename
  PdfFile = "RMA Freight Booking"
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & Range("I3") & ".pdf"
 
  ' Export activesheet as PDF
  With Sheets("Freight Booking")
    .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 = "RMA Freight Booking"
    .To = "..." ' <-- Put email of the recipient here
    .CC = "customerservice@hendrickson.com.au" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Please make the attached booking." & vbLf & vbLf _
          & "Regards," & vbLf _
          & "Despatch" & vbLf & vbLf
    .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

 

[ZVI]

What I need to do now is be able to hide the sheet that is being converted to a PDF, is this possible?

  ' Export activesheet as PDF
  With Sheets("Freight Booking")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

Hi and welcome to the Board!

Below of this line of the code:
.ExportAsFixedFormat ...
insert one more line:
.Visible = xlSheetHidden

Note: at least one sheet in workbook should stay unhidden

 

[kratz101]

Thanks for the quick response ZVI, the below works on the first run (sheet hides and export to email works) although on the second run (with the sheet still hidden) I get run-time error 5 - Invalid procedure call or argument.

To clarify I would like the sheet to remain hidden at all times, while still allowing export to PDF.

  ' Export activesheet as PDF
  With Sheets("Freight Booking")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    .Visible = xlSheetHidden
  End With

 

[ZVI]

Well, then use this code modification:

  ' Export activesheet as PDF
  With Sheets("Freight Booking")
    .Visible = xlSheetVisible
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    .Visible = xlSheetHidden
  End With

 

[ALhammadi]

Hi All, Many thanks for the nice coding.
I have a small question is it possible to capture the system date in the email ?

 

[MilkyTech]

Many possibilities. Google "excel vba date". This page is useful: http://www.globaliconnect.com/excel...-month-week-day-functions&catid=79&Itemid=475

 

[Mindb85]

Thanks for all of the great codes they've been all really helpful so far.

Is it possible to edit the attached code so that I can extract multiple sheets to PDF and attach them to the one email?

Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("G5")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".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 = "Payroll Monthly Analysis"
    .To = Range("L3").Value
    .CC = Range("L4").Value
    .Body = "Hi," & vbLf & vbLf _
          & "Please find the latest payroll report attached" & vbLf & vbLf _
          & "Regards," & vbLf & vbLf
    .Attachments.Add PdfFile
       
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    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

Any assistance is greatly appreciated. I have looked through all of the previous replies and couldn't see anywhere that this issue had been addressed previously.

Regards

Will