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

[aarondesin91]

Dear Forumers,

I really need your help. I am new to this whole VBA coding thing have no basic at all in programming and stuff so please help me out here. I am currently assigned a project where I have to create a excel sheet which act as a templete for sending request. The requirement of the project is that I need a vba code for a button when i click it, it will convert my active sheet alone to pdf, automatically save it with the title captured from a cell in the active sheet which is entered by the user. Email this pdf as a attachment to the specific person. Please help me out, my job depends on this project please guys out there.

Thank you

 

[MilkyTech]

As for the filename, you will need something like the following:

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.

 

[aashwinjain]

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

 

[MilkyTech]

----------------------------------------------------------------------------------------------------------------------------------
...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:

[COLOR=#333333].Body = [SIZE=2][FONT=lucida sans unicode]"[/FONT][/SIZE][/COLOR][SIZE=2][FONT=lucida sans unicode][B]Hi " & Range("A1").Value & ".  Please find the attached pdf for your reference." & vbLf & vbLf _[/B][/FONT][/SIZE]

 

[ajaydeepakson]

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

 

[michaelt721]

Hi,

I tried this code and I get an error "Email not sent"

Any ideas?

 

[mshaynerush]

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

The template code for Excel 2007+ with its own PDF converter:

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

 

[MilkyTech]

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

you need to adjust this section:

' 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:

' Define PDF filename
PdfFile = ActiveSheet.Name & ".pdf"

You can make the filename anything you want in this section

 

[andyswin]

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

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

 

[MilkyTech]

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:

  With ActiveSheet

To this:

  With Sheets[COLOR=#000000][FONT=Consolas][B]([/B][/FONT][/COLOR][COLOR=blue][FONT=Consolas][B]"Your Sheet Name"[/B][/FONT][/COLOR][COLOR=#000000][FONT=Consolas][B])[/B][/FONT][/COLOR]

 

[andyswin]

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?