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]

Did you try running the code exactly as I posted it without any modifications? I would start there since I tested it successfully many times.
If you still get an error with my code, unaltered, then something is wrong with your setup. Are you sure your Z drive is accessible? Try my original code but change "Z:\" to "C:\" and see if it works on your local C drive.
If you are successful with either of these attempts, then you know the problem is with your changes. Start to make your changes one at a time, testing each time, until you get an error, then you will see where you went awry.

 

[rbrace]

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

I am trying the above modification to name the PDF file the same as the sheet name but it errors on line .Attachments.Add PdfFile
What am I doing wrong?

Thank you for the help,

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

 

[MilkyTech]

You need to define the complete path in order for the code to find the pdf file and attach it. You have only given the pdf a filename, but no path.
Something like this will define the same path that the current workbook resides:

 With ThisWorkbook
    PdfFile = .Path & Application.PathSeparator & ActiveSheet.Name & ".pdf"
 End With

 

[CodyMonster]

Thanks everyone for the quick resource on this,its been a great to have this available. However, I've read through all of the 14 pages and I can't get a answer to this.
For some reason the line in the code at With OutlApp.CreateItem(0) doesn't make the new email message pop up.
Everything else works, PDF file created, etc. But, I can't get Outlook to automate. Don't get any errors, just doesn't execute the outlook commands.
Has anyone has had the same problems?
Thanks for any help..

 

[MilkyTech]

Post your code for us

 

[CodyMonster]

Post your code for us

Its the same as the original 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("A2")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
 Debug.Print PdfFile
 
  ' 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)  [COLOR=#ff0000]<---- doesn't do anything[/COLOR]
   
    ' 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]

You have your code set up to immediately send the email (.Send) so you won't see it pop up. check your sent mailbox and you should see the email with attachment sent. your code works fine for me.

Change .Send to .Display and then the email will pop up for you to make adjustments and manually send.

 

[CodyMonster]

You have your code set up to immediately send the email (.Send) so you won't see it pop up. check your sent mailbox and you should see the email with attachment sent. your code works fine for me.

Change .Send to .Display and then the email will pop up for you to make adjustments and manually send.

Hey, it worked!! Okay, I feel dumb now!

 

[zoetwodot]

Did you try running the code exactly as I posted it without any modifications? I would start there since I tested it successfully many times.
If you still get an error with my code, unaltered, then something is wrong with your setup. Are you sure your Z drive is accessible? Try my original code but change "Z:\" to "C:\" and see if it works on your local C drive.
If you are successful with either of these attempts, then you know the problem is with your changes. Start to make your changes one at a time, testing each time, until you get an error, then you will see where you went awry.

Can you please clarify how to instruct the PDF to be saved on a network folder? Everything else is working except that step. The filepath is P:\Finance\Trade Tickets Scanned if that could be used in the example.

(1 million thank yous to all involved in this thread)

Z

 

[MilkyTech]

let me see your code please. It should be as simple as typing the path as you have. test on your local drive. post your code.