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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,876
Office Version
2013
Platform
Windows
Which code are you using ??
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,876
Office Version
2013
Platform
Windows
Maybe this then
Code:
Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim i As Long, DesktopPath As String
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
     ' Not sure for what the Title is
  Title = "Request Form for " & Range("A1").Value

  
  Set WSHShell = CreateObject("WScript.Shell")
    DesktopPath = WSHShell.SpecialFolders("Desktop")
    Set WSHShell = Nothing

    ThisWorkbook.SaveAs Filename:=DesktopPath & "\" & Title, FileFormat:=xlOpenXMLWorkbookMacroEnabled

  ' 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 or Display
    On Error Resume Next
    .Display
    '.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
 

aarondesin91

New Member
Joined
Jun 23, 2013
Messages
7
hey bro,

I have some problem with the code you gave. When I run the code it say "Compile Error..Variable not defined"..Highlighted blue on "WSHShell" and highlighted yellow on "Sub AttachActiveSheetPDF()". How do i solve this bro??

Thanks in advantance
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Try this:
Rich (BB code):
Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  Title = "Request Form for " & Range("A1").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".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 _
          & "See the attached requiest in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    Application.Visible = True
    .Display
  End With
 
  ' Quit Outlook if it was not already open
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,876
Office Version
2013
Platform
Windows
and remove these lines

Code:
' Not sure for what the Title is
  Title = Range("A1")
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,876
Office Version
2013
Platform
Windows
No worries, thanks for correcting the other lines...:pray:
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
Hello, this is a great code! thanks for sharing. Is it possible to return to the Outlook screen after clicking the button ? it is a little confusing to say email is sent, but it is actually outstanding as a draft.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,078
Messages
5,466,488
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top