+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

    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.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Hi Team,

    Please help..

  3. #3
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Hi Team,

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

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

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

    maybe
    Please Login or Register  to view this content.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  5. #5
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Quote Originally Posted by john55 View Post
    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..
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

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

    see if it works
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Quote Originally Posted by john55 View Post
    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.

  8. #8
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

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

    Quote Originally Posted by arun.sj View Post

    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.
    I am sorry, perhaps I did not read the whole yr rqst!

  9. #9
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

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

  10. #10
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Hi Team, Please help me with few suggestions..

  11. #11
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

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

  12. #12
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    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..
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

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

    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.

  14. #14
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Quote Originally Posted by john55 View Post
    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..

  15. #15
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

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

    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!

  16. #16
    Forum Contributor
    Join Date
    06-05-2014
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    139

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

    Quote Originally Posted by john55 View Post
    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..
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

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

    hi,
    this is what I can offer to you!
    the code takes files from individual folders in accordance with yr project name
    the code solves what you rqsted regarding the picture and font/size, just change them for your needs.also save a picture (on yr desktop...) in order to be embedded in the email's body.

    Cheers
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert excel sheet to pdf using macro and send that pdf via email using outlook
    By suresh mongam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2015, 08:51 AM
  2. [SOLVED] Macro to send email from excel using outlook
    By Teblol in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 02-10-2015, 06:47 PM
  3. Macro to send notification from excel to my Outlook Email
    By savethisid in forum Excel General
    Replies: 18
    Last Post: 03-27-2014, 02:25 PM
  4. Excel containing macro to send bulk mails from outlook with multiple attachments
    By amandeep08 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 02:19 PM
  5. Replies: 2
    Last Post: 01-06-2010, 06:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1