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

 

[mahmed1]

Hi ZVI - thank you for your time and making so many of our daily tasks easy - thank you

Is there any chance you could help us with my emailIng PowerPoint query in post 187

if you could please help us with that, that would be awesome..i dont mind paying for it also..

Ps can a range which holds a chart and shapes be copied directly in Outlook?

So say range a5:g45 had shapes and charts and we made a temp copy of this as picture to paste this in outlook body, can that be done?

1) Snapshot of this range onto the body of outlook
2) Email Workbook as attachment
3) Send PDF copy (code you provided)
4) Create PowerPoint Slides for each section

My goal is to have a summary of the dashboard data in outlook, a PowerPoint presentation for the senior managers, a PDF copy as a reference book and the workbook itself for editing purposes

If this is going to take too much of your time i dont mind paying for it but if you can help without me paying for it that would be awesome aswel

 

[ZVI]

... is it possible to amend the email signature so that it retains the formatting? It is currently coming through as plain text but it is necessary to retain the formatting due to the standardization of our corporate communications.

To use signature with formatted text & picture, the .HTMLBody should be used in the code instead of the just .Body
Code in post #159 reflects both methods: using of HTML signature with HTML formatted message, and the plain text message with text of a signature.
Here is a mixed version of the code where plain text of the message is converted to a simple HTML code with full HTML signature

Sub Attach_Sheets_As_Pdf_With_HTMLSignature()
' ZVI:2016-09-21 http://www.mrexcel.com/forum/excel-questions/710212-visual-basic-applications-code-convert-excel-pdf-email-attachment-post4637844.html#post4637844
 
  ' --> User settings, change to suit
  Const MySheets As Variant = "Sheet1,Sheet3" ' Use MySheets = 0 for all the sheets
  Const IsDisplay As Boolean = True           ' Change to False to .Send instead of .Display
  Const IsSilent As Boolean = False           ' Change to True to Send without the confirmation MsgBox
  ' <-- End of settings
 
  Dim IsCreated As Boolean
  Dim PdfFile As String, Signature As String, Message As String
  Dim OutlApp As Object
  Dim i As Long
  Dim char As Variant
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Name
  i = InStrRev(PdfFile, ".xl", , vbTextCompare)
  If i > Len(PdfFile) - 5 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name
  ' Clean up the name of PDF file
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
  ' Add %TEMP% path to the file name and limit too long name
  PdfFile = Left(CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) & "\" & PdfFile, 251) & ".pdf"
 
  ' Try to delete PDF file for the case it was not deleted at debugging
  If Len(Dir(PdfFile)) Then Kill PdfFile
 
  ' Select sheets to be exported in the PDF (single) file
  If MySheets = 0 Then
    ' All sheets to PDF
    Sheets.Select
  Else
    ' Sheets listed in MySheets to PDF
    Sheets(Split(MySheets, ",")).Select
  End If
 
  ' Export the selected sheets as PDF to the temporary folder
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    .Select
  End With
 
  ' Use the 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 email with PDF attachment and default signature
  With OutlApp.CreateItem(0)
  
    ' Add the attachment first for correct attachment's name with non English symbols
    .Attachments.Add PdfFile
  
    ' Get default email signature without blinking (instead of .Display method)
    With .GetInspector: End With
    Signature = .HTMLBody
 
    ' Prepare e-mail (uncommenmt and fill the lines below)
    .Subject = "Payroll Monthly Analysis"
    .To = Range("L3").Value
    .CC = Range("L4").Value
    Message = "Hi," & vbLf & vbLf _
            & "Please find the latest payroll report attached"

    .HTMLBody = Replace(Message, vbLf, Chr(60) & "br" & Chr(62)) & Signature
  
    ' Try to send or just display the e-mail
    On Error Resume Next
    If IsDisplay Then .Display Else .Send
  
    ' Show error of .Send method
    If Not IsDisplay Then
      ' Return focus to Excel's window
      Application.Visible = True
      ' Report on error or success
      If Err Then
        MsgBox "E-mail was not sent for some reasons" & vbLf & "Please check it", vbExclamation
        .Display
      Else
        If Not IsSilent Then
          MsgBox "E-mail successfully sent", vbInformation
        End If
      End If
    End If
    On Error GoTo 0
 
  End With
 
  ' Delete the temporary PDF file
  If Len(Dir(PdfFile)) Then Kill PdfFile
 
  ' Try to quit Outlook if it was not previously open
  If IsCreated Then OutlApp.Quit
 
  ' Try to release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[ZVI]

Hi ZVI - thank you for your time and making so many of our daily tasks easy - thank you

Is there any chance you could help us with my emailIng PowerPoint query in post 187

if you could please help us with that, that would be awesome..i dont mind paying for it also..

Ps can a range which holds a chart and shapes be copied directly in Outlook?

So say range a5:g45 had shapes and charts and we made a temp copy of this as picture to paste this in outlook body, can that be done?

1) Snapshot of this range onto the body of outlook
2) Email Workbook as attachment
3) Send PDF copy (code you provided)
4) Create PowerPoint Slides for each section

My goal is to have a summary of the dashboard data in outlook, a PowerPoint presentation for the senior managers, a PDF copy as a reference book and the workbook itself for editing purposes

If this is going to take too much of your time i dont mind paying for it but if you can help without me paying for it that would be awesome aswel

Hi Mahmed,

Looks like all that is possible but requires some spare time which is not always happens.
Point 1 - Excel 2010+ uses Word application as an email editor, so technically this point seems can be done.
Point 2 - see post #186
Point 3 - the same as above
Point 4 - seems it's out of the scope of this thread subject, if so then it's better to create new thread for it.
This part of the code reflects how to copy Excel's range as picture into the automated application PowerPoint:

  Range("A5:J30").CopyPicture Appearance:=xlScreen, Format:=xlPicture
  With objPowerPoint
    .Presentations.Add
    .ActiveWindow.View.Paste
  End With

But it's unclear if you stuck somewhere in your attempts of coding or the full of code is required.

Regards,

 

[mahmed1]

Hi ZVI,

Thank you once again..

I have no idea how to achieve point 1 and 4 therefore would need full code to get those working..

I really do appreciate all your help and would from the bottom of my heart be greatful if you could help us with this if you can when you are free..

The only reason why I didn't create a new thread is because i was on about the same topic as copying a range as picture and then pasting in each in slide in the powerpoint template


I didnt want to duplicate things..

If you could have a look..that sould be so appreciated..

I wana buy you a drink from me to show my appreciation (this is not you making money or taking money against forum rules) - just a genuine thank you drink

 

[mahmed1]

Hi ZVI, once again thank you

I tested your picture code snippet and that definitely seems to be more down the line of what i need for the PowerPoint slides..

I had a slight issue with pasting it into powerpoint..

It would not let me execute the line of code to paste into powerpoint

I would also like to resize the picture to slide width or resized where its got some spacing around the image and pasted in the middle of the slide

A couple of other things ..I have a Powerpoint Template called Company Template

on this template i have 2 slides

1 homepage slide which has 1 text box
and another slide which is the template slide that should be used throughout the presentation..on this slide there is only 1 text box..

Now what i would like to do is open this template presentation and on the homepage slide (1st slide) i would want to update the rext box to the value which is on Dashboard range A1

on the template slide what i would need to do is first update the textbox to the range i am copying so say my range name is Attendance...
the text box text should say attendance..i would then copy the range as pucture to this slide like the code you provided for the picture creation and then resize

i would repeat this process again ie copy the template slide..fill in the text of the text box to the next range im copying (say its Incoming Volumes)..
the textbox text should say Incoming Volumes
copy the range and then resize

There are several of ranges i need to copy on each slide and the text box text is essentially the title/heading..I have the heading names for each section also ib dashboard sheet range (K1:K15) 15 sections to copy therefore would be creating 15 slides..

I really hope this makes sense..I really appreciate your help and this would help me massively creating the PowerPoint Report ...

Would need help on point 1 also (copy range which has shapes charts etc) and paste into the body of outlook

I am using excel 2013..

Thank you so much

 

[ZVI]

I was hoping you could help me with the last bit to go 1 step further.
The top managers also like to see PowerPoint presentation slides that focus on each are. We have a default PowerPoint template that is used through the business..
Is it possible to have a PowerPoint presentation on of each section into its own slides?

can a range which holds a chart and shapes be copied directly in Outlook?
So say range a5:g45 had shapes and charts and we made a temp copy of this as picture to paste this in outlook body, can that be done?
1) Snapshot of this range onto the body of outlook
2) Email Workbook as attachment
3) Send PDF copy (code you provided)
4) Create PowerPoint Slides for each section
My goal is to have a summary of the dashboard data in outlook, a PowerPoint presentation for the senior managers, a PDF copy as a reference book and the workbook itself for editing purposes

Hi Mahmed,
There is absolutely abnormal amount of posts in this thread. It is difficult even to read all posts to find a suitable/updated solution.
The majority of a question came with a code only from page 1, though for me the later the post the better the code.
Of course someone may help you here in any question. But my participation in this thread is almost on helping questions of the thread's subject. That is, on "VBA code to convert excel to pdf and email it as attachment" like what are in your points 2 and 3 but not in other points.
As to the code it is simple to attach to email any type of files like Presentation or Word document:

  .Attachments.Add PdfFile        '<-- This line present in the code for PDF
  .Attachments.Add PowerPointFile ' PowerPointFile is full path to the file of presentation
  .Attachments.Add WordFile       ' WordFile is full path to the file of Word document

But preparing contents of Presentation (or Word document) is another subject, there can be a lot of such of them, but why in this thread?
I'd help you with a pleasure in code for PowerPoint, but out of this thread – you may create new thread, post your attempts of code, describe where you have stuck in and provide me (by PM) the link to that thread for participation. And even if you have problems with coding and need in the ready to use example for the PowerPoint task (without explaining), then PM the details and I will prepare the sample for you, may be on the next week, and provide the link to download it.
Regards,

 

[mahmed1]

Thank you mate- I have created a new thread and posted some code and my attempt in there with an explanation

I am unable to PM you as its not allowing me to

This is thread I created

http://www.mrexcel.com/forum/excel-...ual-basic-applications-powerpoint-slides.html

Thank you for helping me

 

[ZVI]

Thank you mate- I have created a new thread and posted some code and my attempt in there with an explanation

I am unable to PM you as its not allowing me to

This is thread I created

http://www.mrexcel.com/forum/excel-...ual-basic-applications-powerpoint-slides.html

Thank you for helping me

Nice, thank you! Private Messaging is now switched to all members, thanks for pointing me out it was not allowed.

 

[Manish71]

Hi,

I am using an macro which prints the active sheet, by changing the cell number b5 from the range of input given at the time of entry.

The macro I am using is as under

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
Dim Startchqno As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)
Startchqno = Application.InputBox("Starting Chq No", Type:=1)

For CopieNumber = Startchqno To (CopiesCount + Startchqno)
With ActiveSheet
'number in cell B5
.Range("b5").Value = CopieNumber

'number in the footer
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut

End With
Next CopieNumber
End Sub


Now i want to have an macro, wherein instead of printing the sheet, the active sheet is converted into PDF and mailed to cell with email ID at B16 and copy to B17 if not blank. Using outlook or gmail, with the subject as per field B7

Will appreciate help

 

[Jeson Joshua]

Hello,

I am very new to this VBA thing. Could any of you please help me with the codes to have different sheets in excel printed to PDFs and PDFs getting saved with same name as the sheet name? and then have it send through outlook to the same name ( i.e tab name). It would be a great help for me. Thankyou