Capture 3.jpgI didn't attach any code because I thought you wanted to test your own code.
Here's the whole code.
Originally Posted by
p45cal
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