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

 

[Maticiuc]

Hi,

When the macro send the email, it's possible to include in email a picture with data?

 

[madpirate]

HI Guys ,
I have read through most of the posts here and unless I missed it -- (eyeball exhaustion) I have seen some really close to what I have been trying to find but just missing the mark.

Please of possible could someone show me a code that;

1 I have a workbook with multiple worksheets (14)
2 I need to be able to select 5 or 6 of those worksheets
3 Convert each to its own pdf using the sheet name and creation date as the PDF name
5 Attach each sheet to ONE outlook email
6 Send it to 2 addresses.

Office 16 - 365 Vesrion

Many thanks in advance


I have tried a few Macros however with my sad skill set have failed spectacularly

 

[sreekanth569]

Dear ZVI,
Thanks for the code. It worked for me
But when I'm executing the same code on second day. It's throwing some error like file path doesn't exist(i didn't remember the exact error).
When i click debug on error dialog, it is navigating me to
.attachments.Add PdfFile

Anything I've to do to get rid of that error.

 

[natiman77]

Dear Zvi ,
i tried your code and it is work well ,
i have two qustions,
1 . how can i set the macro to make this operation for each sheet sapertly , means that it will built each e-mail for each sheet with the relevant PDF with email adress that i will mention on B1 in each sheet for example.
2. how can i choose range for each sheet the will be PDF file in attachment ?

thank you for your assis ,

below you can fint the code that i used .

sub try1()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  Title = Range("A1")
 
 
  Title = "Request Form for " & Range("A1").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "" & Title & "1.pdf"
 
 
  With Sheets("sheet1")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  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
 
  With OutlApp.CreateItem(0)
   
    .Subject = Title
    .To = Range("B1")
    .CC = Range("B2")
    .Body = Range("B1") & vbLf & vbLf _
          & "See the attached requiest in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    Application.Visible = True
    .Display
  End With
 
  If IsCreated Then OutlApp.Quit
 
  Set OutlApp = Nothing
 
End Sub

 

[Kilhuch]

Dear,

Thanks a lot for all explanation, it has been very usefull. Unfortunately, when I'm using this code, I have a runtime error 5: Invalid procedure or argument, for the following line:
.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

He is giving an issue with the type but as it is excel 2010, I was not expecting any issue.
What could be the reason that I have a runtime error?

Thanks for the support !

 

[Mcfg007]

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

Hi I have tried your code and works well, but could you tell me how can I specify which sheets and the range on each sheet to email as a PDF?

 

[Colmans]

Amend Export Active Sheet with the following - and amend sheet names accordingly.

' ### Export ActiveSheet to PDF ###
Dim currentSheet As Worksheet
With ActiveWorkbook
Set currentSheet = .ActiveSheet
.Worksheets(Array(currentSheet.Name, "Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

 

[Mcfg007]

Amend Export Active Sheet with the following - and amend sheet names accordingly.

' ### Export ActiveSheet to PDF ###
Dim currentSheet As Worksheet
With ActiveWorkbook
Set currentSheet = .ActiveSheet
.Worksheets(Array(currentSheet.Name, "Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

Great thank you for perfectly, will need to twick a few things but should be ok.

 

[jterrell]

Is there a way to edit this code so that after the .pdf is attached to Outlook that it stops to enable you to attach other documents?

 

[Hazelnut]

Hi Michael;

Can you assist me with my code? I'm not that great at VBA code, but what I would like to do is have a pdf file created from my Excel 2010 worksheet based on specific cell input and then automatically emailed to a specific user. When the amount populates in cell P20, it sends an email with the file as an excel file still and the file is blank. Your help is greatly appreciated!! My code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim xRgPre As Range
 On Error Resume Next
 If Target.Cells.Count > 1 Then Exit Sub
 Set xRg = Range("P20")
 Set xRgPre = xRg.Precedents
 If xRg.Value > 2999.99 Then
 If Target.Address = xRg.Address Then
    Call Mail_small_Text_Outlook
 ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
 Call Mail_small_Text_Outlook
 End If
 End If
 End Sub
 Sub PDF()
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
 "C:\Desktop\OrderForm.pdf", Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
 True
 End Sub
 Sub Mail_small_Text_Outlook()
 Dim xOutApp As Object
 Dim xOutMail As Object
 Dim xMailBody As String
 Set xOutApp = CreateObject("Outlook.Application")
 Set xOutMail = xOutApp.CreateItem(0)
 xMailBody = "Attached is the order form." & vbNewLine & vbNewLine & _
 "Please contact me with any questions." & vbNewLine & _
 "Have a wonderful rest of your day!"
 On Error Resume Next
 With xOutMail
    .To = hazelnut@gardner.com
    .CC = ""
    .BCC = ""
    .Subject = "Order Form"
    .Body = xMailBody
    .Attachments.Add ThisWorkbook.FullName
    .Send
 End With
 On Error GoTo 0
 Set xOutMail = Nothing
 Set xOutApp = Nothing
 End Sub