I need help to modify this macro to send all sheets from the same workbook separately in one email.
Sub SendemailAll()
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim tempFile As String
Dim strbodymsg As String
Dim wb As Workbook
Dim strbody As String
Dim tempWB As Workbook
Dim DisplayEmail As String, Signature As String
Dim a, b, c, d As String
Application.DisplayAlerts = False
ThisWorkbook.Sheets.Copy
Set tempWB = ActiveWorkbook
tempWB.SaveAs Filename:="All sheets"
'problem how to separate save all sheets
'variable from userform or string outputs into default documents folder as xls
'Create Outlook email=============
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
End With
Signature = xEmailObj.Body
On Error Resume Next
With xEmailObj
a = ThisWorkbook.Sheets("Sheet1").Range("R1").Value
b = ThisWorkbook.Sheets("Sheet1").Range("R2").Value
c = ThisWorkbook.Sheets("Sheet1").Range("R3").Value
d = ThisWorkbook.Sheets("Sheet1").Range("R4").Value
.Display
.To = a
.CC = b
.Subject = c
.Attachments.Add tempWB.FullName
'previously saved workbook with single sheet
.Body = d & Signature
If DisplayEmail = False Then
'.Display
'.Send
End If
End With
tempWB.ChangeFileAccess Mode:=xlReadOnly
Kill tempWB.FullName
tempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
Set xEmailObj = Nothing
Set xOutlookObj = Nothing
End Sub
Try something like this:
Sub SendemailAll()
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim tempFile As String
Dim strbodymsg As String
Dim wb As Workbook
Dim strbody As String
Dim tempFiles()
Dim DisplayEmail As String, Signature As String
Dim a, b, c, d As String
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
ReDim tempFiles(1 To ThisWorkbook.Worksheets.Count)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Copy
With ActiveWorkbook
Dim counter As Long
.SaveAs ThisWorkbook.Path & Application.PathSeparator & ws.Name, FileFormat:=51
counter = counter + 1
tempFiles(counter) = .FullName
.Close savechanges:=False
End With
Next ws
'Create Outlook email=============
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
End With
Signature = xEmailObj.Body
On Error Resume Next
With xEmailObj
a = ThisWorkbook.Sheets("Sheet1").Range("R1").Value
b = ThisWorkbook.Sheets("Sheet1").Range("R2").Value
c = ThisWorkbook.Sheets("Sheet1").Range("R3").Value
d = ThisWorkbook.Sheets("Sheet1").Range("R4").Value
.Display
.to = a
.CC = b
.Subject = c
Dim n As Long
For n = LBound(tempFiles) To UBound(tempFiles)
.Attachments.Add tempFiles(n)
Kill tempFiles(n)
Next n
'previously saved workbook with single sheet
.Body = d & Signature
If DisplayEmail = False Then
'.Display
'.Send
End If
End With
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
Set xEmailObj = Nothing
Set xOutlookObj = Nothing
End Sub
Thanks for answer, but seams when program copying sheets with ws copy it show error "failed".
Are there any hidden sheets in the workbook?
Yes, there were hidden files, I added some in the code.
Thank you very much for your help
Sub Sendemail()
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim tempFile As String
Dim strbodymsg As String
Dim wb As Workbook
Dim strbody As String
Dim tempFiles()
Dim DisplayEmail As String, Signature As String
Dim a, b, c, d As String
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
ReDim tempFiles(1 To ThisWorkbook.Worksheets.Count)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
'ws.Select
ws.Copy
With ActiveWorkbook
Dim counter As Long
.SaveAs ThisWorkbook.Path & Application.PathSeparator & ws.Name, FileFormat:=51
counter = counter + 1
tempFiles(counter) = .FullName
.Close SaveChanges:=False
End With
End If
Next ws
Just to note: cross posted in a couple of places:
https://www.mrexcel.com/board/threads/how-to-send-all-sheets-from-the-same-workbook-separately-in-one-email.1227035/
https://forum.ozgrid.com/forum/index.php?thread/1233082-how-to-send-all-sheets-from-the-same-workbook-separately-in-one-email/