VBA Run time error 5

[NVRensburg]

Please can someone assist. When I run the below macro in my Excel 2016 it works perfectly. I've emailed the sheet to another colleague who runs Excel 2007, and when I try run it on her PC it gives me a runtime error 5 Invalud Proceudre Call or Argument. When I debug it highlights this line:

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

The full macro is:

Sub EMAIL_AS_PDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("$E$11")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Range("$E$11")
    .To = Range("$E$33")
    .CC = "ushers@lantic.net" ' <-- Put email of 'copy to' recipient here
    .Body = "Thank you for your recent purchase at Usher's Pool & Garden," & vbLf & vbLf _
          & "To our valued Customer " & vbLf _
          & "Please find attached a thank you letter regarding your recent purchase at Usher's Pool & Garden." & vbLf & vbLf _
          & " " & vbLf _
          & "Should you have any queries, please do not hesitate to contact us." & vbLf _
          & " " & vbLf _
          & "Kind Regards," & vbLf _
          & " " & vbLf _
          & "Usher's Pool & Garden Centre" & vbLf _
          & " " & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

Thanks for your help

 

[Joe4]

It looks like "ExportAsFixedFormat" was not introduced until Excel 2010 (see": http://www.contextures.com/excelvbapdf.html).

General rule of thumb: When creating VBA code to be used by other users, always program in the lowest (oldest) version of Excel any of your users will be using. Then, it should run on all user's versions. Otherwise, you may be using new functionality that is not available to all your users (like in this case).

 

[NVRensburg]

Thanks Joe. I've now upgraded them to Office 2010, and when running the macro I now get:

Run-time error "-2147417851 (80010105)':
Method 'Subject' of object '_MailItem' failed

What have I done wrong now?

 

[Joe4]

I have never written code to email like that, but the first thing I would check is your References (in the VB Editor, go to the Tools menu and select References).
Make sure that they have all the same References checked that you do.

 

[vmjan02]

Try this code
.ExportAsFixedFormat = xlpdf

Ins of .ExportAsFixedFormat Type= xlTypepdf