Page 1 of 5 123 ... LastLast
Results 1 to 10 of 48
Like Tree1Likes

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

This is a discussion on VBA code to convert excel to pdf and email it as attachment within the Excel Questions forums, part of the Question Forums category; Dear Forumers, I really need your help. I am new to this whole VBA coding thing have no basic at ...

  1. #1
    New Member
    Join Date
    Jun 2013
    Posts
    7

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

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,667

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,706

    Default Re: VBA code to convert excel to pdf and email it as attachment

    The template code for Excel 2007+ with its own PDF converter:
    Code:
    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
    Last edited by ZVI; Jun 23rd, 2013 at 11:00 PM.
    VBA Geek likes this.
    Vladimir Zakharov
    Microsoft MVP – Excel

  4. #4
    New Member
    Join Date
    Jun 2013
    Posts
    7

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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 .
    [IMG]file:///C:\Users\AABRAH~1\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png[/IMG]
    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

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,667

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    New Member
    Join Date
    Jun 2013
    Posts
    7

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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.

  7. #7
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,706

    Default Re: VBA code to convert excel to pdf and email it as attachment

    What is Excel version on your PC?
    Vladimir Zakharov
    Microsoft MVP – Excel

  8. #8
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,706

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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
    Vladimir Zakharov
    Microsoft MVP – Excel

  9. #9
    New Member
    Join Date
    Jun 2013
    Posts
    7

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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

  10. #10
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,667

    Default Re: VBA code to convert excel to pdf and email it as attachment

    Regardless of which code you use, change this line
    Code:
    .Send
    TO
    Code:
    .Display
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Page 1 of 5 123 ... LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com