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

JohnnyRey

New Member
Joined
May 3, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
    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:

VBA Code:
    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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
Solution
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):

VBA Code:
    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! :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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