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

 

[kapilsum]

Hi Michael
Thanks for all the information i have tried the code and it works perfectly; except on small issue the PDF file generated is not in a single page.
So the scenario is have one worksheet and it is an invoice but when code converts the file to PDF it is broken into 2 pages of a single PDF file . so out of 7 rows and columns 3 are written on the first page and the rest 4 on the second page.
I am not sure what wrong i am doing , Please i will thankful if you can help me to sort this out.

Thanks
SK

 

[suparson]

Sorry if I'm doing this wrong not sure why I can not open a new thread

I have set up a macro to save an excel file as pdf and send it but I now want to be able to send with this a further pdf from a file path and cell pointer can you help me?

 

[bill1967]

I am trying to do something similar but instead of trying to convert my Excel to a PDF, I and trying simply email a single sheet from the workbook. I have code that I found online that I have altered to almost work. It does what it is supposed to do but because the sheet has linked cells the links are in the emailed copy. I would like to get it to some how be text only by possibly doing a paste as values or something along those lines. Below is the script I have so far.


Sub Email_Worksheet_Only()

Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim FileName As String
Dim wSht As Worksheet
Dim shtName As String

Application.ScreenUpdating = False

' Make a copy of the active worksheet
' and save it to a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook

FileName = WB.Worksheets(1).Name
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="H:\CCC Business & Reports\CCC Reports\2015 CCC Reports\QC_Evaluation_Form.xlsx"

'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "phelpsb@grangeinsurance.com"
'Uncomment the line below to hard code a subject
.Subject = "QC Evaluation Form Email Test"
'Uncomment the lines below to hard code a body
.body = "This is a test email"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub

 

[abbasi82]

Hello zvi,

i followed above code as per your instruction, but didn't success.

Error:

Compile Error:
Expected End Sub

Please Help....

 

[ZVI]

Compile Error:
Expected End Sub

Hi,

This means that the code was not completely copied from here into your VBA module.
Please select full contents of the code before copying, the last copied line of the code have to be End Sub

Regards

 

[Onsmaram]

Hello ZVI,

First of all, please forgive my bad English.

Thank you very much for this very nice code. I'm trying to adapt it for my own purpose.
I'm very new with VBA coding and need help.

I'm trying to find a way on how I can have a treeview with checkbox to be able to select one or many files (pdf, doc, xls ....) and to attach them to an email directly from my excel workbook.

Can you please help me with this?

Regards

 

[Stocktaker]

Hi Everyone

Just wanted to say a huge Thanks to ZVI and Michael M, I pick up this thread and used the code from ZVI in page 2

Works an absolute dream form me in excel 2010 with Microsoft Outlook. I just need remote user's to test my Order form next.

Many Thanks Again.

The Stock taker

 

[bill1967]

I used the same one and got it to work for me as well. I had to make a couple tweaks to suit my needs but it is a great piece of code.

 

[MilkyTech]

convert worksheet to pdf, save, and attach to email

OK, I've gotten this to work really well with the code below, including saving the pdf to the same location as the workbook. Problem is that my solution seems klunky in that I am attaching the temp pdf file to the email and saving a completely separate pdf file. I would rather just save and attach the same file. I tried just deleting the code that deletes the temp pdf file and then added ThisWorkbook.Path to the export code like this:

With ActiveSheet    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

but that didn't work; it produces a run-time error and the file doesn't save. So this is what I ended up doing using moslty the code from the post on page 5 of this thread):

Private Sub CommandButton2_Click()
 
  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 = "INVOICE " & ActiveSheet.Range("G15").Value
 
  ' Define PDF filename in TEMP folder
  PdfFile = ActiveWorkbook.Name
  i = InStrRev(PdfFile, ".xl", , vbTextCompare)
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = Title
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
  PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"
  'Debug.Print 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

  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "INVOICE " & ActiveSheet.Range("G15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
  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 = ActiveSheet.Range("A13").Value ' <-- Put email of the recipient here
    .CC = "" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi " & ActiveSheet.Range("A12").Value & "," & vbLf & vbLf _
          & "Your invoice is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf & vbLf _
          & "My Company" & vbLf & "www.MyCompany.com" & vbLf & "xxx-xxx-xxxx" & vbLf
    .Attachments.Add PdfFile
   
    ' Display email
    On Error Resume Next
    .Display ' or use .Send
   
    ' 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

How do I simply attach my second "With ActiveSheet" command which saves the pdf where I want instead of the first one (the temp file)?

 

[MilkyTech]

Re: convert worksheet to pdf, save, and attach to email

So my question that I should have put in the post above is, How can I simply attach the file created in the second export command which saves the pdf where i want it, rather than the first export command that is in the temp directory?