none
Run time error 1004 RRS feed

  • Question

  • Hi

    I am in a Mac Environment and have written a VBA program to convert a excel worksheet to a pdf and then send to email but I keep getting 2 errors.  One being a print error and the other a run time error 1004.  I don't understand where I have gone wrong in the code (see below).  Would be greatly appreciative if someone could please help.

    Sub AttachActiveSheetPDF()

      Dim IsCreated As Boolean

      Dim i As Long

      Dim PdfFile As String, Title As String

      Dim MailApp As Object

      Dim SendTo As String

     

      ' Not sure for what the Title is

      Title = "PORegister"

     

      ' Define PDF filename

      PdfFile = ActiveWorkbook.FullName

      i = InStrRev(PdfFile, ".")

      If i > 1 Then PdfFile = Left(PdfFile, i - 1)

      PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

     

     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="ICS Australia \ pdfFile", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

     

     

      ' Use already open Mail if possible

      On Error Resume Next

      'Set MailApp = GetObject(, "Mail.Application")

      'If Err Then

        Set MailApp = CreateObject("Mail.Application")

        IsCreated = True

      'End If

      'MailApp.Visible = True

      On Error GoTo 0

     

     SendTo = ActiveSheet.Range("D9").Value

     

      ' Prepare e-mail with PDF attachment

      With MailApp.CreateItem(0)

       

        ' Prepare e-mail

        .Subject = "Purchase Order"

        .To = SendTo

        .CC = "accounts@icsaust.com.au"

        .Body = "Hi," & vbLf & vbLf _

              & "The report is attached in PDF format." & vbLf & vbLf _

              & "Regards," & vbLf _

              & Application.UserName & vbLf & vbLf

        .Attachments.Add PdfFile

       

        ' Try to send

        On Error Resume Next

        .Display

        Application.Visible = True

        If Err Then

          MsgBox "E-mail was not sent", vbExclamation

        End If

        On Error GoTo 0

       

      End With

      

      ' Quit Mail if it was created by this code

      'If IsCreated Then MailApp.Quit

     

      ' Release the memory of object variable

      Set MailApp = Nothing

     

    End Sub

    Tuesday, March 14, 2017 3:27 AM

All replies

  • Hi Kirsty67,

    Filename:="ICS Australia \ pdfFile", 
    I suppose "ICS Australia" is a folder name.
    If so, please modify an operator "Filename:=", like this. 
    Filename:="X:\ICS Australia\" & pdfFile, 

    Regards,
    Ashidacchi
    Tuesday, March 14, 2017 5:26 AM