Macro to save File Name using Text and Cell Reference

[howard]

I have the following code below to email two sheets and to save the file containing two sheets and using a combination of text and cell reference containing the month and year


However when running the macro, I get a compile error "Subscript out of range and the code below is highlighted

  file_save_name = "Sales comms for Uploading pertaining to " & Sheets("Journals").Range("F1").Value & ".xlsx"

See my full code below

 Sub EmailSalesComms()

Dim Ztext As String
Dim Zsubject As String

Ztext = [bodytext1]                              'read in text from named cell
  Sheets(Array(""Summary Comms Payable", "Late Comms")).Copy

     With Range("A1:N150")
  .Value = .Value
End With
   
    Application.DisplayAlerts = False
   
    With ActiveWorkbook
        file_save_name = "Sales comms for Uploading pertaining to " & Sheets("Journals").Range("F1").Value & ".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("F1:F4").Value), ";")
        .Subject = "Manager Comms for Uploading"
        .Body = Ztext
       
      .Attachments.Add Environ("TMP") & "\" & file_save_name
        .Display
        '.send
    End With
           
End Sub

it would be appreciated if someone could kindly amend my the portion of code above that is causing this problem

 

[Joe4]

What exactly is in cell F1 on the Journals sheet?
You don't have an error in that cell, do you?

Can you run this line of code without an error?

Sheets("Journals").Select

 

[howard]

Hi Joe


Thanks for the reply. I can run code below

 Sheets("Journals").Select

I have a month and year in cell F1 for e.g. Mar-2022

 

[Joe4]

OK, there is nothing wrong then with the code or the cell value.

Note that you will get that error if your workbook with the "Journals" sheet is NOT the active workbook at the time when that code is run.
Do you have multiple workbooks open?

 

[howard]

Thanks for the reply and your input . I closed all other workbooks and ran the code and it works now