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]

Mat be workbook's name of sheet's name includes some of thе symbols which can't be used if filename: ? " / \ < > * | :
Try using ASCII symbols instead.

 

[ZVI]

This code will fix illegal symbols in PDF file name

Sub SheetToPdf1()
  Dim PdfFile As String, char As Variant, i As Long
  
  ' Check compatibility of Excel version
  If Val(Application.Version) < 12 Then
    MsgBox "Export to PDF requires Excel 2007+", vbExclamation, "SheetToPdf"
    Exit Sub
  End If
  
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Name
  i = InStrRev(PdfFile, ".xl", , vbTextCompare)
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = ActiveWorkbook.Path & "\" & PdfFile & "_" & ActiveSheet.Name
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
  PdfFile = Left(PdfFile, 251) & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Report
  MsgBox "PDF file:" & vbLf & PdfFile, vbInformation, "SheetToPdf"
 
End Sub

 

[sclasen24]

I need for the title to use the word PU: and then today's date? Also I need for it to include multiple worksheets, Dec, Jan & Feb

 

[NARAYANAPRASAD]

Good Day!

I was looking at the above mentioned thread.
Copied and pasted the code provided.... but when i try to run the macro....
I get publishing screen and run through
But, get message saying "E-mail was not sent"
Please help

 

[ZVI]

Good Day!

I was looking at the above mentioned thread.
Copied and pasted the code provided.... but when i try to run the macro....
I get publishing screen and run through
But, get message saying "E-mail was not sent"
Please help

Hi and welcome aboard!
For Excel 2007 firstly make sure this AddIn is installed - 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS
Then try this safer (see the part in bold) version of the code:

Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim char As Variant
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Change to suit
  'Title = Range("A1") & " " & Date
  Title = "PU: " & Date
 
  ' Define PDF filename
  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(ActiveWorkbook.Path & "\" & PdfFile, 251) & ".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
    .Display  ' or use .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

Regards

 

[NARAYANAPRASAD]

Thank you so much for quick reply. Its working.
with .Display, its attaching the file and keeping mail ready to send.
But, when i try to change from .Display to .Send it showing me the message E-mail successfully sent but really not sending the mail. Is there anything which needs to be added as add-in?

 

[ZVI]

No other AddIns are required in case it is working with .Display
But sending e-mail programmatically with Microsoft Office Outlook generates security warnings for the user and waits for manual confirming of sending.
Read more details in this link - Avoiding Excessive Security Warnings when Sending Automated E-mail Messages
To be sure warning message takes a focus try to comment this line: Application.Visible = True

P.S. Please delete this atavistic line of the code: OutlApp.Visible = True

 

[NARAYANAPRASAD]

No sir, its not working.
I can go with .Display works perfectly fine for me. But wanted to just understand what needs to be done if i need to use .Send.
Thank You again Sir.

 

[ZVI]

No way to help without facing with that issue - .Send works well on my PC.
So, just common suggestions:
1. Try to run the code on another PC to compare behavior
2. Pay your attention on Outlook's version - Microsoft Outlook Express is limited relative to Microsoft Outlook and may not work properly.
3. Play with settings of antivirus

 

[micfly]

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

Trying this code I get an error: "Run-time error '1004' Document not saved. The document may open, or an error may have been encountered when saving."

Debug highlights this line:

.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

What is wrong? excell 2007