asked on

VBA Excel Sending Multiple Workbooks with Outlook using Range of File Names, Recipients &

I have a concolidated workbook that programmatically breaks many of its worksheets into stand alone workbooks for the purpose of distributing them via Outllook to separate locations. I have a table of recipients that looks sort of like this:

LOC      Workbook Name      Regional VP      Area VP      General Manager
1      LOC1.XLS      Theresa Jones      Katie Martinez      John Smith
2      LOC2.XLS      Theresa Jones      Katie Martinez      Patty Reiker
3      LOC3.XLS      Theresa Jones      Tom Black      Sheryl Thomas
4      LOC4.XLS      Theresa Jones      Tom Black      Paul Green

I want to use this range to create drafts of emails with attachments. I'd like to send each location file to the Regional VP, the Area VP & the General Manager. I can write it to send the Regional VP 4 emails with 4 differents attachments, but what I'd really like to figure out is how a way to send the Regional VP one email with 4 attachments.

All the files are in the same directory as the consolidated file.

Is there a way to do this? Thanks so much for your help!

MWGainesJR

I think you can do something like this
......
Set msg = ol.CreateItem(olMailItem)
Dim atch As Attachments
atch.add "C:\sheet1.xls"
atch.add "C:\sheet2.xls"
.......
msg.Attachments.Add atch
......
msg.send
Haven't tested it though.

MWGainesJR

or it could be
msg.attachements = atch

MWGainesJR

after testing, this worked perfectly. It sent both attachments.

Dim ol As Outlook.Application
Dim atch As Attachments
Dim msg As MailItem
Set ol = New Outlook.Application
Set msg = ol.CreateItem(olMailItem)
Set atch = msg.Attachments

atch.Add "C:\test.xlsx"
atch.Add "C:\mycsv.csv"

msg.To = "person@domain.com" 'or a range when looping
msg.Send

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

socallilly

ASKER

Wow! Cool! Thanks! I'll try it!

Patrick Matthews

Glad to help :)

BTW, I would greatly appreciate it if you could go to that article, https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html, and click 'Yes' in the 'Was this helpful' voting.

Cheers,

Patrick

VBA Excel Sending Multiple Workbooks with Outlook using Range of File Names, Recipients &amp;

VBA Excel Sending Multiple Workbooks with Outlook using Range of File Names, Recipients &