Page 11 of 28 FirstFirst ... 91011121321 ... LastLast
Results 101 to 110 of 277

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

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

    As for the filename, you will need something like the following:
    Code:
    Sub AttachActiveSheetPDF_02() 
      Dim IsCreated As Boolean
      Dim PdfFile As String, Title As String, signature As String
      Dim OutlApp As Object
      Dim char As Variant
      Dim today As String
      
      
      today = Date
      today = Format(Date, "mm-dd-yyyy")
      
      ' Change to suit
      Title = "Order for " & Range("C3") & "_" & today
    
        With ThisWorkbook
        PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"
        End With
      
      
      With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
      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
      On Error GoTo 0
     
      With OutlApp.CreateItem(0)
       
        .Subject = "..."
        .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
        .Send
        
        On Error Resume Next
       
        ' Return focus to Excel's window
        Application.Visible = True
        If Err Then
          MsgBox "E-mail was not sent", vbExclamation
        Else
        End If
        On Error GoTo 0
     
      End With
      
      ' Try to quit Outlook if it was not previously open
      If IsCreated Then OutlApp.Quit
     
      ' Release the memory of object variable
      ' Note: sometimes Outlook object can't be released from the memory
      Set OutlApp = Nothing
    End Sub
    Just remember that this will save a copy of the pdf to your desktop.
    Last edited by MilkyTech; Aug 10th, 2015 at 05:54 PM.

  2. #102
    New Member
    Join Date
    Aug 2015
    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

    Quote Originally Posted by ZVI View Post
    Hi and welcome to MrExcel Message Board!

    Uncomment the line of code with .To = "..."
    and write it something like this: .To = Range("A1").Value
    or: .To = ActiveCell.Value
    or for 2 recipients in cells A1:A2: .To = Range("A1").Value & ";" & Range("A2").Value
    and so on

    Regards,

    ----------------------------------------------------------------------------------------------------------------------------------

    Dear ZVI : thanx for sharing the wonderful code. Works for us perfectly well. We are trying to do a small modification and are stuck.

    In the body section of the mail : We want the following :

    Hi { name of the person stored in A1 cell }. Please find attached the pdf for your ref.

    For the folllowing we did this modification but is not working. Please help

    .Subject = Title
    .To = Mail ' <-- Put email of the recipient here
    .CC = "info@flygoldfinch.com" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi " ";" range("a1").value ": Please find attached the pdf for your ref," & vbLf & vbLf _
    & "See the attached requiest in PDF format." & vbLf & vbLf _
    & "Regards," & vbLf _
    & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile

  3. #103
    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

    Quote Originally Posted by aashwinjain View Post
    ----------------------------------------------------------------------------------------------------------------------------------
    ...In the body section of the mail : We want the following :

    Hi { name of the person stored in A1 cell }. Please find attached the pdf for your ref.
    try this:

    Code:
    .Body = "Hi " & Range("A1").Value & ".  Please find the attached pdf for your reference." & vbLf & vbLf _

  4. #104
    New Member
    Join Date
    Aug 2015
    Posts
    12
    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 milky tech !! thanx for the previous revert.
    Could you help us out with two more things ::

    1. After i run this code on button click : I want to create pdf of a non active sheet and not the active sheet
    2. How do i save the files on a specific folder/drive.
    PS : We want tot save the file on one drive folder which is located on the d drive

    Thanx for the update

  5. #105
    New Member
    Join Date
    Aug 2015
    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 tried this code and I get an error "Email not sent"

    Any ideas?

  6. #106
    Board Regular
    Join Date
    Oct 2012
    Posts
    96
    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

    This code below names my pdf WORKBOOKNAME_Sheet Name
    Can it just be the sheet name only??

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

  7. #107
    Board Regular
    Join Date
    May 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mshaynerush View Post
    This code below names my pdf WORKBOOKNAME_Sheet Name
    Can it just be the sheet name only??
    you need to adjust this section:
    Code:
    ' Define PDF filename
      PdfFile = ActiveWorkbook.FullName
      i = InStrRev(PdfFile, ".")
    If i > 1 Then PdfFile = Left(PdfFile, i - 1)
      PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

    to read:
    Code:
    ' Define PDF filename
    PdfFile = ActiveSheet.Name & ".pdf"
    You can make the filename anything you want in this section

  8. #108
    New Member
    Join Date
    Nov 2015
    Posts
    12
    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,

    The code below works perfectly for what I am trying to do, but please can I ask (my VBA knowledge is really limited):

    Is there a way to change the code so that I can have it print a specific worksheet within the workbook? As opposed to the current sheet?

    I have several different sheets within the workbook, and I want to manipulate the code so that it prints one of the sheets from a 'control panel' on another worksheet... if that makes any sense?

    Any help would be appreciated!
    Andy

    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

  9. #109
    Board Regular
    Join Date
    May 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by andyswin View Post
    Hi all,

    Is there a way to change the code so that I can have it print a specific worksheet within the workbook? As opposed to the current sheet?
    Change this line:
    Code:
      With ActiveSheet
    To this:

    Code:
      With Sheets("Your Sheet Name")

  10. #110
    New Member
    Join Date
    Nov 2015
    Posts
    12
    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. I know this might sound like another silly question, but if I want to add more than one recipient, do I do this by separating an email address with a comma and then put the additional address in quotation marks?

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
  •