Select range in Excel to print to Pdf and send by Outlook

%3CLINGO-SUB%20id%3D%22lingo-sub-1147005%22%20slang%3D%22en-US%22%3ESelect%20range%20in%20Excel%20to%20print%20to%20Pdf%20and%20send%20by%20Outlook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1147005%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%C2%B4m%20%3CSTRONG%3Ereally%20noobie%3C%2FSTRONG%3E%20in%26nbsp%3B%20Excel%20Vba%20but%20i%20really%20want%20a%20Macro%20that%20allows%20me%20to%20select%20a%20range%20in%20Excel%2C%20print%20that%20selection%20to%20Pdf%20and%20send%20it%20by%20Outlook.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20managed%20to%20achieve%20most%20of%20it%20(modified%20some%20code%20found%20in%20websites)%20but%20i%C2%B4m%20missing%20the%20following%20and%20since%20i%C2%B4m%20not%20a%20programmer%20i%20stuck....%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%C2%B4m%20still%20missing%20this%3A%3CBR%20%2F%3E%3CSTRONG%3EImportant%20to%20me%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Choose%20the%20range%20in%20the%20sheet%20to%20print%20to%20Pdf%20(it%20can%20be%20on%20the%20Vba%20code%2C%20but%20i%20would%20prefer%20a%20sheet%20cell%20reference)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Choose%20the%20name%20of%20the%20sheet%20to%20print%2C%20because%20this%20macro%20can%20be%20useful%20for%20other%20sheets%20i%20have%20((it%20can%20be%20on%20the%20Vba%20code%2C%20but%20i%20would%20prefer%20a%20sheet%20cell%20reference)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ENot%20so%20important%3A%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Choose%20the%20default%20computer%20path%20to%20save%20the%20document%2C%20for%20example%20%22c%3A%5Cpdf_prints%5C%22%26nbsp%3B%20.%20I%20would%20prefer%20to%20write%20the%20path%20in%20a%20excel%20cell%2C%20it%C2%B4s%20more%20flexible%20to%20use%20in%20another%20sheets%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Is%20there%20a%20way%20of%20deleting%20the%20pdf%20file%3F%20Just%20send%20it%20in%20the%20email.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20sent%20the%20file%20in%20attachement.%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ECan%20someone%20help%20please%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3ECode%20below%3C%2FSTRONG%3E%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3ESub%20Save_as_pdf_and_send()%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xSht%20As%20Worksheet%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xFileDlg%20As%20FileDialog%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xFolder%20As%20String%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xYesorNo%20As%20Integer%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xOutlookObj%20As%20Object%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xEmailObj%20As%20Object%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDim%20xUsedRng%20As%20Range%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESet%20xSht%20%3D%20ActiveSheet%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESet%20xFileDlg%20%3D%20Application.FileDialog(msoFileDialogFolderPicker)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20xFileDlg.Show%20%3D%20True%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ExFolder%20%3D%20xFileDlg.SelectedItems(1)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EElse%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EMsgBox%20ActiveSheet.Range(%22n9%22)%20%26amp%3B%20vbCrLf%20%26amp%3B%20vbCrLf%20%26amp%3B%20%22Press%20OK%20to%20exit%20this%20macro.%22%2C%20vbCritical%2C%20%22Must%20Specify%20Destination%20Folder%22%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EExit%20Sub%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E'xFolder%20%3D%20ActiveSheet.Range(%22n9%22)%20'Not%20working%20properly%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ExFolder%20%3D%20xFolder%20%2B%20%22%5C%22%20%2B%20ActiveSheet.Range(%22n10%22)%20%2B%20%22.pdf%22%20'xSht.Name%20%2B%20%22.pdf%22_Can%C2%B4t_have_bars%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3E'Check%20if%20file%20already%20exist%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20Len(Dir(xFolder))%20%26gt%3B%200%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ExYesorNo%20%3D%20MsgBox(xFolder%20%26amp%3B%20%22%20already%20exists.%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20vbCrLf%20%26amp%3B%20%22Do%20you%20want%20to%20overwrite%20it%3F%22%2C%20_%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EvbYesNo%20%2B%20vbQuestion%2C%20%22File%20Exists%22)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EOn%20Error%20Resume%20Next%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20xYesorNo%20%3D%20vbYes%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EKill%20xFolder%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EElse%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EMsgBox%20%22if%20you%20don't%20overwrite%20the%20existing%20PDF%2C%20I%20can't%20continue.%22%20_%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E%26amp%3B%20vbCrLf%20%26amp%3B%20vbCrLf%20%26amp%3B%20%22Press%20OK%20to%20exit%20this%20macro.%22%2C%20vbCritical%2C%20%22Exiting%20Macro%22%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EExit%20Sub%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20Err.Number%20%26lt%3B%26gt%3B%200%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EMsgBox%20%22Unable%20to%20delete%20existing%20file.%20Please%20make%20sure%20the%20file%20is%20not%20open%20or%20write%20protected.%22%20_%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E%26amp%3B%20vbCrLf%20%26amp%3B%20vbCrLf%20%26amp%3B%20%22Press%20OK%20to%20exit%20this%20macro.%22%2C%20vbCritical%2C%20%22Unable%20to%20Delete%20File%22%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EExit%20Sub%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESet%20xUsedRng%20%3D%20xSht.UsedRange%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20Application.WorksheetFunction.CountA(xUsedRng.Cells)%20%26lt%3B%26gt%3B%200%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E'Save%20as%20PDF%20file%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ExSht.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20FileName%3A%3DxFolder%2C%20Quality%3A%3DxlQualityStandard%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3E'Create%20Outlook%20email%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESet%20xOutlookObj%20%3D%20CreateObject(%22Outlook.Application%22)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESet%20xEmailObj%20%3D%20xOutlookObj.CreateItem(0)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EWith%20xEmailObj%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E.Display%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E.to%20%3D%20ActiveSheet.Range(%22n5%22)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E.CC%20%3D%20ActiveSheet.Range(%22n6%22)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E.Subject%20%3D%20ActiveSheet.Range(%22n7%22)%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E.HTMLBody%20%3D%20%22%3CFONT%20face%3D%22%26quot%3B%22%3E%22%20%26amp%3B%20%22Good%20day%20dear%20Master%2C%22%20%26amp%3B%20%22%3CBR%20%2F%3E%20%3CBR%20%2F%3E%22%20%26amp%3B%20ActiveSheet.Range(%22n8%22)%20%26amp%3B%20%22%3CBR%20%2F%3E%20%3CBR%20%2F%3E%22%20%26amp%3B%20signature%20%26amp%3B%20%22%3C%2FFONT%3E%22%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E.Attachments.Add%20xFolder%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20DisplayEmail%20%3D%20False%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E'.Send%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20With%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EElse%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EMsgBox%20%22The%20active%20worksheet%20cannot%20be%20blank%22%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EExit%20Sub%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1147005%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello,

 

I´m really noobie in  Excel Vba but i really want a Macro that allows me to select a range in Excel, print that selection to Pdf and send it by Outlook.

 

I managed to achieve most of it (modified some code found in websites) but i´m missing the following and since i´m not a programmer i stuck....

 

I´m still missing this:
Important to me
- Choose the range in the sheet to print to Pdf (it can be on the Vba code, but i would prefer a sheet cell reference)
- Choose the name of the sheet to print, because this macro can be useful for other sheets i have ((it can be on the Vba code, but i would prefer a sheet cell reference)

Not so important:
- Choose the default computer path to save the document, for example "c:\pdf_prints\"  . I would prefer to write the path in a excel cell, it´s more flexible to use in another sheets
- Is there a way of deleting the pdf file? Just send it in the email.

I sent the file in attachement. Can someone help please?

 

Code below:

 

  1. Sub Save_as_pdf_and_send()
  2. Dim xSht As Worksheet
  3. Dim xFileDlg As FileDialog
  4. Dim xFolder As String
  5. Dim xYesorNo As Integer
  6. Dim xOutlookObj As Object
  7. Dim xEmailObj As Object
  8. Dim xUsedRng As Range
  9.  
  10. Set xSht = ActiveSheet
  11. Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
  12.  
  13. If xFileDlg.Show = True Then
  14. xFolder = xFileDlg.SelectedItems(1)
  15. Else
  16. MsgBox ActiveSheet.Range("n9") & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
  17. Exit Sub
  18. End If
  19. 'xFolder = ActiveSheet.Range("n9") 'Not working properly
  20. xFolder = xFolder + "\" + ActiveSheet.Range("n10") + ".pdf" 'xSht.Name + ".pdf"_Can´t_have_bars
  21.  
  22. 'Check if file already exist
  23. If Len(Dir(xFolder)) > 0 Then
  24. xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
  25. vbYesNo + vbQuestion, "File Exists")
  26. On Error Resume Next
  27. If xYesorNo = vbYes Then
  28. Kill xFolder
  29. Else
  30. MsgBox "if you don't overwrite the existing PDF, I can't continue." _
  31. & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
  32. Exit Sub
  33. End If
  34. If Err.Number <> 0 Then
  35. MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
  36. & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
  37. Exit Sub
  38. End If
  39. End If
  40.  
  41. Set xUsedRng = xSht.UsedRange
  42. If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
  43. 'Save as PDF file
  44. xSht.ExportAsFixedFormat Type:=xlTypePDF, FileName:=xFolder, Quality:=xlQualityStandard
  45.  
  46. 'Create Outlook email
  47. Set xOutlookObj = CreateObject("Outlook.Application")
  48. Set xEmailObj = xOutlookObj.CreateItem(0)
  49. With xEmailObj
  50. .Display
  51. .to = ActiveSheet.Range("n5")
  52. .CC = ActiveSheet.Range("n6")
  53. .Subject = ActiveSheet.Range("n7")
  54. .HTMLBody = "<font face=" & Chr(34) & "Calibri" & Chr(34) & " size=" & Chr(34) & 4 & Chr(34) & ">" & "Good day dear Master," & "<br> <br>" & ActiveSheet.Range("n8") & "<br> <br>" & signature & "</font>"
  55. .Attachments.Add xFolder
  56. If DisplayEmail = False Then
  57. '.Send
  58. End If
  59. End With
  60. Else
  61. MsgBox "The active worksheet cannot be blank"
  62. Exit Sub
  63. End If
  64. End Sub
0 Replies
www.000webhost.com