Add extra worksheet

[SteveABC]

Hi,

How do I add an extra worksheet named "Debtors" in my attachment? Already have one sheet called E&J master being added to my attachment and not sure on where to add this code either.


Many Thanks



Sub Pmtemplate()




Dim w As Worksheet, b As Workbook, 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\E&J Estates\Reporting\Reports\"
With Sheets("Control")
sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
End With
Set w = Sheets("E&J Master")
Set b = Workbooks.Add
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")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2
b.Sheets(1).Name = "E&J PM Master"
ActiveWindow.Zoom = 75


myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
'Application.DisplayAlerts = False
'For sh = b.Sheets.Count To 2 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 = "E&J PM's Template"
.Body = "Good Morning"
.attachments.Add myfile
.display
End With

[snb]

Avoid redundant variables.
It's better to send an email than to show it:


With CreateObject("outlook.application").Createitem(0)
.To = sto
.cc = scc
.Subject = "E&J PM's Template"
.Body = "Good Morning"
.attachments.Add myfile
.send
End With

[SteveABC]

Thanks for that, but I'd rather view the email prior to sending.




Avoid redundant variables.
It's better to send an email than to show it:


With CreateObject("outlook.application").Createitem(0)
.To = sto
.cc = scc
.Subject = "E&J PM's Template"
.Body = "Good Morning"
.attachments.Add myfile
.send
End With

[snb]

Don't you trust your own code ?

[SteveABC]

Not a question of trust. Just a question of process. I like to double check. Just a preference of mine when sending to clients.



Don't you trust your own code ?

[mancubus]

actually you don't need but if you want to "display" an email rather than "send" it, replace .Send with .Display.