Excel VBA Macro: Send Multiple Emails (with Multiple Attachments)

Excel VBA Macro: Send Multiple Emails (with Multiple Attachments)
Code (KZbin doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub multi_emails_attachments()
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim strbody As String
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Set ws = ThisWorkbook.Sheets("Emails")
ws.Activate
row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
For i = 2 To row_count
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "LT BODY style = font-size:12pt, font-familt:Arial GT" & _
"Hi Team, LT p GT Please see file(s) attached. LT p GT" & _
"Thanks, LT br GT Greg"
On Error Resume Next
With OutMail
.to = ws.Cells(i, 1).Text
.CC = ""
.BCC = ""
.Subject = ws.Cells(i, 2).Text
.Display
.HTMLBody = strbody & .HTMLBody
For j = 3 To col_count
.attachments.Add ws.Cells(i, j).Text
Next j
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Next i
End Sub
#excelvba #excelmacro

[@Jojosmith342]

Thank you so much Greg for your other excellent tutorial. This is what I have searched for so long as I have a list of > 500 of email receipients and noone made a youtube about multiple receipients with multi-attachments. I actually need attach the multiple tables (instead of attachments) inside the email body that will be sent to the long list of receipients >500. I copied down the code (save a lot of time typing out). Your code works perfectly. You are genius coder and great teacher. 🙏

[@greggowaffles]

Thank you! So glad to hear that!!

[@CollDott]

Must be another masterpiece!! Will indulge soon!! 😆

[@greggowaffles]

Thanks for the continued support!!

[@kpollatos]

Hi Greg! I have to say your coding is very helpful as I already adopted it in my excel file for sending mulitple emails with attachments! So Many Many Thanks ! I have a question though. Is it possible to send the emails when filtering out some of the rows? I did put some filters in the top row and when displaying let's say 3 out of 10 rows the script will not send the email only to the 3 rows being displayed, but to all the rows from the first to the one that is the last of the 3 rows. For example if we have a total of 10 rows of data and filter out some of them and display only row 3, 4, 7 the script will not create emails only for rows 3,4,7, but it will create for rows 2 to 7. So, is it possible to customize the script in order to take into account only the filtered (displayed) rows? Thanks again, Costas!

[@nagakishorel1368]

If 'To' email address is blank cell then email shouldn't draft can you please help code in this scenario

[@nixnews2267]

Fantastic Greg Greg, it would be better for us if you could add '.send' as we use this sending mails for n number of recipients. Please add... Thank you I must say your codes are very easy to understand.

[@greggowaffles]

Thanks! I’ll keep that in mind going forward!

[@adrianomarcos3615]

Hey Greg, thank you for your tutorials, they're very helpfull. Have you already made a tutorial, where you show how to send emails hour by hour until the user stop the macro?

[@lydiajin9647]

This is so helpful Greg. But one question - how can I make the macro to go to the next file path if there is any errors in the one before? Thank you!

[@DavidElliot13]

I just noticed that the TEXTBody when Manipulating Text Font and style did not update on the email itself.... Outlook reports the text back as the standard Calibri 12pt

[@Morphru]

Hey Greg, thanks for the video, all was working until the attachment part. Email windows do pop up but without attachment. I tried locations on one drive as well as on my local drive. Path should be also correct, because the files did open when copypasting them into the file explorer path bar. Also im getting no errors. Any idea how to troubleshoot this? Thanks

[@bronxxneho]

Very helpful , I have a question, why do you add two .HTMLBody to the code?

[@deekshi999]

Getting an error saying 'Can't execute code in break mode'. How can I fix this? Please help

[@poweroftradingandinvestmen467]

It is only drafting an email not sending How to send it