Email Sheet-Must always be on Cell A

[howard]

I have the following code below to attach a sheet from Excel in Outlook

I would like the code mended so that when the attachment is opened, it will be on Cell A1

Your assistance is most appreciated

Sub Email_BR1()

Dim File As String, strBody As String

Application.ScreenUpdating = False

Application.DisplayAlerts = False

File = Environ$("temp") & "\" & Format(Range("Q2"), "mmm-yy ") & Format(Now, "dd-mmm-yy h-mm-ss") & ".xlsx"

strBody = "Hi " & Sheets("BR1").Range("S2") & vbNewLine & vbNewLine & _

"Attached, please find current " & Sheets("BR1").Range("Q3") & vbNewLine & vbNewLine & _

"Regards" & vbNewLine & vbNewLine & _

"Howard"
ActiveWorkbook.Save

Sheets("BR1").Copy

With ActiveWorkbook

.SaveAs Filename:=File, FileFormat:=51

.Close savechanges:=False

Range("A1").Select



End With

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

.Display

.to = Join(Application.Transpose(Sheets("BR1").Range("R2:R5").Value), ";")

.Subject = Sheets("BR1").Range("Q3")

.body = strBody

.Attachments.Add File

End With

Kill File

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

 

[MrTomasz]

change order and add dot before range:

With ActiveWorkbook
    .Range("A1").Select
    .SaveAs Filename:=File, FileFormat:=51
    .Close savechanges:=False
End With

 

[howard]

Thanks for the help

I now get a run time error

"Object doesn't support this property or method"

 With ActiveWorkbook
  .Range("A1").Select

    
    .SaveAs Filename:=File, FileFormat:=51
    .Close savechanges:=False
End With

The part below is in yellow when debugging it

   .Range("A1").Select

Kindly test & advise on how to resolve this

 

[MrTomasz]

change:
.Range("A1").Select
to
ActiveSheet.Range("A1").Select
or try without dot, but with new order.

 

[howard]

Thanks for the help

It still did not select A1 in the activesheet

I added these two ines of code beneath it and it now works

 ActiveWindow.ScrollRow = 1
   ActiveWindow.ScrollColumn = 1