Is DeliveryNote.xlsx the same workbook as the activeworkbook? If so then much depends on what is in cell D4. If it contains Print_DeliveryNote_001.pdf then sName is the content of D4. If D4 contains only a number then you need the optional lines.
Code:
Const sPath As String = "D:\MDH_Data\Delivery Notes\"
Dim sName As String
With ActiveSheet
sName = .Range("D4")
If IsNumeric(sName) Then 'Optional
sName = "Print_DeliveryNote_" & Format(sName, "000") & ".pdf" 'Optional
End If 'Optional
.PageSetup.PrintArea = "$A$1:$AE$40"
PageSetup.Orientation = xlPortrait
.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=sPath & sName, _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False
End With