1

I am trying to save two worksheets in a workbook as separate files to company network locations and then attach those files to an email.

Sub Test_Module_Peter()
'
Dim OutApp As Object
Dim OutMail As Object
Dim SPpath As String
Dim SCpath As String
Dim SPfilename As String
Dim SCfilename As String
Dim SPFullFilePath As String
Dim SCFullFilePath As String
Dim wb As Workbook
Dim Cell As Range

Application.ScreenUpdating = False

' export a copy of PER SP Form
    Sheets("PER SP").Select
    Sheets("PER SP").Copy

' Remove formulas from SP sheet
    With ActiveSheet.UsedRange
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False

' Save a copy of the SP PER Form
    SPpath = "\\UKRLTD008\Company\...\...\...\2019\"
    SPfilename = "TEST - PER SP ABL90_2019 " & Range("M1")
    SPFullFilePath = SPpath & SPfilename
    ActiveWorkbook.SaveAs filename:=SPpath & SPfilename, FileFormat:=52
    ActiveWorkbook.Close SaveChanges = True

' select ABL90 Credit Claim Master Spreadsheet
    For Each wb In Application.Workbooks
        If wb.Name Like "ABL90 Credit Claim Master*" Then
            wb.Activate
        End If
    Next

' export a copy of PER SC Form
    Sheets("PER SC").Select
    Sheets("PER SC").Copy

' Remove formulas from SC sheet
    With ActiveSheet.UsedRange
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False

' Save a copy of the SC PER Form
    SCpath = "\\UKRLTD008\Company\...\...\...\2019\"
    SCfilename = "TEST - PER SC ABL90_2019 " & Range("M1")
    SCFullFilePath = SCpath & SCfilename
       ActiveWorkbook.SaveAs filename:=SCpath & SCfilename, FileFormat:=52
       ActiveWorkbook.Close SaveChanges = True

' Send the SP PER Form to RMED
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .SentOnBehalfOfName = "sales@radiometer.co.uk"
        .To = "laura.valenti@radiometer.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "RLTD PER Forms " & Range("M1")
        .Body = "Hi " & vbNewLine & vbNewLine & "Please find attached ABL90 PER's" & vbNewLine & vbNewLine & "Thank you"
        .Attachments.Add SPFullFilePath
        .Attachments.Add SCFullFilePath
        .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

It saves the files, but when I try to add them as attachments to the email, the following error occurs:

Run-time error '-2147024894(80070002)': Cannot find this file. Verify the path and file name are correct.

I tried to save the path and filename together as FullFilePath for each file but it doesn't seem to work, can anyone tell me why?

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
Laura Valenti
  • 11
  • 1
  • What is in `Range("M1")`? I'd start by fully qualifying that range, like `ThisWorkbook.Worksheets("Sheet1").Range("M1")`, then try again. Also, you should try to avoid using `Select`, `Copy`, `Paste`, etc. – dwirony Aug 19 '19 at 16:22
  • 1
    Obligatory link on [How to avoid using Select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Nacorid Aug 19 '19 at 16:31
  • I'm able to recreate that error if the file type is left out of the path so you may just be missing the appropriate .xls* – TheJeebo Aug 19 '19 at 17:13
  • Hi everyone, thank you for your comments! I tried declaring the workbook and worksheet but I was still getting the error so I tried adding the file extension and it now works! Thank you so much for your help, this had been driving me crazy :) – Laura Valenti Aug 22 '19 at 11:33

0 Answers0