Wow. Amazing piece of code that. Should have included the test book in my first post. haha. Think of the time we would have saved. haha.

How do I get the new workbook to save down as myfile? We haven't defined anything as a new workbook?

Quote Originally Posted by p45cal View Post
see msg#25

I've included some additional code to your one liner as I need to save it down as myfile in the mypath directory. Its not correct as the attachment doesn't pick up the new workbook that's been created. Mainly due to me not saving the file correctly. 


Sub blah()


Dim ol As Object, msg As Object
Dim mypath As String, myfile As String, scc As String, sto As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
Workbooks("Mainstay Master Template").Sheets("Control").Activate
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")


Sheets(Array("Mainstay Master", "Mainstay Report")).Copy
    
myfile = mypath & " Mainstay Master.xlsx"


Application.DisplayAlerts = True
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