Generate Emails with Excel VBA by looping through sheets

[QMAN223]

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.

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

 

[QMAN223]

Could there be a table with all sheetnames in it?

Ahhh, yeah, there is a Sheet, with a table in it. That's what's causing the problem. It's a minor issue... Don't worry about it!

Thank you so much! I really appreciate your help! All the best! :D

 

[JEC]

Nice! You're very welcome

 

[QMAN223]

Could there be a table with all sheetnames in it?
The code always looks at .listobjects(1), considering every sheet has just 1 table

Yep, I have a sheet with a table that monitors the email lists.

How would I make the .attachments.Add dynamic? The file are in the same folder but the name of the files change for each email.

 

[JEC]

You have to specify some criteria for choosing the right attachment for each email.
I would probably use a sheettable with all filenames and emails next to eachother. Then do an index/match inside the loop to lookup the right filename for each mail.

Hard to explain in words but that is a method

 

[QMAN223]

You have to specify some criteria for choosing the right attachment for each email.
I would probably use a sheettable with all filenames and emails next to eachother. Then do an index/match inside the loop to lookup the right filename for each mail.

Hard to explain in words but that is a method

Yikes! That sounds difficult ahahah. I'm an excel noob but trying to learn as much as I can.

Thank you anyways!

 

[QMAN223]

You have to specify some criteria for choosing the right attachment for each email.
I would probably use a sheettable with all filenames and emails next to eachother. Then do an index/match inside the loop to lookup the right filename for each mail.

Hard to explain in words but that is a method

Hi JEC, sorry to bother you again. Can you show me a simple example of this, I'll try to figure it out...