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
 
Dear all,
first of all really thank you very much for this helpful thread.

Dear ZVI,
Everything is fine but even my default font for messages is Candara 11, mail comes with Calibri 10. how to change this?
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
...Everything is fine but even my default font for messages is Candara 11, mail comes with Calibri 10. how to change this?
Hi,
Define font name and size of that font in the user settings section of the below code:
Rich (BB code):
Sub Attach_Sheets_As_Pdf_With_Signature1()
' ZVI:2018-12-26 https://www.mrexcel.com/forum/excel-questions/710212-vba-code-convert-excel-pdf-email-attachment-post5197824.html#post5197824
 
  ' --> User settings, change to suit
  Const MySheets As Variant = "Sheet1;Sheet3" ' Use MySheets = "" for all sheets. Semicolon is used as list separator!
  Const IsDisplay As Boolean = True           ' Change to False for .Send instead of .Display
  Const IsSilent As Boolean = False           ' Change to True for Send without the confirmation MsgBox
  Const FontName = "Candara"                  ' Font name of the email body
  Const FontSize = 11                         ' Font size of the email body
  ' <-- End of settings
 
  Dim IsCreated As Boolean
  Dim OutlApp As Object
  Dim i As Long
  Dim char As Variant
  Dim PdfFile As String, HtmlFont As String, HtmlBody As String, HtmlSignature As String
 
  ' Set the font for the html-body (parentheses are just because of MrExcel posting limitation)
  HtmlFont = "(body font: " & FontSize & "pt " & FontName & ";color:black"")(/p)"
  HtmlFont = Replace(HtmlFont, "(", Chr(60))
  HtmlFont = Replace(HtmlFont, ")", Chr(62))
 
  ' Build HtmlBody for the email (Change to suit)
  HtmlBody = "Hi," & vbLf & vbLf _
           & "Please find the latest payroll report attached"
 
  ' Replace vbLf by its html tag
  HtmlBody = Replace(HtmlBody, vbLf, Chr(60) & "br" & Chr(62))
   
  ' 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
 
  ' Replace illegal symbols in PdfFile by underscore
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
 
  ' Apply %TEMP% path to the file name and limit lenght of the pathname
  PdfFile = Left(Environ("TEMP") & "\" & PdfFile, 251) & ".pdf"
  
  ' Try to delete PDF file if present
  If Len(Dir(PdfFile)) Then Kill PdfFile
 
  ' Select sheets to be exported into the PDF (single) file
  If MySheets = "" Then
    ' All sheets to PDF
    Sheets.Select
  Else
    ' Only 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 the default signature
  With OutlApp.CreateItem(0)
   
    ' Set HTML format
    .BodyFormat = 2
      
    ' 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
    HtmlSignature = .HtmlBody
   
    ' Insert sText into HtmlBody
    .Subject = "Payroll Report"
    .To = "someone@domain.com" ' Put 'To' email(s) here
    .CC = ""                   ' Carbon copy email(s)
    .HtmlBody = HtmlFont & HtmlBody & HtmlSignature
      
    ' Try to send or just display the e-mail
    On Error Resume Next
    If IsDisplay Then .Display Else .Send
  
    ' Show error of the .Send method
    If Not IsDisplay Then
      ' Return focus to Excel's window
      Application.Visible = True
      ' Show error/success message
      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
 
Last edited:
Upvote 0
Also how can we prevent excel closing after running macro?
The provided code does not quit Excel application, post your code to find the reason of the issue.
 
Upvote 0
Dear ZVI,
i apply below code. but i noticed that same file closes excel at my work , does not at my home. same operating systm + same office version.
Second, i could not succeed to apply Candara even i tried to add some lines from your last advice. Need your kind comments. Thank you.

Code:
Sub Send_PDF_customer()
' --> User settings, change to suit
Const IsHtml As Boolean = True     ' Change to True for HTML body of email
Const IsDisplay As Boolean = True  ' Change to True to .Display instead of .Send
Const IsSilent As Boolean = False  ' Change to True to Send without the confirmation MsgBox
Const FontName = "Candara"         ' Font name of the email body
Const FontSize = 11                ' Font size of the email body
' <-- End of settings
Dim IsCreated As Boolean
Dim MailSubject As String, PdfFile As String, s As String
Dim HtmlSignature As String, Signature As String
Dim OutlApp As Object
Dim i As Long
Dim char As Variant
' Set the font for the html-body (parentheses are just because of MrExcel posting limitation)
HtmlFont = "(body font: " & FontSize & "pt " & FontName & ";color:black"")(/p)"
HtmlFont = Replace(HtmlFont, "(", Chr(60))
HtmlFont = Replace(HtmlFont, ")", Chr(62))
' Not sure for what the Title is
Title = Range("H2")
' Define PDF filename
PdfFile = ActiveSheet.Name
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = Range("H2") & "_" & PdfFile & ".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 email with PDF attachment and default signature
With OutlApp.CreateItem(0)
' Get default email signature without blinking (instead of .Display method)
With .GetInspector: End With
If IsHtml Then HtmlSignature = .HtmlBody Else Signature = .Body
' Prepare e-mail
.Display     '<-- This needs to be first for the signature to be added
.Subject = Range("H2") & " / " & Range("K1")
.To = Range("L1") ' <-- Put email of the recipient here
' Edit the body's text or html text as required
If IsHtml Then
' The tags are: h3 is for Header#3; b is for Bold; br is for line break
' HTML tag's brakets are not displayed properly in the forum post, thus replacing in s is used to fix this problem
s = "AAAAA, (br)" _
& "AAAAAA.(br)" _
& "AAAAA."
s = Replace(s, "(", "<")
s = Replace(s, ")", ">")
.HtmlBody = s & HtmlSignature
.Attachments.Add PdfFile
' Try to send
On Error Resume Next
.Display 'to send without displaying, change with .Send
Application.Visible = True
' 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 If
End With
End Sub
 
Last edited:
Upvote 0
...I noticed that same file closes excel at my work , does not at my home. same operating system + same office version.
Second, i could not succeed to apply Candara even i tried to add some lines from your last advice. Need your kind comments.
Below is the updating of your code.
Rich (BB code):
Sub Send_PDF_customer1()
 
  ' --> User settings, change to suit
  Const IsDisplay As Boolean = True  ' Change to False for .Send instead of .Display
  Const IsSilent As Boolean = False  ' Change to True to show Send status
  Const FontName = "Candara"         ' Font name of the email body
  Const FontSize = 11                ' Font size of the email body
  ' <-- End of the settings
 
  Dim IsCreated As Boolean
  Dim OutlApp As Object
  Dim char As Variant
  Dim PdfFile As String, HtmlFont As String, HtmlBody As String, HtmlSignature As String
 
  ' Edit the body's html text as required
  ' The tags are: h3 is for Header#3; b is for Bold; br is for line break
  ' HTML tag's are not displayed properly in the post of MrExcel forum, thus replacing is used to fix this problem
  HtmlBody = "First line, (br)" _
           & "Second line.(br)" _
           & "Third line."
  HtmlBody = Replace(HtmlBody, "(", "<")
  HtmlBody = Replace(HtmlBody, ")", ">")
 
  ' Set the font for the html-body (parentheses are just because of MrExcel posting limitation)
  HtmlFont = "(body font: " & FontSize & "pt " & FontName & ";color:black"")(/p)"
  HtmlFont = Replace(HtmlFont, "(", "<")
  HtmlFont = Replace(HtmlFont, ")", ">")
  
  ' Define PDF filename
  PdfFile = Range("H2") & "_" & ActiveSheet.Name
 
  ' Replace illegal symbols in PdfFile by underscore
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
 
  ' Apply %TEMP% path to the file name and limit lenght of the pathname
  PdfFile = Left(Environ("TEMP") & IIf(Right(Environ("TEMP"), 1) <> "\", "\", "") & PdfFile, 251) & ".pdf"
 
  ' Try to delete PDF file if present
  If Len(Dir(PdfFile)) Then Kill PdfFile
 
  ' Export the activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  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
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare email with PDF attachment and the default signature
  With OutlApp.CreateItem(0)
  
    ' Set HTML format
    .BodyFormat = 2
     
    ' 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
    HtmlSignature = .HtmlBody
   
    ' Prepare e-mail
    .Subject = Range("H2") & " / " & Range("K1")
    .To = Range("L1")   ' <-- Put email of the recipient here
    .HtmlBody = HtmlFont & HtmlBody & HtmlSignature
           
    ' Try to send or just display the e-mail
    On Error Resume Next
    If IsDisplay Then .Display Else .Send
 
    ' Show error of the .Send method
    If Not IsDisplay Then
      ' Return focus to Excel's window
      Application.Visible = True
      ' Show error/success message
      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
Font of email sets correctly now.
On your work PC try to repair an Office application.
To find code line after which Excel quits put cursor into the code and hit F8 for step-by-step debugging till Excel quits.
 
Upvote 0
My bad, the typos in HtmlFont have been found.
Please replace in the code of the post 263 and 266
this line: HtmlFont = "(body font: " & FontSize & "pt " & FontName & ";color:black"")(/p)"
by the: HtmlFont = "(body style=""font: " & FontSize & "pt " & FontName & ";color:black"")(/p)"
 
Upvote 0
Dear ZVI,
how to change the code to send mail from specific mail account (not default one) and with its signature on outlook?
thank you
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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