Macro to send mails from outlook as per the email ids in excel

Hi Team,

I am trying to find codes to automate one of the manual activities that I do daily.

I am sending a same mail from outlook to different people. The body of the mail remains the same, people in “To”, “CC”, attachment, and part of subject changes in every mail.
I have an excel with list of projects, Email IDs of the project who should be in “To” and “CC”. So if there are 10 projects in the excel, I will have to draft 10 emails with same content in the body of the email.

I am looking for codes by which if there are 10 projects in the excel, 10 mails are created with people in “To” and “CC” updated as per the excel and mail is saved in drafts folder. Attachments for each mail is saved in a folder in my desktop.
Subject of the mail is “welcome to - “project name”. “Welcome to” is common in all mails but project name should be as per the excel.

Please help me with suggestions. Sample data attached. Please help.

Hi Team,

Please help..

Hi Team,

Please help.. Please let me know if this is doable..

Originally Posted by john55

maybe

Please Login or Register  to view this content.

Thanks a lot for your reply.. I appreciate the way you explained the codes..

I an getting the error that says "Run-time error 440: Automation error "..

Please help.. Attaching the file with codes..

Originally Posted by john55

see if it works

Thanks a lot John for updating the codes. I am excited and happy to see this working . Please help to make few more changes..

The “To “and “CC” is working perfectly fine. I also have the “From” field enabled and have access to send from multiple mail Ids. The cell H2 in the latest excel has the mail ID that has to be updated in the “From” Field. Please help by updating the codes so the “From” is updated as per H2.

Attaching the doc in the mail is not working as expected. The current macro picks the file based on the filename that is updated in the codes. That has to be updated in such a way that the macro picks up the right file from the destination folder.
The folder in my desktop will have many excel files named welcome AAA, Training AAA, welcome ABB, Training ABB etc.. So when the mail is automatically drafted for the project “AAA”, it has to attach all the files that end with “AAA.xlsx” from the destination folder.

Please also help in bringing the image in the excel to the body of the email. The image should be under the sentence “We welcome you all to the project...”
Requesting for your help.

Hi John.. Requesting for your help with updated codes.. Please help..

Hi Team, Please help me with few suggestions..

Hi Team.. Please let me know if this is doable.. Please help..

Hi Team.. Please help with some suggestions please.. The codes in the attached file is working expect attaching the files..
Please help by updating codes in such way that the files that have the project name is attached in the mail..

Originally Posted by john55

hi,
try this one! the credits go to GTO!
according to your data, add in column F the full path for each project
I mean in row 2 col B you have AAA, so the path will be c:\users\george\desktop\AAA, AAA=folder name
row 3 col B you have ABB, so the path will be c:\users\george\desktop\ABB, ABB=folder name
and so on.
hope it help you!

Please Login or Register  to view this content.

Thanks a lot John and GTO for help... My luck is not that great as far as attachment is concerned..

I tried these codes after creating individual folders for each project, but it's not attaching the files..

Also, my requirement is not to pick from individual folder.. The folder is only one.. ie all the files are saved in the same folder..
The folder in my desktop is same for all the files.. It will have many excel files named welcome AAA, Training AAA, welcome ABB, Training ABB etc.. So when the mail is automatically drafted for the project “AAA”, it has to attach all the files that end with “AAA.xlsx” from the destination folder.

Please help.. If you can update the codes in the sample and give, it would be a blessing to me..

Originally Posted by john55

it works with different folders according to yr projects name, I do not know how to attach the file(s) from a single folder to different recipients! I am sorry!

Thanks a lot for replying John... The macro is going to reduce several hours of my work .. The sample data has only 10 projects, but in reality its 30 to 40 projects.. You are a game changer!!

Let’s hold on to the attachment part bcoz I am using a diff macro that saves all the files to one folder. So saving to individual folders is not possible now. Let me update the other macro to create individual folders post which I will use your latest codes.

Attached is the latest version of sample data.. I made minor changes to add more columns in TO and CC and to save the mails in the draft folder.

I have few clarifications more before closing the thread..
Can you help me bring the image in the excel to the body of the email.. Also please let me know how to change the font and size of the sentences in the body of mail and change the page color..
Please help..