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

aarondesin91

New Member
Joined
Jun 23, 2013
Messages
7
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
 
You don't want to save your own copy of the pdf? you only want to attach it, then delete the temp file?

I don't need to keep a copy for myself. If I have to to get it to work that's fine. All of this code is cobbled together mostly from this thread and a few other details from other places so if you can show me exactly what needs to be added or removed I would be grateful.
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I don't need to keep a copy for myself. If I have to to get it to work that's fine. All of this code is cobbled together mostly from this thread and a few other details from other places so if you can show me exactly what needs to be added or removed I would be grateful.

You haven't defined a path for the temp file, so when the code tries to attach the file it can't find it.

Change the line after `Next` to:
Code:
Next
   PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"
 
Upvote 0
ZVI,

I know it has been a while since you have been on this thread, but I am wondering if you could supply the code needed to format the PDF as follows:

1) A selected range of (A1:E100)
2) Fit colums on one page

Thanks for the help!

Also, is there a way to use the Outlook defined signature rather than an excel defined signature?
 
Upvote 0
...code needed to format the PDF as follows:
1) A selected range of (A1:E100)
2) Fit colums on one page
Also, is there a way to use the Outlook defined signature rather than an excel defined signature?

This code should do just about everything you want plus a lot more. There are a couple of options: You can either manually select the cells that you want printed to pdf or, if the range is always going to be the same, we can set that range.
For this example I have set the range to be printed to pdf but we will also be able to place a manually selected range in the body of the email. All in all this code will:


  1. Print a set range of cells to pdf and fit columns to 1 page width
  2. Name and save the pdf to the same folder that the excel file resides (we can make this a temp folder then delete the file after attaching if you don't want to save your own copy)
  3. Fill out a new email using the default outlook account
  4. Add the manually selected range of cells to the body of the email
  5. Attach the pdf to the email
  6. Add the default outlook account's signature

Code:
Sub printSelection()  
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String, signature As String
  Dim OutlApp As Object
  Dim RngCopied As Range


  Set RngCopied = Selection
 
  ' Here we define the pdf path and filename
  Title = Range("B11") & " PROPOSAL" ' I only use this line to help fill out the email subject.  It is not necessary
    With ThisWorkbook
    PdfFile = .Path & Application.PathSeparator & _
              .Sheets("Sheet1").Range("B11")
    End With
  
  With ActiveSheet.PageSetup
        .FitToPagesWide = 1
        .Zoom = False
  End With
  
  ' Export activesheet as PDF to the current folder
  With ActiveSheet
    Range("A1:E100").Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile & " Proposal.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
  End With
  
   With ThisWorkbook
    PdfFile = PdfFile & " Proposal.pdf"
    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
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    .Display         ' We need to display email first for signature to be added
    .Subject = Title
    .To = ActiveSheet.Range("E10").Value ' <-- Put email of the recipient here or use a cell value
    .CC = "whoever@abc.com; copy@abc.com" ' <-- Put email of 'copy to' recipients here
    .HTMLBody = "Thank you for the opportunity to bid on the painting for " & ActiveSheet.Range("B9").Value & ". " & " Please read our attached proposal in it's entirety to be sure of all inclusions, exclusions, and products proposed.  Give us a call with any questions or concerns." & _
        vbNewLine & vbNewLine & _
        RangetoHTML(RngCopied) & _
        "Thank you," & _
        .HTMLBody      ' Adds default outlook account signature
    .Attachments.Add PdfFile
   
    
    On Error Resume Next
    
   
    ' Return focus to Excel's window
    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
    
  ' 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




Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
 
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    'Close TempWB
    TempWB.Close savechanges:=False
 
    'Delete the htm file we used in this function
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Last edited:
Upvote 0
You haven't defined a path for the temp file, so when the code tries to attach the file it can't find it.

Change the line after `Next` to:
Code:
Next
   PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"

Thanks Milky you rock!
 
Upvote 0
ok..i have this question...and i dont know where to post it...I have a basic knowledge about excel and am trying to learn new things…need some help with this..Example:I have made a Time sheet in excel for John..as John is not tech with IT….in this time sheet I have added details,login,logout,formulas for calculating the total hours etc…so when John receives it he just have to put the login and logout timings and the remaining is calculated automatically…
so here is my question,
1)can i email this active workbook as a body of the email(Not as attachment) to John??2)And when John receives the email he should be able to fill the sheets with timings in the active worksheet in the email itself and forward me back the same email.(No need to download,fill,attachment and send back headache for John)3)which mail supports this….I use outlook 2007…so I was just wondering is there any thing like this and Detailed Answer is much appreciated…Thanks in advance
 
Upvote 0
...1)can i email this active workbook as a body of the email(Not as attachment)
workbook or worksheet? I'll assume you meant worksheet. The code in post #95 will do that for you with a little manipulation. It only works with outlook so you're good there. It's well commented so should be easy for you to figure out. Give it a shot and let me know how it went.
 
Upvote 0
...and Detailed Answer is much appreciated
This is what you should be shooting for. If you really want to learn something and not just have someone else write your code for you, compare this code with the code in post #95 and take notice the changes I made.
You can paypal donations to milkytech @ gmail . com
:beerchug:
Code:
Sub pasteSelection()  
  Dim IsCreated As Boolean
  Dim Title As String, signature As String
  Dim OutlApp As Object
  Dim RngCopied As Range
 
  
  ' Select the range of cells you want pasted in the email body
  With ActiveSheet
    Range("A1:E100").Select
    Set RngCopied = Selection
  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
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    .Display         ' We need to display email first for signature to be added
    .Subject = Title
    .To = ActiveSheet.Range("E10").Value ' <-- Put email of the recipient here or use a cell value
    .CC = "whoever@abc.com; copy@abc.com" ' <-- Put email of 'copy to' recipients here
    .HTMLBody = "Please reply to this email to be able to fill out the time sheet below:" & _
        vbNewLine & _
        RangetoHTML(RngCopied) & _
        "Thank you," & _
        .HTMLBody      ' Adds default outlook account signature
   
    
    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




Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
 
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    'Close TempWB
    TempWB.Close savechanges:=False
 
    'Delete the htm file we used in this function
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Last edited:
Upvote 0
Hi, This is my first post to this Forum, and from what I've read it looks very helpful. I am trying to do the same as the original poster, that is to save the file as a PDF and e-mail it to a set e-mail address. I have put a button on my worksheet, and have got ZVI's code to work form page 2 to work. However, when I run the macro, Outlook opens, with an e-mail ready to send the PDF, BUT doesn't actually send the e-mail, outlook just asks" Do you want to save the changes" What is going wrong?

Also I would like the code to create the PDF filename as "Order for" + reference cell C3 in worksheet (contains customer name) + todays date on the end.

No signature is required, and it would be best if this process runs in the background without user input. Confirmation of the order having been sent would also be useful.

Please note that I have little to no experience with VBA, and I will need all the assistance you can spare. My apologies and thanks, programming is not a strong-point of mine.

My computer is running excel 2013 and outlook 2013.

Thanks in advance for your help,

David
 
Upvote 0
...when I run the macro, Outlook opens, with an e-mail ready to send the PDF, BUT doesn't actually send the e-mail, outlook just asks" Do you want to save the changes" What is going wrong?

Also I would like the code to create the PDF filename as "Order for" + reference cell C3 in worksheet (contains customer name) + todays date on the end.

No signature is required, and it would be best if this process runs in the background without user input. Confirmation of the order having been sent would also be useful.

What is happening is that ZVI's code on page 2 has a line of code that opens Outlook if it isn't already open, then the email is created, then the email is set to `.display` rather than `.send`, then finally there is a line of code that closes Outlook if it wasn't already open. Because outlook is trying to close while still having an open email that hasn't been sent is why it is asking you to save.

You can solve this easily by switching `.display` to `.send`, then Outlook will immediately send the email as soon as it finishes filling it out.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top