VBA Code to Send Email Using Excel

[rsrasc]

Hi all,

Hope you all doing great!

I'm using the following code to email workbooks to various recipients but only works with one file.

If I try to add a second file if doesn't work.


For example, if I add this workbook to the code, the email still pick up the first file listed in the code but not the second one.


Attached_File = "Z:\FY23 Budget\SSE Expenses\050 FY 2023 Expenses-SSE-Budget.xlsx"



Could someone please assist me?


Cheers!



Sub Engagement_025_SSE()
Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")
Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = “nothing@vbaexcel.com”
MyMail.Subject = "Monthly Variance Report-Mar 23"
MyMail.Body = "Hello all "
Attached_File = "Z:\FY23 Budget\SSE Expenses\025 FY 2023 Expenses-SSE-Budget.xlsx"
MyMail.Attachments.Add Attached_File
MyMail.Send
End Sub

[June7]

I don't see any code to attach multiple files, only one file as defined with Attached_File variable.

How should code know what files to send? Are they listed in a table/worksheet and you should loop through cells of column? Or do you need to loop through a folder to pull multiple filepath/name?

[rsrasc]

Hi June7,

Thank you asking. Below are my answers. Hope this will help!

When I submitted the code, the only file that was defined was the following: Attached_File = "Z:\FY23 Budget\SSE Expenses\025 FY 2023 Expenses-SSE-Budget.xlsx". If I add another one, like this:

Attached_File = "Z:\FY23 Budget\SSE Expenses\050 FY 2023 Expenses-SSE-Budget.xlsx", when I run the macro--the output or email will pick up only the first one.



The files are not listed in a table or worksheet and will not loop through any cells or columns in a sheet or workbook so the macro should loop through the path/folder to pull maybe one (in some cases) or multiple files (two to fives but varies).



Attached_File = "Z:\FY23 Budget\SSE Expenses\025 FY 2023 Expenses-SSE-Budget.xlsx"-----this one will work and it will be pick up by the email
Attached_File = "Z:\FY23 Budget\SSE Expenses\050 FY 2023 Expenses-SSE-Budget.xlsx"-----this one will not work and will not be pick up when sending the email.

[June7]

You would need an Add line after each instance of setting variable. Or don't bother with variable.


MyMail.Attachments.Add "Z:\FY23 Budget\SSE Expenses\025 FY 2023 Expenses-SSE-Budget.xlsx"
MyMail.Attachments.Add "Z:\FY23 Budget\SSE Expenses\050 FY 2023 Expenses-SSE-Budget.xlsx"

[rsrasc]

Hi June7,

Thank you for your assistance and cooperation. Much appreciated! Code is working great!

[June7]

Okay, but not showing code looping through folder, if that's what you really need.

[rsrasc]

Hi Junu7,

After updating the code you provided me, the macro is working as intended.

This is what I used to run the macro.



Sub Engagement_025_SSE_SSX()
Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")
Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = "brown.karen@vba.com"
MyMail.To = "sugar.chris@vba.com"
MyMail.Cc = "rod.john@vba.com"
MyMail.Cc = "hoff.marci@vba.com"
MyMail.Cc = "pop.jennifer@vba.com"
MyMail.Cc = "jack.jolanta@vba.com"
MyMail.Subject = "Monthly Variance Report-Mar 23"
MyMail.Body = "Hello all," & vbNewLine & vbNewLine & "Here is the Monthly Variance Report for your deparment. Please review the entries and let me know if you have any questions." & vbNewLine & "Thanks!" & vbNewLine & "Roberto"
MyMail.Attachments.Add "Z:\FY23 Budget\SSE Expenses\025 FY 2023 Expenses-SSE-Budget.xlsx"
MyMail.Attachments.Add "Z:\FY23 Budget\SSX Expenses\025 FY 2023 Expenses-SSX-Budget.xlsx"
MyMail.Send
End Sub




After running the macro and testing the output I found that the following code does not work as intended for some of the recipients (see below code). With this I mean not all the recipients will received the email with the attachments.



MyMail.To = "brown.karen@vba.com"
MyMail.To = "sugar.chris@vba.com"
MyMail.Cc = "rod.john@vba.com"
MyMail.Cc = "hoff.marci@vba.com"
MyMail.Cc = "pop.jennifer@vba.com"
MyMail.Cc = "jack.jolanta@vba.com"


What will work for the recipients is the following:



MyMail.To = "brown.karen@vba.com;sugar.chris@vba.com"
MyMail.cc = "brown.karen@vba.com;sugar.chris@vba.com"

Thanks!


Finally, I'm not an expert on VBA Code but with this code the macro is working.

[June7]

If you want one email to go to multiple TO and multiple CC, build address lists.


MyMail.To = "brown.karen@vba.com;sugar.chris@vba.com;
MyMail.CC = "rod.john@vba.com;hoff.marci@vba.com;pop.jennifer@vba.com;jack.jolanta@vba.c om"