Page 2 of 28 FirstFirst 123412 ... LastLast
Results 11 to 20 of 277

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

  1. #11
    New Member
    Join Date
    Jun 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank alot michael...How about the others in my reply above. could you help me??

  2. #12
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    Which code are you using ??
    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 !
    Home 2007 & 2013

    - 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. #13
    New Member
    Join Date
    Jun 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I am using the ZVI's code because i dont have acrobat to use your code.

  4. #14
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    Maybe this then
    Code:
    Sub AttachActiveSheetPDF()
      Dim IsCreated As Boolean
      Dim i As Long, DesktopPath As String
      Dim PdfFile As String, Title As String
      Dim OutlApp As Object
     
      ' Define PDF filename
      PdfFile = ActiveWorkbook.FullName
      i = InStrRev(PdfFile, ".")
      If i > 1 Then PdfFile = Left(PdfFile, i - 1)
      PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
         ' Not sure for what the Title is
      Title = "Request Form for " & Range("A1").Value
    
      
      Set WSHShell = CreateObject("WScript.Shell")
        DesktopPath = WSHShell.SpecialFolders("Desktop")
        Set WSHShell = Nothing
    
        ThisWorkbook.SaveAs Filename:=DesktopPath & "\" & Title, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
      ' 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 or Display
        On Error Resume Next
        .Display
        '.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
    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 !
    Home 2007 & 2013

    - 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]

  5. #15
    New Member
    Join Date
    Jun 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    hey bro,

    I have some problem with the code you gave. When I run the code it say "Compile Error..Variable not defined"..Highlighted blue on "WSHShell" and highlighted yellow on "Sub AttachActiveSheetPDF()". How do i solve this bro??

    Thanks in advantance

  6. #16
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

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

    Try this:
    Code:
    Sub AttachActiveSheetPDF_01()
      Dim IsCreated As Boolean
      Dim PdfFile As String, Title As String
      Dim OutlApp As Object
     
      ' Not sure for what the Title is
      Title = Range("A1")
     
      ' Define PDF filename
      Title = "Request Form for " & Range("A1").Value
      PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".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 _
              & "See the attached requiest in PDF format." & vbLf & vbLf _
              & "Regards," & vbLf _
              & Application.UserName & vbLf & vbLf
        .Attachments.Add PdfFile
       
        ' Try to send
        Application.Visible = True
        .Display
      End With
     
      ' Quit Outlook if it was not already open
      If IsCreated Then OutlApp.Quit
     
      ' Release the memory of object variable
      Set OutlApp = Nothing
     
    End Sub
    Vladimir Zakharov

  7. #17
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    and remove these lines

    Code:
    ' Not sure for what the Title is
      Title = Range("A1")
    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 !
    Home 2007 & 2013

    - 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]

  8. #18
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

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

    Quote Originally Posted by Michael M View Post
    and remove these lines
    It's true, thank you Michael!
    Vladimir Zakharov

  9. #19
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    No worries, thanks for correcting the other lines...
    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 !
    Home 2007 & 2013

    - 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]

  10. #20
    Board Regular
    Join Date
    Jul 2008
    Posts
    491
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hello, this is a great code! thanks for sharing. Is it possible to return to the Outlook screen after clicking the button ? it is a little confusing to say email is sent, but it is actually outstanding as a draft.

Some videos you may like

User Tag List

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
  •