Hi,
I have 20 sheets which have Emails in a Table. (See image below which describes how the table is set up)
Currently, in each sheet, I have a Macro Button that generates the email. I have to manually go into each sheet in the file and generate the email.
I want to create a loop, where if I click one button, all the emails are subsequently generated for each sheet.
I have 20 sheets which have Emails in a Table. (See image below which describes how the table is set up)
Currently, in each sheet, I have a Macro Button that generates the email. I have to manually go into each sheet in the file and generate the email.
I want to create a loop, where if I click one button, all the emails are subsequently generated for each sheet.
VBA Code:
Option Explicit
Sub Send_Email_With_Attachment()
Dim OutApp As Object, OutMail As Object
Dim emailTo As String, emailCC As String
Dim lastSunday As Date
Dim c As Range
lastSunday = DateAdd("d", 1 - Weekday(Now), Now)
emailTo = WorksheetFunction.TextJoin(";", True, ActiveSheet.Range("Email2[To]"))
emailCC = WorksheetFunction.TextJoin(";", True, ActiveSheet.Range("Email2[CC]"))
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = emailTo
.CC = emailCC
.Subject = "Training Report - " & Format(lastSunday, "dd-MM-yyyy")
.Body = "Dear All" & vbCrLf & vbCrLf & _
"Please find attached the Weekly Training report." & vbCrLf & vbCrLf & "Kind Regards,"
'.Attachments.Add ""
.Send
End With
End Sub