Macro to Mail out my Reports

Hello again, I am looking to make my job a bit more simple and help some co-workers at the same time. I run these Excel reports off a data webpage everyday and sent bits of data from them to several locations all around the US. I have already created a Macro to make these reports pretty and have found out that you can use Outlook with Excel to send data.
I have an outlook template made and all I need to do it place data off my report into the body of this template and insert the correct adress and CC to these locations.

My e-mail template already has a subject line and most of the body complete.

What I need:
1. Copy data from Excel to Outlook Template into a certian section of the body.
2. Add the correct e-mail adress/CC based on a value in the data copied.
3. Send the e-mail
Right now I must manually copy and paste data out of my excel and place it into the body of my outlook template. Then lookup the location managers e-mail of the location I am
sending this data too. A macro to do this would be much more efficent.

I will add an example of the report in the exact format needed. All data in this report is false and only for the use of this example.

Thanks in advance.

See this page: Example Code for sending mail from Excel

Hi TWent

Can you show us the code you have? Is your code running from Outlook or Excel? Attach the workbook you have (including code).

Reinventing/Guessing only makes possible solutions more difficult.

John

Hi TWent

Try the code in the attached. It should get you started. If I can help, feel free to ask.

I'd recommend you put a break point in the procedure before you run or you'll get over 30 emails displayed.

Let me know of issues.

John

Hi TWent

Sorry, I attached the wrong workbook. The attached does this

how can I make is so the e-mail will open with my template

In the Procedure "Mail_Reports()" you'll need to change this line of code

Please Login or Register  to view this content.

Regarding this

the body will snap to a grid I have made

I'm not familiar with this process. If you will attach a copy of your template, perhaps I can figure something out. You'll probably need to zip the file as .oft is not a valid file name for attaching.

John

Hi TWent

I've some ideas...it may take a little while.

John

Hi TWent

The attached should be getting close. This has been tested with Excel 2007 and Outlook 2007. Don't know what happens with earlier (or later) versions.

Again, you'll need to change this code

Please Login or Register  to view this content.

If you wish the procedure to send the Emails, change this code

Please Login or Register  to view this content.

(uncomment ".Send")

Let me know of issues.

John

PS: I noticed I wasn't picking up all the headings in the email. Change this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.