Page 19 of 28 FirstFirst ... 91718192021 ... LastLast
Results 181 to 190 of 277

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

  1. #181
    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

    Do you mean all sheets should be in the attached PDF file or only some of them?
    Vladimir Zakharov

  2. #182
    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

    The below code sends Sheet1 and Sheet3 in the attached single PDF file.
    Those sheets are listed in MySheets constant in the top of the code, modify it as required.
    To send all sheets use Const MySheets As Variant = 0
    Signature is added to the bottom of email.
    [code]Sub Attach_Sheets_As_Pdf_With_Signature()
    ' ZVI:2016-09-20 http://www.mrexcel.com/forum/excel-questions/710212-visual-basic-applications-code-convert-excel-pdf-email-attachment-5.html#post4636652

    ' --> User settings, change to suit
    Const MySheets As Variant = "Sheet1,Sheet3" ' Use MySheets = 0 for all the sheets
    Const IsDisplay As Boolean = True ' Change to False to .Send instead of .Display
    Const IsSilent As Boolean = False ' Change to True to Send without the confirmation MsgBox
    ' <-- End of settings

    Dim IsCreated As Boolean
    Dim PdfFile As String, Signature As String
    Dim OutlApp As Object
    Dim i As Long
    Dim char As Variant

    ' Define PDF filename
    PdfFile = ActiveWorkbook.Name
    i = InStrRev(PdfFile, ".xl", , vbTextCompare)
    If i > Len(PdfFile) - 5 Then PdfFile = Left(PdfFile, i - 1)
    PdfFile = PdfFile & "_" & ActiveSheet.Name
    ' Clean up the name of PDF file
    For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
    Next
    ' Add %TEMP% path to the file name and limit too long name
    PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"

    ' Try to delete PDF file for the case it was not deleted at debugging
    If Len(Dir(PdfFile)) Then Kill PdfFile

    ' Select sheets to be exported in the PDF (single) file
    If MySheets = 0 Then
    ' All sheets to PDF
    Sheets.Select
    Else
    ' Sheets listed in MySheets to PDF
    Sheets(Split(MySheets, ",")).Select
    End If

    ' Export the selected sheets as PDF to the temporary folder
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    .Select
    End With

    ' Use the 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
    On Error GoTo 0

    ' Prepare email with PDF attachment and default signature
    With OutlApp.CreateItem(0)

    ' Add the attachment first for correct attachment's name with non English symbols
    .Attachments.Add PdfFile

    ' Get default email signature without blinking (instead of .Display method)
    With .GetInspector: End With
    Signature = .Body

    ' Prepare e-mail (uncommenmt and fill the lines below)
    .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 _
    & Signature

    ' Try to send or just display the e-mail
    On Error Resume Next
    If IsDisplay Then .Display Else .Send

    ' Show error of .Send method
    If Not IsDisplay Then
    ' Return focus to Excel's window
    Application.Visible = True
    ' Report on error or success
    If Err Then
    MsgBox "E-mail was not sent for some reasons" & vbLf & "Please check it", vbExclamation
    .Display
    Else
    If Not IsSilent Then
    MsgBox "E-mail successfully sent", vbInformation
    End If
    End If
    End If
    On Error GoTo 0

    End With

    ' Delete the temporary PDF file
    If Len(Dir(PdfFile)) Then Kill PdfFile

    ' Try to quit Outlook if it was not previously open
    If IsCreated Then OutlApp.Quit

    ' Try to release the memory of object variable%
    Last edited by ZVI; Sep 20th, 2016 at 01:55 PM.
    Vladimir Zakharov

  3. #183
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,288
    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 ZVI,

    Awesome thread , there are so many pages on here so i was hoping you could help me and advise which one suits best for my situation...

    I am using excel 2013

    I want to be able to email certain sheets as PDF but not the whole range
    I have sheets called Dashboard and WeeklyInput
    I want to be able to export range A1 to X500 as PDF from the dashboard sheet and range A1:D200 to export from sheet weekly
    I want to then SetPrint are to these ranges and then print them also..

    The PDF file should be titled Summary for and what the previous date was

    My sheets have shapes and charts therefore i would want it to look exactly the same as it looks onmy excel sheets but as PDF file

    Hopefully you can help me tweak your existing code to achieve this..

    Many Thanks
    Last edited by mahmed1; Sep 20th, 2016 at 01:58 PM.

  4. #184
    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 ZVI View Post
    The below code sends Sheet1 and Sheet3 in the attached single PDF file.
    Those sheets are listed in MySheets constant in the top of the code, modify it as required.
    To send all sheets use Const MySheets As Variant = 0
    Signature is added to the bottom of email.
    I repost the code here:
    Code:
    Sub Attach_Sheets_As_Pdf_With_Signature()
    ' ZVI:2016-09-20 http://www.mrexcel.com/forum/excel-questions/710212-visual-basic-applications-code-convert-excel-pdf-email-attachment-5.html#post4636678
     
      ' --> User settings, change to suit
      Const MySheets As Variant = "Sheet1,Sheet3" ' Use MySheets = 0 for all the sheets
      Const IsDisplay As Boolean = True           ' Change to False to .Send instead of .Display
      Const IsSilent As Boolean = False           ' Change to True to Send without the confirmation MsgBox
      ' <-- End of settings
     
      Dim IsCreated As Boolean
      Dim PdfFile As String, Signature As String
      Dim OutlApp As Object
      Dim i As Long
      Dim char As Variant
     
      ' Define PDF filename
      PdfFile = ActiveWorkbook.Name
      i = InStrRev(PdfFile, ".xl", , vbTextCompare)
      If i > Len(PdfFile) - 5 Then PdfFile = Left(PdfFile, i - 1)
      PdfFile = PdfFile & "_" & ActiveSheet.Name
      ' Clean up the name of PDF file
      For Each char In Split("? "" / \ < > * | :")
        PdfFile = Replace(PdfFile, char, "_")
      Next
      ' Add %TEMP% path to the file name and limit too long name
      PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"
     
      ' Try to delete PDF file for the case it was not deleted at debugging
      If Len(Dir(PdfFile)) Then Kill PdfFile
     
      ' Select sheets to be exported in the PDF (single) file
      If MySheets = 0 Then
        ' All sheets to PDF
        Sheets.Select
      Else
        ' Sheets listed in MySheets to PDF
        Sheets(Split(MySheets, ",")).Select
      End If
     
      ' Export the selected sheets as PDF to the temporary folder
      With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Select
      End With
     
      ' Use the 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
      On Error GoTo 0
     
      ' Prepare email with PDF attachment and default signature
      With OutlApp.CreateItem(0)
      
        ' Add the attachment first for correct attachment's name with non English symbols
        .Attachments.Add PdfFile
      
        ' Get default email signature without blinking (instead of .Display method)
        With .GetInspector: End With
        Signature = .Body
     
        ' Prepare e-mail (uncommenmt and fill the lines below)
        .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 _
              & Signature
      
        ' Try to send or just display the e-mail
        On Error Resume Next
        If IsDisplay Then .Display Else .Send
      
        ' Show error of .Send method
        If Not IsDisplay Then
          ' Return focus to Excel's window
          Application.Visible = True
          ' Report on error or success
          If Err Then
            MsgBox "E-mail was not sent for some reasons" & vbLf & "Please check it", vbExclamation
            .Display
          Else
            If Not IsSilent Then
              MsgBox "E-mail successfully sent", vbInformation
            End If
          End If
        End If
        On Error GoTo 0
     
      End With
     
      ' Delete the temporary PDF file
      If Len(Dir(PdfFile)) Then Kill PdfFile
     
      ' Try to quit Outlook if it was not previously open
      If IsCreated Then OutlApp.Quit
     
      ' Try to release the memory of object variable
      Set OutlApp = Nothing
     
    End Sub
    Vladimir Zakharov

  5. #185
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,288
    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 you

    Does this do the whole sheet as PDF (certain ranges are just there for calculation therefore these ranges dnt need to be exported as PDF..

    I will therefore need certain ranges from the 2 sheets to be exported and then printed also, if its not too much, i will need to attach the workbook also
    reason why i need both is because certain managers just want to see the dashboard view (PDF) and certain managers want access to workbook

  6. #186
    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

    Hi Mahmed,
    Try this code.
    Modify constants in the top of the code according to their comments.
    Code:
    Sub Attach_Ranges_With_Signature()
    ' ZVI:2016-09-20 http://www.mrexcel.com/forum/excel-questions/710212-visual-basic-applications-code-convert-excel-pdf-email-attachment-post4636746.html#post4636746
     
      ' --> User settings, change to suit
      Const Sh1 = "Dashboard"             ' Sheet1 to be attached in PDF
      Const Rng1 = "A1:X500"              ' The range of sheet1
      Const Sh2 = "WeeklyInput"           ' Sheet2 to be attached in that PDF too
      Const Rng2 = "A1:D200"              ' The range of sheet2
      Const IsAttachWb As Boolean = True  ' True to attach active workbook as well
      Const IsDisplay As Boolean = True   ' Change to False to .Send instead of .Display
      Const IsSilent As Boolean = False   ' Change to True to Send without the confirmation MsgBox
      ' <-- End of settings
     
      Dim IsCreated As Boolean
      Dim PdfFile As String, Signature As String
      Dim OutlApp As Object
      Dim i As Long
      Dim char As Variant
     
      ' Prepere Dashboard for PDF
      With Sheets(Sh1).Cells
        .EntireColumn.Hidden = True
        .EntireRow.Hidden = True
        With .Range(Rng1)
          .EntireColumn.Hidden = False
          .EntireRow.Hidden = False
        End With
      End With
     
      ' Prepere Dashboard for PDF
      With Sheets(Sh2).Cells
        .EntireColumn.Hidden = True
        .EntireRow.Hidden = True
        With .Range(Rng2)
          .EntireColumn.Hidden = False
          .EntireRow.Hidden = False
        End With
      End With
     
      ' Define PDF filename
      PdfFile = "Summary"
      ' Add %TEMP% path to the file name and limit too long name
      PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"
     
      ' Try to delete PDF file for the case it was not deleted at debugging
      If Len(Dir(PdfFile)) Then Kill PdfFile
     
      ' Select sheets to be exported in the PDF (single) file
      Sheets(Split("Dashboard,WeeklyInput", ",")).Select
     
      ' Export the selected sheets as PDF to the temporary folder
      With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Select
      End With
     
      ' Restore Dashboard for PDF
      With Sheets(Sh1).Cells
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
      End With
     
      ' Retore Dashboard for PDF
      With Sheets(Sh2).Cells
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
      End With
     
      ' Use the 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
      On Error GoTo 0
     
      ' Prepare email with PDF attachment and default signature
      With OutlApp.CreateItem(0)
      
        ' Add the attachment first for correct attachment's name with non English symbols
        .Attachments.Add PdfFile
        If IsAttachWb Then .Attachments.Add ThisWorkbook.FullName
      
        ' Get default email signature without blinking (instead of .Display method)
        With .GetInspector: End With
        Signature = .Body
     
        ' Prepare e-mail (uncommenmt and fill the lines below)
        .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 _
              & Signature
      
        ' Try to send or just display the e-mail
        On Error Resume Next
        If IsDisplay Then .Display Else .Send
      
        ' Show error of .Send method
        If Not IsDisplay Then
          ' Return focus to Excel's window
          Application.Visible = True
          ' Report on error or success
          If Err Then
            MsgBox "E-mail was not sent for some reasons" & vbLf & "Please check it", vbExclamation
            .Display
          Else
            If Not IsSilent Then
              MsgBox "E-mail successfully sent", vbInformation
            End If
          End If
        End If
        On Error GoTo 0
     
      End With
     
      ' Delete the temporary PDF file
      If Len(Dir(PdfFile)) Then Kill PdfFile
     
      ' Try to quit Outlook if it was not previously open
      If IsCreated Then OutlApp.Quit
     
      ' Try to release the memory of object variable
      Set OutlApp = Nothing
     
    End Sub
    Last edited by ZVI; Sep 20th, 2016 at 02:56 PM.
    Vladimir Zakharov

  7. #187
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,288
    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 ZVI,

    Firstly i want to say thank you so so so much..

    Honestly you dont know how helpful you have been..

    I was hoping you could help me with the last bit to go 1 step further..

    The top msnagers also like to see PowerPoint prentation slides that focus on each are. We have a default PowerPoint template that is used through the business..

    Is it possible to have a PowerPoint reprentation on of each section into its own slides?

    e.g

    I have named range area Work in progress..

    A5:J30 (that is the range that holds my data and the charts)
    A35:J55 (that holds another section with charts)etc..

    I have several of these ranges named..

    I have to every other day copy those ranges and show those sections on PowerPoint on its own slide (resize the image) for that section nicely on each slide so that the senior managers can go through each slide in their meetings..(I normally coppy the range as a picture and then paste into PowerPoint slide and resize) (sometimes the image looks blurry tho

    Would that be possible...you would help my day to day job massively if i can automate this and i can imagine helping so many others also who will no doubt have the same problem..

    If you can please come up with some code that will do this i am happy to even pay for this to show my appreciation- thank you

  8. #188
    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

    Thank you for your help!

    The code works perfectly

    Regards

    Will

  9. #189
    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

    Hi everyone.

    I'm happy with everything that the attached code does however is it possible to amend the email signature so that it retains the formatting? It is currently coming through as plain text but it is necessary to retain the formatting due to the standardization of our corporate communications.
    [CODE][Sub Attach_Sheets_As_Pdf_With_Signature()
    ' ZVI:2016-09-20 http://www.mrexcel.com/forum/excel-q...ml#post4636678

    ' --> User settings, change to suit
    Const MySheets As Variant = "SUMMARY,PAYROLL,MILEAGE,OVERTIME" ' Use MySheets = 0 for all the sheets
    Const IsDisplay As Boolean = True ' Change to False to .Send instead of .Display
    Const IsSilent As Boolean = True ' Change to True to Send without the confirmation MsgBox
    ' <-- End of settings

    Dim IsCreated As Boolean
    Dim PdfFile As String, Signature As String
    Dim OutlApp As Object
    Dim i As Long
    Dim char As Variant

    ' Define PDF filename
    PdfFile = ActiveWorkbook.Name
    i = InStrRev(PdfFile, ".xl", , vbTextCompare)
    If i > Len(PdfFile) - 5 Then PdfFile = Left(PdfFile, i - 1)
    PdfFile = PdfFile & "_" & ActiveSheet.Name
    ' Clean up the name of PDF file
    For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
    Next
    ' Add %TEMP% path to the file name and limit too long name
    PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "" & PdfFile, 251) & ".pdf"

    ' Try to delete PDF file for the case it was not deleted at debugging
    If Len(Dir(PdfFile)) Then Kill PdfFile

    ' Select sheets to be exported in the PDF (single) file
    If MySheets = 0 Then
    ' All sheets to PDF
    Sheets.Select
    Else
    ' Sheets listed in MySheets to PDF
    Sheets(Split(MySheets, ",")).Select
    End If

    ' Export the selected sheets as PDF to the temporary folder
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    .Select
    End With

    ' Use the 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
    On Error GoTo 0

    ' Prepare email with PDF attachment and default signature
    With OutlApp.CreateItem(0)

    ' Add the attachment first for correct attachment's name with non English symbols
    .Attachments.Add PdfFile

    ' Get default email signature without blinking (instead of .Display method)
    With .GetInspector: End With
    Signature = .Body

    ' Prepare e-mail (uncommenmt and fill the lines below)
    .Subject = "Payroll Monthly Analysis"
    .To = Range("I3").Value
    .CC = Range("I4").Value
    .Body = "Hi," & vbLf & vbLf _
    & "Please find the latest payroll report attached" & vbLf & vbLf _
    & Signature

    ' Try to send or just display the e-mail
    On Error Resume Next
    If IsDisplay Then .Display Else .Send

    ' Show error of .Send method
    If Not IsDisplay Then
    ' Return focus to Excel's window
    Application.Visible = True
    ' Report on error or success
    If Err Then
    MsgBox "E-mail was not sent for some reasons" & vbLf & "Please check it", vbExclamation
    .Display
    Else
    If Not IsSilent Then
    MsgBox "E-mail successfully sent", vbInformation
    End If
    End If
    End If
    On Error GoTo 0

    End With

    ' Delete the temporary PDF file
    If Len(Dir(PdfFile)) Then Kill PdfFile

    ' Try to quit Outlook if it was not previously open
    If IsCreated Then OutlApp.Quit

    ' Try to release the memory of object variable
    Set OutlApp = Nothing

    End Sub


    /CODE]

    Regards

    Will
    Once again any assistance is greatly appreciated!

  10. #190
    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 Mindb85 View Post
    Thank you for your help!

    The code works perfectly

    Regards

    Will
    I'm glad it has helped. Welcome to MrExcel Board!
    Vladimir Zakharov

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
  •