Page 27 of 28 FirstFirst ... 1725262728 LastLast
Results 261 to 270 of 277

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

  1. #261
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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?

  2. #262
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    Also how can we prevent excel closing after running macro?

  3. #263
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    Quote Originally Posted by turkanet View Post
    ...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:
    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 by ZVI; Dec 25th, 2018 at 10:52 PM.
    Vladimir Zakharov

  4. #264
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    Quote Originally Posted by turkanet View Post
    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.
    Vladimir Zakharov

  5. #265
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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 by turkanet; Dec 26th, 2018 at 02:11 PM.

  6. #266
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    Quote Originally Posted by turkanet View Post
    ...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.
    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.
    Vladimir Zakharov

  7. #267
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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)"
    Vladimir Zakharov

  8. #268
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    This was so helpful, thank you very much.

  9. #269
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    Quote Originally Posted by turkanet View Post
    This was so helpful, thank you very much.
    You are welcome, good luck!
    Vladimir Zakharov

  10. #270
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to convert excel to pdf and email it as attachment

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •