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

 

[mikeflo94]

Hello Everyone,

Great Code by the way MVP. Thank you so much. I been reading the entire thread and try a few things but nothing seems to work. I just need to add a custom pdf tittle that could be set in the vba code or in a cell that I can link to the vba code. And how do I add the default signature from outlook to show in the email.

Hope someone can help me out. Thank you.

 

[mikeflo94]

Hello Everyone,

Great Code by the way MVP. Thank you so much. I been reading the entire thread and try a few things but nothing seems to work. I just need to add a custom pdf tittle that could be set in the vba code or in a cell that I can link to the vba code. And how do I add the default signature from outlook to show in the email.

Hope someone can help me out. Thank you.

OKay So I got this resolved. Is there a way to save the pdf file in a specific location after the emails are send? or even to the same folder where the workbook is saved?

Thank you.

 

[poochy16]

Hi all

Code is working great for me, with one little hiccup. My temp PDF file that's being emailed is ~1,000 pages long, despite only having the data I want to send on the first page. I've been trying to figure out the root cause with little success. Is it possible that I simply have the print area defined incorrectly?

Thanks in advance

 

[mikeflo94]

You might want to set the Print Area to only the range you want to send.

 

[mikeflo94]

Is there away to send the file as an excel file and not as pdf?

 

[Mike_Hall_SP_PA]

Afternoon ZVI.
I have been playing with a VBA that when I click a button in excel it activates the macro that sends the active sheet as a pdf to two pre-defined addresses. I am using Office 2010 Pro. It worked once then I now receive a Run-time error '2147024773 (8007007b)':
Document not saved.
The code I used actually looks to be identical to the one on page one.
The code is below, the section highlighted BOLD is where the debugger stops. It will not allow me to step past either.
The excel file is saved on my desktop.
Any help would be greatly appreciated, thanks in advance

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 & Format(Now, "MM-DD-YYYY HH:mm:ss") & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
[B]    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False[/B]
  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 = "email1@email.com" ' <-- Put email of the recipient here
    .CC = "email2@email.com" ' <-- 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
[CODE]

 

[ZVI]

Afternoon ZVI.
I have been playing with a VBA that when I click a button in excel it activates the macro that sends the active sheet as a pdf to two pre-defined addresses. I am using Office 2010 Pro. It worked once then I now receive a Run-time error '2147024773 (8007007b)':
Document not saved.
The code I used actually looks to be identical to the one on page one.

Hi Mike,

Please try a more safe version of the code in the post #43 with description of possible problems (see points 2, 3, 4), and let us know whether the problem is solved or not.

Regards

 

[Mike_Hall_SP_PA]

Thanks Vladimir,
I was just about to respond as I got it to work on my own, I even got the date appended to the name of the file when it emailed. My part that I am now stuck on is I am guessing simpler. I also need the time added to the name of the file. Anytime I change the "format" code it gives an error. Here is what I am using:

PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & FormatDateTime(Now, 1) & "_" & ".pdf"

I have attempted to change the 1 to a 4 to see if it would just put the time in, and I get the same debug error.
Ideas?
thanks
Mike

 

[ZVI]

Try this:
PdfFile = PdfFile & "_" & Format(Now, "yyyyddmm_hhmmss") & ".pdf"

Please pay your attention on the fact that symbol ":" from FormatDateTime is illegal for file name

 

[janarocky]

Try this:

Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  Title = "Request Form for " & Range("A1").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".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 _
          & "See the attached requiest in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    Application.Visible = True
    .Display
  End With
 
  ' Quit Outlook if it was not already open
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

Hi Sir,

This is working perfectly. I just need one more thing. How to add a excel range into outlook body. The range is in sheet2. A1:D56.
I want this range to be pasted at outlook body as unformatted text.

Please advice.

Thanks in advance.