Curious problem, excel creates a PDF (with data), then when sending as attachment, PDF is BLANK

[JohnnyRey]

I have tried to search and can't find anyone reporting a similar question/problem.

I have a macro that filters some data, and then creates a PDF file from the filtered data, so far no problem. Then I have a macro that then sends the PDF file as an attachment in an email, but the problem is that when I do this, the PDF file that it sends is blank, there is no data in the PDF file that is sent. I know that the PDF that is initially created is fine, as I can go to the PDF file to look at it, and the data is there. But then when I send it as part of a macro, it seems the data is somehow stripped out? the PDF that gets sent is as many pages as the original PDF file was, so if the PDF was 4 pages, then it sends 4 pages, it is just that they are empty pages! In my most recent example of trying this, the PDF created is 267kb, but what gets attached and sent then becomes 140kb.

Here is a part of the macro I use to create the initial PDF:

    PDFFilename = "Current Residents " & Format(Now(), "YYYYMMDD") & ".pdf"
    Application.CutCopyMode = False
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .LeftMargin = Application.InchesToPoints(0.31496062992126)
        .RightMargin = Application.InchesToPoints(0.31496062992126)
        .TopMargin = Application.InchesToPoints(0.354330708661417)
        .BottomMargin = Application.InchesToPoints(0.354330708661417)
        .Orientation = xlPortrait
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True

    Application.DisplayAlerts = False

    ActiveWorkbook.ActiveSheet.UsedRange.ExportAsFixedFormat Type:=xlTypePDF, _
                                              Filename:=FilePath & PDFFilename, _
                                              Quality:=xlQualityStandard, _
                                              IncludeDocProperties:=True, _
                                              IgnorePrintAreas:=False, _
                                              OpenAfterPublish:=False
    Application.DisplayAlerts = True

Here is the code in the macro to send the file:

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    Set SourceWb = ThisWorkbook
    FilePath = SourceWb.Path & "\"
    Columns("M:M").Select
    Selection.EntireColumn.Hidden = True

    PDFFilename = "Current Residents " & Format(Now(), "YYYYMMDD") & ".pdf"

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "source email here"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "email password here"
        .Update
    End With

    With iMsg
        Set .Configuration = iConf
        .To = "target email here"
        .CC = ""
        .BCC = ""
        .From = "source email here"
        .Subject = "Current Residents " & Format(Now(), "dd/mm/yyyy")
        .addAttachment FilePath & PDFFilename
        .Send
    End With
    
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing

I just do not understand what is going on, maybe I am missing something basic?

 

[Micron]

Is PDFFilename a module level variable? Can't see your declarations. If it's not, then FilePath & PDFFilename might turn out to be an edited and unsaved workbook or something. I've read that this has been the cause of sending blank wb attachments.

EDIT - Just noticed there's no message body. Would think that's necessary.

 

[JohnnyRey]

Is PDFFilename a module level variable? Can't see your declarations. If it's not, then FilePath & PDFFilename might turn out to be an edited and unsaved workbook or something. I've read that this has been the cause of sending blank wb attachments.

EDIT - Just noticed there's no message body. Would think that's necessary.

Thanks for your help Micron!

Yes, PDFFilename and FilePath are declared as Strings at the top of the Macro, and have confirmed that the name and path are valid for the PDF being created in another macro.

Actually, your edited comment has resolved the problem!! It is a very strange consequence of not having any message body, but by adding in some kind of message body, the problem is fixed, the PDF attachment is as it should be!

I changed that section of code as follows (with "Greeting" and "FirstLine" defined earlier in the macro):

    With iMsg
        Set .Configuration = iConf
        .To = "target email here"
        .CC = ""
        .BCC = ""
        .From = "source email here"
        .Subject = "Current Residents " & Format(Now(), "dd/mm/yyyy")
        .addAttachment FilePath & PDFFilename
        .htmlBody = "<html>" & "<body>" & Greeting & "<br> <br>" & FirstLine & "<br>" & _
        "<br> Cheers, <br> John" & " </body> " & " </html> "
        .Send
    End With

Thanks again, problem solved!

 

[Micron]

You're welcome. I had read that lack of a body element would cause this but wasn't sure as it seems odd that it would affect the attachment.