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]

What is wrong? excell 2007

Hi Micfly,

In post #36 it was highlighted that for Excel 2007 this Add-In have to be installed - 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS.
Please check it up or try its (re)installing.

Regards

 

[micfly]

Still getting the error. Perhaps a seperate problem? Could be due to anti-virus software on my PC but it's my work PC so I can't access those settings.

 

[ZVI]

Still getting the error. Perhaps a seperate problem? Could be due to anti-virus software on my PC but it's my work PC so I can't access those settings.

Here is the list of some possible reasons of that kind of issue:

1. 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS is not installed.
This is not your case as you have already checked it out.

2. There is a symbol in the sheet name which is not allowed for the file name.
Code of post#36 solves this problem

3. Restricted permission for the file saving on desktop.
Try to save PDF manually to the same folder where your workbook is stored to see if it is possible.
Moving workbook to the folder outside of the desktop can solve this issue.

4. There can be influence of anti-virus software. Firewall software also can turn Excel to the sandbox mode.
Contact with administrator of your PC to eliminate these types of problems.

The below code solves issues 2 and 3, give it a try:

Sub AttachActiveSheetPDF_02()
 
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  Dim i As Long
  Dim char As Variant
 
  ' Change to suit
  'Title = Range("A1") & " " & Date
  Title = "PU: " & Date
 
  ' Define PDF filename in TEMP folder
  PdfFile = ActiveWorkbook.Name
  i = InStrRev(PdfFile, ".xl", , vbTextCompare)
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
  PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"
  'Debug.Print PdfFile
 
  ' Delete PDF file - for the case it was not deleted at debugging
  If Len(Dir(PdfFile)) Then Kill PdfFile
 
  ' Export activesheet as PDF to the temporary folder
  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
  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 _
          & "The report is attached in PDF file" & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send ' or use .Display
   
    ' Return focus to Excel's window
    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 the temporary PDF file
  If Len(Dir(PdfFile)) Then Kill PdfFile
 
  ' Try to quit Outlook if it was not previously open
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  ' Note: sometimes Outlook object can't be released from the memory
  Set OutlApp = Nothing
 
End Sub

 

[micfly]

Okay, thanks for the help, I'll keep working on it.

 

[gajump]

thanks for sharing thoese codes to convert excel to pdf, here is what i find out about this conversion progress.

// Render Excel to get a REImage collection, or choose specific page to render
BasePage XLSXDocument.GetPage(int pageIndex);
BaseImage XLSXPage.toImage();
BaseImage XLSXPage.toImage(int height, int width);

//Render Excel to desired document image format
void REFile.SaveImageFile(REImage image, String filePath);
hope it is helpful.
</pre>

 

[chococ]

Hi ZVI,

Thank you so much for sharing such helpful code with us. Can I ask if it can be modified to select multiple sheets and exported into multiple pdf in multiple emails respectively? I can stop it at the .Display stage so I can manually modify email body. The name of sheets can be manually coded into macro, they are known names and won't change. For convenience can just use Sheet1 Sheet2 Sheet3 for now, but I have 11 sheets in total.

At the moment I can run the macro separately on each sheet, but it will involve manually changing the recipients list inside the macro and if possible I would like to draw that list from a pre-made table as well.

Thank you very much in advance.

Bests

 

[gregsilvers]

Just tried this myself and it works great! Is there anyway to include a standard signature i,ve already set in outlook please? (instead of regards, user name)

 

[AntExcel]

Hi ZVI,

I tried your code BUT when it gets to .Attachements.add I get "Cant find file" error message. So then decided to add a file path:

ENVIRON("USERPROFILE") & "\Desktop" & PdfFile

IT WORKS! BUT in the file name I see DesktopExcelSheet.pdf

I tried many situations and now very annoyed, can you help???

Thank you.

Ants

 

[AntExcel]

Nevrmind jutsaw your latst post.

Thanks works great!

Hi ZVI,

I tried your code BUT when it gets to .Attachements.add I get "Cant find file" error message. So then decided to add a file path:

ENVIRON("USERPROFILE") & "\Desktop" & PdfFile

IT WORKS! BUT in the file name I see DesktopExcelSheet.pdf

I tried many situations and now very annoyed, can you help???

Thank you.

Ants

 

[lapot]

The template code for Excel 2007+ with its own PDF converter:

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("A1")
 
  ' 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 = Title
    .To = "..." ' <-- Put email of the recipient here
    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .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

How do I change this code for a Word Document, I have a Word Document on my desktop and I like to convert and attach it to Outlook? Here is the location C:\Users\Admin\Desktop


Regards