Automate Outlook Emails with Attachments in Excel

I took some liberties with this. I converted the raw data to an Excel Table. There are too many advantages to Excel tables not to use them. So I suggest you read up on them. I had to change the data to get the condition that 1755 should have only one email address. The way you had the data, it would have had two.

I made two pivot tables to help me with formatting the data. The one in column A is a unique list of projects. The other one gets the company name, documents and email addresses for a project. I overlay the results of the pivot tables with named dynamic ranges.

I use an outer loop for the project list, and two inner loops for the documents and the email lists.

I feed the results to my "standard" mail program. I suggest you save this module since it can be used with other VB code. All you have to do is pass the parameters to it.

With a little more work I can make a better Subject line (maybe including the project) and body (ditto) - the body I have is simply the documents, one per line. If you really want to make another spreadsheet and copy these values in and attach the spreadsheet, I can do that too.

I tested the mailer with the FALSE flag.

Please Login or Register  to view this content.

With the false flag, the mailer will create the emails and put them on the desktop, but you will have to click the send button to send them. This gives you the chance to edit the email before sending it. Change the last argument from False to True and the mail goes out without human intervention.

Please Login or Register  to view this content.

I've made a few adjustments to make it easier for you to manage the mail.

The email addresses were done using a named dynamic range. I adjusted that so it now points to the correct column again. Also I added the company ID to the pivot table. If you don't want to see this, hide the column.

I've included a Control Panel.

You can basically "fill in the blanks". The Subject Line is calculated based on what gets returned in the pivot table. The Flag is sent to false, so the mail gets stacked on the desktop. Flip it to true and the mail will go out automatically.

Press the Send Mail button and the mail will go out.

Let me restate what I thought you just said as I think I missed something in the requirement.

You want an attachment (What format? Excel or PDF would be easiest) and it would contain the information that is shown in the pivot table. I will give the file a name based on the company ID and date unless you have a better idea. Finally, do you want to keep an archived copy of the attachment or do you want to dispose of it after posting. I can also put another item on the control panel: the directory in which you want to save the attachments if you choose to archive them.

My mail code is already set up to mail attachments, so it is merely a matter of creating it.

Here you go.

I added an attachment path. You will have to set this up manually the first time.

The Attachment file is computed automatically

There is also a Keep Attachment flag. True - the attachments will be kept. False - the attachments will be deleted after the mail is sent.

Do you have outlook installed?

It should not have to be running.

It works, thank you so much. I had to reboot my computer for some updates to run.

Hello again dFlak, I previously marked the post solved, but didn't realize the pivot table data is not in the body of the email. Is it possible to have the data in both places (the body of the email AND as an email attachment?