Page 26 of 28 FirstFirst ... 162425262728 LastLast
Results 251 to 260 of 277

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

  1. #251
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Anybody wishing to respond to the previous post, please do so in this thread.

    @ Hazelnut
    We don't want to end up with 2 or more sets of people trying to solve the same problem, not knowing that their time may be wasted if the problem was already solved in another thread. Please refer to #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  2. #252
    New Member
    Join Date
    Mar 2018
    Posts
    10
    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

    Sorry, I was just trying to get an answer to the coding question. Oh well.

  3. #253
    New Member
    Join Date
    Mar 2018
    Posts
    10
    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

    When I use this code I receive a Run-time error '70': Permission denied. It creates the pdf and sends the email, but not sure why I'm getting this error. If I click on debug it takes me to the line "Kill PdfFile". I've tried removing that line and all the lines below it other than End Sub and nothing fixes it. Could you provide a fix for this issue? Thanks much!

    Quote Originally Posted by ZVI View Post
    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

  4. #254
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,634
    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 Hazelnut View Post
    When I use this code I receive a Run-time error '70': Permission denied. It creates the pdf and sends the email, but not sure why I'm getting this error. If I click on debug it takes me to the line "Kill PdfFile". I've tried removing that line and all the lines below it other than End Sub and nothing fixes it. Could you provide a fix for this issue? Thanks much!
    Hi,
    Such error can happen in case PdfFile has been previously open in Acrobat because Acrobat locks that file and file can't be deleted/updated. Close all Acrobat windows with PdfFile before running the code. Or add timestamp to the name of PdfFile to guarantee its unique name.
    Also be sure that parameter OpenAfterPublish:=False is present in the code line with .ExportAsFixedFormat
    Last edited by ZVI; Apr 21st, 2018 at 08:48 PM.
    Vladimir Zakharov

  5. #255
    New Member
    Join Date
    Jun 2018
    Posts
    2
    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

    Hi Michael,

    I tried to run your code but got a compile error for the PdfDistiller: "User-defined type not defined". Do i need to install a third party PDF export plugin?

    Thanks,

  6. #256
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Quote Originally Posted by imatjazz View Post
    Hi Michael,

    I tried to run your code but got a compile error for the PdfDistiller: "User-defined type not defined". Do i need to install a third party PDF export plugin?

    Thanks,
    Welcome to the MrExcel board!

    With over 250 posts in this thread, about 80 different posters, and the thread being 5 years old, it isn't easy to know which post(s) you are referring to or whether the particular member is still active in the forum. You would be well advised to point out which post(s) you are referring to.
    Last edited by Peter_SSs; Jun 18th, 2018 at 10:30 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #257
    New Member
    Join Date
    Jun 2018
    Posts
    2
    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

    Hi Peter, thank you for the note.

    I tired to run the script below to create pdf however I got a Runtime error 5. If I change the xlTypeXPS then it worked. I run this on my work laptop and not sure if there is issues with the xlTypePDF engine or my work laptop restriction.


    Code:
    Sub SavePDF()
        Dim Path, FileName1 As String
        Path = "C:\temp\"  '<--- edit path as desired
        FileName1 = "TestPDF1"   '<--- change file name as desired
        ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
    End Sub
    Thanks

  8. #258
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,634
    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 imatjazz View Post
    Hi Michael,

    I tried to run your code but got a compile error for the PdfDistiller: "User-defined type not defined". Do i need to install a third party PDF export plugin?

    Thanks,
    For me it's tiresome to read further the first page of this huge thread too
    Seems the question relates to the code of post #2 .
    It is necessary to set reference via VBE-Tools-References to Acrobat Distiler
    Vladimir Zakharov

  9. #259
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,634
    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 imatjazz View Post
    Hi Peter, thank you for the note.

    I tired to run the script below to create pdf however I got a Runtime error 5. If I change the xlTypeXPS then it worked. I run this on my work laptop and not sure if there is issues with the xlTypePDF engine or my work laptop restriction.


    Code:
    Sub SavePDF()
        Dim Path, FileName1 As String
        Path = "C:\temp\"  '<--- edit path as desired
        FileName1 = "TestPDF1"   '<--- change file name as desired
        ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
    End Sub
    Thanks
    For early version of Excel 2007 you need to install "2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS", the download link is in the post #35
    Last edited by ZVI; Jun 19th, 2018 at 12:54 AM.
    Vladimir Zakharov

  10. #260
    New Member
    Join Date
    Dec 2017
    Posts
    2
    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

    Hi,

    I was looking at this post and its something similar to what i need as well. can i check how do i assign this code to a button? In which event display event for outlook will occur when i press the button.

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
  •