Excel VBA attaching print area as PDF

  • I'm trying to create a macro whereby the print area of a sheet, "Hotel Booking" is attached as a PDF file to an email. The email will be created using CDO and not Outlook Application. Everything else in my code works except for the attachment. It will say file not found and will not attach anything to the email.


    Here's my code that only focuses on the email attachment:


    Here's the whole code:


    Also asked this question here:
    http://www.vbaexpress.com/foru…s-PDF&p=383039#post383039
    https://stackoverflow.com/ques…taching-print-area-as-pdf

  • Test that you built the filename as you expect. Use Debug.Print. After the run, check VBE's Immediate Window.


    I would change:

    Code
    'PdfFile = destFolder & Sheets("Hotel Booking").Range("C4") & Sheets("Hotel Booking").Range("C11") & Sheets("Hotel Booking").Name & ".pdf"
    'to
     PdfFile = destFolder & Sheets("Hotel Booking").Range("C4") & Sheets("Hotel Booking").Range("C11") & "Hotel Booking.pdf"
    Debug.Print pdfFile


    I guess the other CDO parts work without an attachment? This is what I use for Gmail with CDO:

  • Hey, the CDO part of my code works fine. It could send emails without attachments. I added the

    Code
    Debug.Print pdfFile

    line and when I checked the immediate window, it shows the correct file name. However, now I'm getting an error on this line

    Code
    .AddAttachment PdfFile

    . The error says "The specified protocol is unknown."


    Here are the details for my SMTP part:

    Code
    With Flds
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp-mail.outlook.com"
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
                .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxx"
                .Update
            End With
  • I guess you obfuscated your smpt server name and email address. The server governs what ports it will accept.


    As for the attachment deal, you can use Debug.Print Dir(pdffile) or in one line of code:

    Code
    If Dir(PdfFile) <> "" Then .AddAttachment (PdfFile)
  • I guess you obfuscated your smpt server name and email address. The server governs what ports it will accept.


    As for the attachment deal, you can use Debug.Print Dir(pdffile) or in one line of code:

    Code
    If Dir(PdfFile) <> "" Then .AddAttachment (PdfFile)


    I don't seem to understand what you mean here. The error I'm getting right now says ""The specified protocol is unknown." on

    Code
    .AddAttachment PdfFile


    The correct file name is shown in the immediate window.

  • I also forgot to mention that the attachment portion of my code has changed. Here is the revised one after I add the "debug.print" line


  • I don't see a need for the With. Just use ActiveSheet directly. Using it for the CDO object is fine.


    My guess as surmised earlier is that the value of pdffile is not a valid filename or full path does not exist or both. E.g. one of the cells values is a date string like 8/17/18. That would be a partial subfolders path.


    Do 2 runs with activeworkbook having fullpath for one and a new unsaved workbook in another and post the immediate window results.

  • For the sake of the file name to be constant, both cells "C4" and "C11" will have the same values in both workbooks. "C4" will have "EVA Air", and "C11" will have "BR225".


    First run on the activeworkbook: Immediate window displays "EVA Air BR225 Hotel Booking.pdf". This is the intended file name.
    Second run on an unsaved workbook: Immediate window displays "EVA Air BR225 Hotel Booking.pdf".


    The same error appears on both runs. "The specified protocol is unknown."

  • Where is your drive:\path\ part in those values?


    You need to make the CDO part run right first less the AddAttachment line and then the making of the pdffile and then put the two together. Use F8 to run each line of code to see which line is a problem if you don't know.

  • I seem to have forgotten to add the file path it seems. I changed it now.

    Code
    'PdfFile = destFolder & Sheets("Hotel Booking").Range("C4") & " " & Sheets("Hotel Booking").Range("C11") & " " & "Hotel Booking.pdf"
    'to
     PdfFile = PdfFile & destFolder & Sheets("Hotel Booking").Range("C4") & " " & Sheets("Hotel Booking").Range("C11") & " " & "Hotel Booking.pdf"


    So the complete code for the attachment is


    If I run the macro now, it will send the email without any attachments and will give me an error on the line "Kill PdfFile" saying "File not found". The result on the immediate window is "https://d.docs.live.net/5b4e348d0c77a54e/Desktop/School Work/Crew Scheduling with CDO EVA Air BR225 Hotel Booking.pdf".



    When I try changing the line

    Code
    'PdfFile = ActiveWorkbook.FullName
    'to
    PdfFile = ActiveWorkbook.Path


    and keep the rest of the code the same, it will give me an error on this line

    Code
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


    saying "Invalid procedure call or argument". The result on the immediate window is "https://d.docs.live EVA Air BR225 Hotel Booking.pdf". I'm not sure why it doesn't display the full path name. When I created another macro only with the line "Msgbox Activeworkbook.Path", it will display "https://d.docs.live.net/5b4e348d0c77a54e/Desktop/School Work".



    When I try running the macro on an unsaved workook, I will get an error on the line

    Code
    PdfFile = PdfFile & " " & destFolder & Sheets("Hotel Booking").Range("C4") & " " & Sheets("Hotel Booking").Range("C11") & " " & "Hotel Booking.pdf"


    saying "Subscript out of range".

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!