Page 18 of 28 FirstFirst ... 81617181920 ... LastLast
Results 171 to 180 of 277

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

  1. #171
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,656
    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 CarlosPetersen View Post
    Very thank you again Vladimir

    Just a last question: and if I want to use on the subject, or email body, how can I use the script to take a cell content?

    Thankyou!
    Assuming the text of Subject is in the cell A2, then you can put it to the email like this (see in red):
    Code:
    
      ' Prepare e-mail with PDF attachment
      With OutlApp.CreateItem(0)
      
        ' Prepare e-mail
        .Subject = Range("A2").Value '<-- This copies text of subject from A2 to email
        .To = Range("A1").Value
    

    Regards,
    Last edited by ZVI; Aug 2nd, 2016 at 11:38 AM.
    Vladimir Zakharov

  2. #172
    New Member
    Join Date
    Aug 2016
    Posts
    3
    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

    Vladimir, thank you so much ofr your generosity for sharing your knowledgement with us.

    now its ok!

  3. #173
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,656
    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 CarlosPetersen View Post
    Vladimir, thank you so much ofr your generosity for sharing your knowledgement with us.

    now its ok!
    Nice to know it works for you Carlos, welcome to the Board!
    Vladimir Zakharov

  4. #174
    New Member
    Join Date
    Aug 2016
    Posts
    4
    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 Everyone,

    Appreciate everyone's work thus far in this thread it has got me 90% of the way into completing my project.

    What I need to do now is be able to hide the sheet that is being converted to a PDF, is this possible?

    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("I3")
     
      ' Define PDF filename
      PdfFile = "RMA Freight Booking"
      i = InStrRev(PdfFile, ".")
      If i > 1 Then PdfFile = Left(PdfFile, i - 1)
      PdfFile = PdfFile & "_" & Range("I3") & ".pdf"
     
      ' Export activesheet as PDF
      With Sheets("Freight Booking")
        .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 = "RMA Freight Booking"
        .To = "..." ' <-- Put email of the recipient here
        .CC = "customerservice@hendrickson.com.au" ' <-- Put email of 'copy to' recipient here
        .Body = "Hi," & vbLf & vbLf _
              & "Please make the attached booking." & vbLf & vbLf _
              & "Regards," & vbLf _
              & "Despatch" & vbLf & vbLf
        .Attachments.Add PdfFile
       
        ' Try to send
        On Error Resume Next
        .Display
        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

  5. #175
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,656
    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 kratz101 View Post
    What I need to do now is be able to hide the sheet that is being converted to a PDF, is this possible?

    Code:
      ' Export activesheet as PDF
      With Sheets("Freight Booking")
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      End With
    Hi and welcome to the Board!

    Below of this line of the code:
    .ExportAsFixedFormat ...
    insert one more line:
    .Visible = xlSheetHidden

    Note: at least one sheet in workbook should stay unhidden
    Vladimir Zakharov

  6. #176
    New Member
    Join Date
    Aug 2016
    Posts
    4
    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

    Thanks for the quick response ZVI, the below works on the first run (sheet hides and export to email works) although on the second run (with the sheet still hidden) I get run-time error 5 - Invalid procedure call or argument.

    To clarify I would like the sheet to remain hidden at all times, while still allowing export to PDF.

    Code:
      ' Export activesheet as PDF
      With Sheets("Freight Booking")
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Visible = xlSheetHidden
      End With

  7. #177
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,656
    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

    Well, then use this code modification:
    Code:
      ' Export activesheet as PDF
      With Sheets("Freight Booking")
        .Visible = xlSheetVisible
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Visible = xlSheetHidden
      End With
    Last edited by ZVI; Aug 26th, 2016 at 02:00 AM.
    Vladimir Zakharov

  8. #178
    New Member
    Join Date
    Sep 2016
    Posts
    1
    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 All, Many thanks for the nice coding.
    I have a small question is it possible to capture the system date in the email ?

  9. #179
    Board Regular
    Join Date
    May 2015
    Posts
    52
    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

    Many possibilities. Google "excel vba date". This page is useful: http://www.globaliconnect.com/excel/...=79&Itemid=475
    Last edited by MilkyTech; Sep 19th, 2016 at 10:54 AM.

  10. #180
    New Member
    Join Date
    Sep 2016
    Location
    United Kingdom
    Posts
    6
    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

    Thanks for all of the great codes they've been all really helpful so far.

    Is it possible to edit the attached code so that I can extract multiple sheets to PDF and attach them to the one email?

    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("G5")
     
      ' 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 = "Payroll Monthly Analysis"
        .To = Range("L3").Value
        .CC = Range("L4").Value
        .Body = "Hi," & vbLf & vbLf _
              & "Please find the latest payroll report attached" & vbLf & vbLf _
              & "Regards," & vbLf & vbLf
        .Attachments.Add PdfFile
           
        ' Try to send
        On Error Resume Next
        .Display
        Application.Visible = True
        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
    Any assistance is greatly appreciated. I have looked through all of the previous replies and couldn't see anywhere that this issue had been addressed previously.

    Regards

    Will

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
  •