What happens is I get debug error and shows the line where the error is, but if I'm using below syntax, it works
Sub Summary_Report()
'
' Test2 Macro
'
'
Sheets("Report").Select
Sheets("Summary").Visible = True
Sheets("Report").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub Summary_Return()
'
' Test2 Macro
'
'
Sheets("Summary").Select
Sheets("Report").Visible = True
Sheets("Summary").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub Send_Report()
Dim r As Range
Set r = Range("b2:t27")
r.Copy
Dim outlookApp As Object
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Object
Set outMail = outlookApp.CreateItem(0)
Dim strBody As String
strBody = "Hi Team," & vbNewLine & vbNewLine & _
"Kindly see attached and below report:"
With outMail
.display
.To = Range("b29").Value
.cc = Range("b30").Value
.Subject = Range("b31").Value
.body = strBody
Dim wordDoc As Object
Set wordDoc = .GetInspector.WordEditor
End With
With wordDoc
.Application.Selection.EndKey Unit:=6 'wdStory
.Application.Selection.TypeParagraph
.Application.Selection.PasteAndFormat 13 'wdChartPicture
End With
With Attached_File
Attached_File = "sample.xlsm"
outMail.Attachments.Add Attached_File
End With
'To paste as a table
'wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
End Sub
What I need to do is unhide the veryhidden sheet and hide the visible sheet using veryhidden attribute then send the file itself as an attachment.