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

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

micfly

Well-known Member
Joined
Sep 8, 2008
Messages
526
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,659
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:
Rich (BB code):
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
 
Last edited:

gajump

New Member
Joined
Jun 26, 2013
Messages
3
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

New Member
Joined
Jan 14, 2014
Messages
2
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

New Member
Joined
Aug 14, 2014
Messages
15
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

New Member
Joined
Oct 10, 2014
Messages
2
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

New Member
Joined
Oct 10, 2014
Messages
2
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

New Member
Joined
Jul 25, 2014
Messages
27
The template code for Excel 2007+ with its own PDF converter:
Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,024
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top