Attachment generated from newly saved file is corrupt

Question

The code snippet below is part of an Excel VBA Version 2507 (Build 19029.20208) procedure to create a PDF file from a worksheet range and create an email with the PDF as an attachment.

The procedure works under Windows 10 (old laptop).

The attachment is corrupt under Windows 11 2025-08 Cumulative Update for Windows 11 Version 24H2 for x64-based Systems (KB5063878) (26100.4946) (new laptop).
I found by accident a pause after creation of the pdf and before creation of the email allowed the procedure to complete correctly.
Hence the For_Next loop.

It would appear the background routine converting the pdf to an email attachment is the issue either because of the change of laptop or OS.
The new is faster than the old.

NOTE: The saved pdf is not corrupt only the attachment.

Sub part
    'Create and save PDF
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, \_
    printtofile:=True, prtofilename:=lcPDFname, IgnorePrintAreas:=False
    'Generate strings to be pasted into the email
    lcFundMsg = WorksheetFunction.Round((Range("Fund").Value), 2)
    lcUnitCash = WorksheetFunction.Round(Range("UnitValue").Value, 2)
    'and tidy both to £0.00 format
    Call CashFix(lcFundMsg)
    Call CashFix(lcUnitCash)
    lcFundMsg = "No Club handicap reductions." & vbNewLine & "Paid to the fund this
    session £" & lcFundMsg & " @ " & \_
    lcUnitCash & "p/game." '& vbNewLine & "Michael."
    'This pause allows code to work correctly
    For lnI = 1 To 100000000# '00000000# '1E8 seems to be minimum delay
    Next lnI
    'Generate email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    lcSignBy = "Michael"
    With OutMail
        .To = "Starters"
        .CC = ""
        .BCC = ""
        .Subject = "Diddly Results Sheet for " & lvDate 'RsltHdr.Value
        .Body = lcFundMsg & vbNewLine & lcSignBy
        .Attachments.Add lcPDFname
        .display
    End With 'OutMail
End Sub

Unless my pause workaround is a digression and the fault lies in my coding I have to assume a problem with the conversion process of the attachment.

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Aug 25 at 15:26
If you use an existing "known good" PDF as the attachment does it work as expected? Do other file types also have problems when sent as attachments? — Tim Williams
– Tim Williams, Commented Aug 25 at 16:09
It might be more reliable to use ExportAsFixedFormat instead of SelectedSheets.PrintOut, so you're not involving the print spooler. learn.microsoft.com/en-us/office/vba/api/… — Tim Williams
– Tim Williams, Commented Aug 25 at 19:09
ExportAsFixedFormat works without any pause so my immediate problem is solved. — jmcsa3
– jmcsa3, Commented Aug 28 at 7:58
ExportAsFixedFormat works without any pause so my immediate problem is solved. However it would be good to understand why the PrintOut method fails, I have noted that the PDF generated by the Export method is about 220Kb while that by PrintOut is around 550Kb. The latter is a "good" file inasmuch it can be opened by Adobe. However without the "pause" it results in a corrupt email attachment using OutMail. It would be helpful to understand how to choose between ExportAs... and PrintOut — jmcsa3
– jmcsa3, Commented Aug 28 at 8:22

Ryan H · Accepted Answer · 2025-08-25 16:53:02Z

Instead of using a For...Next loop, you can use the FileSystemObject to check if the PDF file exists and is no longer locked (fully written) before attaching it to the email. This ensures the file is ready without relying on a fixed delay, which may not scale across different systems or file sizes.

Try adding this in place of 'This pause allows code to work correctly

 ' Check if PDF file is ready using FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FileReady = False
    WaitStart = Timer
    Do While Not FileReady And (Timer - WaitStart) < MaxWaitSeconds
        If FSO.FileExists(lcPDFname) Then
            On Error Resume Next
            ' Attempt to open the file to check if it's not locked
            Open lcPDFname For Input As #1
            If Err.Number = 0 Then
                FileReady = True
                Close #1
            Else
                Err.Clear
            End If
            On Error GoTo 0
        End If
        If Not FileReady Then
            Application.Wait Now + TimeValue("00:00:01") ' Wait 1 second before retrying
        End If
    Loop

    ' Check if file was not ready after max wait time
    If Not FileReady Then
        MsgBox "Error: PDF file '" & lcPDFname & "' is not accessible after " & MaxWaitSeconds & " seconds.", vbCritical
        Exit Sub
    End If

Collectives™ on Stack Overflow

Attachment generated from newly saved file is corrupt

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related