Forum

How to send all she...
 
Notifications
Clear all

How to send all sheets from the same workbook separately in one email

6 Posts
2 Users
0 Reactions
111 Views
(@cdejan70)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 16/01/2023 4:49 pm
(@debaser)
Posts: 836
Member Moderator
 

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

 
Posted : 17/01/2023 12:30 pm
(@cdejan70)
Posts: 3
Active Member
Topic starter
 

Thanks for answer, but seams when program copying sheets with ws copy it show error "failed".

 
Posted : 17/01/2023 6:50 pm
(@debaser)
Posts: 836
Member Moderator
 

Are there any hidden sheets in the workbook?

 
Posted : 18/01/2023 4:04 am
(@cdejan70)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 18/01/2023 12:23 pm
(@debaser)
Posts: 836
Member Moderator
Share: