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

 

[Michael M]

Hi and welcome to the Board
Her's an example of what you need, but you will either need toprovide more info on what cells contain the required data, or make changes as commented on the code

Sub DoALLsingle()
Dim tempPDFFileName, tempPSFileName, tempPDFRawFileName As String, mypdfDist As New PdfDistiller, _
 i As Integer, Mail_Object, Email_Subject, o As Variant
    tempPDFRawFileName = "G:\Temp\" & ActiveWorkbook.Name ' Change File Path to suit
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
     Kill tempPSFileName
Set mypdfDist = Nothing

'************End of PDF section*************
'************Start of emailing code*********
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "TEXT IN HERE" ' CHANGE TO SUIT
            .To = "RECIPIENT IN HERE" 'CHANGE TO SUIT
            .Body = "E MAIL TEXT GOES HERE" & Chr(13) & Chr(13) & "Regards," & Chr(13) & "YOUR NAME." & Chr(13) & "YOUR ADDRESS." 'Change comments to suit
            .Attachments.Add tempPDFFileName
            .Send
    End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

 

[ZVI]

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

 

[aarondesin91]

Hey guys,
Thank alot for your help here. Really appreciate your help here. But i face a problem. When trying to paste your code i have a problem .

So guys that wat my screen shows. Im not sure didn’t I paste the code in the correct place. So please guys help me out here.

Thank alot

 

[Michael M]

Can't see the image ??
So, what is the problem ??
Either code must go in Standard module OR "This Workbook" module
It can then either be run by Pressing ALT + F8 and selecting the macro
oR
asigning it to a button / shape / menu

 

[aarondesin91]

Hey,
Thank for the help. I think my problem is that I don't know where to paste the code and also the reference. So can you guide me where to paste the code, please..

Thank you.

 

[ZVI]

What is Excel version on your PC?

 

[ZVI]

Michael's code works in all Excel versions but additionally requires Abobe Acrobat software which is not freeware.

My code works in Excel 2007, 2010, 2013 only, but without any additional software.

How to use the code:
1. Copy the sample code that you want to use
2. Open the workbook in which you want to add the code
3. Hold the Alt key and press the F11 key to open the Visual Basic Editor (VBE)
4. Choose menu Insert | Module
5. Where the cursor is flashing, choose menu Edit | Paste
6. Hold the Alt key and press the Q key to leave VBE
7. Hold the Alt key and press the F8 key to display the Run Macro Dialog.
8. Double Click the macro's name to Run it

 

[aarondesin91]

Dear ZVI,

I would like to ask another help. In your code, given the mail is send automatically right. Is it possible that all the title and the body and the attachment is send to outlook but the last step which is clicking the send button is done manually meaning that when i run this code, the mail is all ready just waiting for the user to click the send button. another thing that is it possible to make the pdf file saved in the user pc at the desktop. and the "title" ( data captured from the cell) to be a part of the email title. For example, "Request Form for Title".Plus can the title be used at the name of the pdf file that been saved in the user's pc. please help me man.. would really appreciate your help.

Thanks alot

 

[Michael M]

Regardless of which code you use, change this line

.Send

TO

.Display