Email to named attached File as "Financial Data"

[howard]

i have he following macro to create email in Outlook and attach several sheets

I get subscript out of range and the code below is highlighted

 .Attachments.Add Environ("TMP") & "\" & ThisWorkbook.Sheets("Financial Data for BR1").Name & ".xlsx"

See full code below

 Sub EmailSheets()

Dim Ztext As String

Dim Zsubject As String


'ThisWorkbook.Activate 'start in THIS workbook

Ztext = [bodytext] 'read in text from named cell

Sheets(Array("Statement of accounts", "Statistical Information")).Copy

With Range("A1:O150")

.Value = .Value

End With



With ActiveWorkbook

Application.DisplayAlerts = False

.SaveAs Environ("TMP") & "\" & ThisWorkbook.Sheets("Statement of Financial Position").Name & ".xlsx"



Application.DisplayAlerts = True

.Close (True)

End With



With CreateObject("Outlook.Application").CreateItem(0)

.To = Join(Application.Transpose(Sheets("index").Range("S1:S3").Value), ";")

.Subject = "Financial Info"

.Body = Ztext



.Attachments.Add Environ("TMP") & "\" & ThisWorkbook.Sheets("Financial Data for BR1").Name & ".xlsx"

.Display

End With 
end Sub

It would be appreciated if someone could kindly amend my code

 

[JackDanIce]

ThisWorkbook.Sheets("Financial Data for BR1").Name & ".xlsx"

This creates the string "Financial Data for BR1.xlsx"

But your code is saving the file as "Statement of Financial Position.xlsx" so it can't find the file "Financial Data for BR1.xlsx" and generates the error

Untested, however, try:

Sub EmailSheets()

    Dim Ztext As String
    Dim Zsubject As String

    Dim file_save_name As String

    'ThisWorkbook.Activate 'start in THIS workbook
    Ztext = [bodytext].Value 'read in text from named cell
   
    Sheets(Array("Statement of accounts", "Statistical Information")).Copy
    With Range("A1:O150")
        .Value = .Value
    End With

    Application.DisplayAlerts = False
   
    With ActiveWorkbook
        file_save_name = "Statement of Financial Position.xlsx"
        .SaveAs Environ("TMP") & "\" & file_save_name
        .Close True
    End With
   
    Application.DisplayAlerts = True

    With CreateObject("Outlook.Application").CreateItem(0)

        .To = Join(Application.Transpose(Sheets("index").Range("S1:S3").Value), ";")
        .Subject = "Financial Info"
        .Body = Ztext
           
        .Attachments.Add Environ("TMP") & "\" & file_save_name
        .Display
    End With
End Sub

 

[howard]

Many thanks for the help, code works perfectly

 

[JackDanIce]

Glad it's resolved, thanks for the feedback