VBA Edit Links

[SteveABC]

Capture 3.jpgI didn't attach any code because I thought you wanted to test your own code.

Here's the whole code.

There's no code in the file you attached. You didn't quote the whole code in your first post here (at least it looks that way without Sub… End Sub). Also, I don't know in what kind of module the code you quoted is in.
Could you re-attach?

Sub SendReportv2()


Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
Dim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
With Sheets("Control")
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
  End With
Set w = Sheets("Mainstay Master")
Set X = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")




b.Names.Add Name:="Sitenames", RefersTo:=Rng


With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
X.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"


X.Cells("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
          
newLink = mypath & "\Mainstay Master.xlsx"
linkSources = b.linkSources(1)
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master Template.xlsm") Then _
          b.ChangeLink link, newLink, 1
     Next
     End If
     myfile = mypath & " Mainstay Master.xlsx"
Application.DisplayAlerts = False
For sh = b.Sheets.Count To 3 Step -1
         b.Sheets(sh).Delete
Next
Application.DisplayAlerts = True
b.SaveAs myfile
Set ol = CreateObject("outlook.application")
Set msg = ol.Createitem(0)
With msg




    .To = sto
    .cc = scc
    .Subject = "Mainstay Report & Master file"
    .Body = "Good Morning"
    .attachments.Add myfile
    .display
End With


End Sub