Sending email from Excel to email addresses from a worksheet

  • Hi,
    I have an Excel worksheet were I store information of my stakeholders. In column G I have their email addresses. So whenever I am arranging a meeting with them I would like to send an email notification to all of them (group email) via a single button click right from that Excel worksheet. It’s like when command button SEND clicked it should pick up all available email addresses from column G as recipients and open the system’s default mail client with those email addresses in ‘To’ field. I guess some VB coding needed.
    Any help or reference is appreciated.

    Please note I do not want to send either worksheet or workbook within that email. I just need to open mail client with those emails selected from the corresponding column of that worksheet.

    Additional Info: Since the list of stakeholders is very dynamic and changes frequently (so do the email addresses) it is not convenient to use distribution list feature in Outlook for group mailing.

    I use Excel 2007 on Win 7.

    Many thanks
    marmanlive

  • Re: Sending email from Excel to email addresses from a worksheet


    Thank you S M C for your reply.
    I found some interesting add-ins for Excel but unfortunately non of the solutions covered my issue. I still need to send an email right form excel to all email addresses i have in G column of my worksheet without attaching that workbook. Any other ideas?


    Thanx

  • Re: Sending email from Excel to email addresses from a worksheet


    Dear ,


    See the attached file ,it should do the trick ,


    I am suprised nobody responded to you ,anyways your case is dam simple ,



    Not only do i give you a special button ,but also i give you a counter on H1 which shows you the number of emails as they get processed ,


    You write


    'So whenever I am arranging a meeting with them I would like to send an email notification to all of them (group email) via a single button click right from that Excel worksheet. It’s like when command button SEND clicked it should pick up all available email addresses from column G as recipients and open the system’s default mail client with those email addresses in ‘To’ field.'


    My attached macro will do exactly what you want ....you have a arrow for preview mails ,i have assigned the macro to that arrow ...incase you are interested to know the module no its one and in case you want to know the code ,it is ,





    It will loop until you have values in Column G ....note for multiple email ids ,just use ALT +ENTER before you type the next value ....You could paste subject ,body ,to ,cc,bcc ,attachment using excel macro but then your requirement was so specific that i designed this just for your requirement ...that is only to send mail to email id in Column G without any worksheet or attachment ,
    In Cell H1 ,i have attached a dynamic ticker which will telling you the numbers of mail getting created as they appear for preview ,it helps and gives a very nice feel ,



    Just be careful of one thing ,you are doing this in excel and connecting to outlook ,so you need to do referencing ,how you do that .....press alt+f11 to go to VBE editor ,select our module ,see tools above in the ribbon ,click references ,then in drop box that appears maske sure that microsoft office outlook 12 object library (or depending on your office version )is ticked...this is mandatory since you are connecting excel to outlook using outlook defined objects ...without doing this ,our code will fail ,



    Well ,i can explain you more ,but i won't think you would be interested ,



    Work the attachment and let me know the results,besides if possible do help others ,its because someone helped me that i help you and maybe someday you help someone ,lets make the world a better place



    Humble Regard's,


    Amlan Dutta ,


    "A small thank you will make me feel good "









  • Re: Sending email from Excel to email addresses from a worksheet


    Thanks Amlan for your code . my requirement is similar to this i want to send mail from exel to multiple mail ids but my every mail will contain attachments and every mails subject line is different and also the body of the mail is different . i want a macro so that it will pick the data from the excel and also it should attach a attachment from the path specified and send directly

  • Re: Sending email from Excel to email addresses from a worksheet



    Dear amlan009,


    I am really sorry for responding to your wonderful post so late... I was quite busy with other stuff and got away form this for a while.


    Now you see I am back and say my late "thank you" to you.


    As you see quite a long time has past since my last post and I would kindly ask you to advice about some new requirements in my Excel worksheet.


    Your VB runs Outlook new email window per email in each cell and for multiple emails you suggested entering them in the same cell with Alt+ENTER combination.


    1. The point here is that I need to run one single outlook new email window which will include all available emails from each cell of the column G in 'To' field.


    2. Besides that my column 'G' (for email addresses) changed to column 'C' and it starts from the 7th row. I desperately tried to edit the code to match it to my file changes but unfortunately failed to make it work.


    So once again, my gratitude to you for your help and if you could please advice on those new 2 issues.


    Many thanks.

  • Re: Sending email from Excel to email addresses from a worksheet


    Dear sudarshan and marman ,


    I will solve both your queries because they are extremely extremely easy ,but one at a time,


    Sudarshan ,i will go chronologically and first solve yours in this post ,


    See attached custom file for you (i think i made this for someone earlier ,but i don't have time to search that so i am posting it again for everyone's benefit ...my advise ,learn it dont heed to responses like "Look here ,look there "more you understand the field ,more you will be able to create your own solution tommorow ,



    Ok ,now listen - In the attached excel file ,i have made seperate columns for subject ,body ,attachment ,email id etc ...please note that the entries only will go to the respective fields in excel for each individual row starting from 4(because the first iterationn of the loop starts from 4 ) ,
    you simply type the subject ,email adress ,cc,bcc,body ,attachment in the respective columns i made for you and then you have 2 options


    -> preview or ->send directly without previewing


    In email column ,if you wanna enter more than one email id ,then use alt+enter inside the same cell to seperate entries


    Once done ,simply press preview to see the mails if you wanna see them before sending or simply press send directly ,if you wanna send directly without previewing ....


    I have created two sweet cute buttons "Preview "& send without preview and macroised this buttons and they will perfom like you command ,




    A.)Code for Preview Macro





    B.)Code for Direct sending


    I give a second option of sending directly without previewing ,here it goes ,





    One thing ,just insert a blank row where you may want the macro to stop .......i have programmed it to stop when it finds a blank value at email address


    Hope this solves any requirement you might be having ....if you require anything else wild in outlook macro ,just write back ,i can virtually do anything in excel outlook combination ,However -if topic is different ,start a new thread



    I will now look at macroman query ,




    Besides -This macro tested and checked .


    Humble regards ,


    Amlan Dutta

  • Re: Sending email from Excel to email addresses from a worksheet


    My dear Friend Marman ,


    I appreciate your thanks ,my friend .....I thought you would never read my solution ,luckily you did ...i take special interest in your case because i started learning VB macro first time when i posted your solution ,but i feel it was a good solution ...marman ,your question is very simple ,first you want email addresses initiation to start from row 7 ,email address colun to get shifted fro Cell G to cell C ,Alt+enter to work such that single outlook mail window contains all email reciptents in "To "Field....


    1.)-> Row 7 query -Solution - Customised solution s-You can now type in the excel sheet at cell B2 itself any row number you want and the macro will start from that point since i have initialised loop to start from user fed data at CELL B2 ...i keep value 7



    How it works -> Guess what ,i make a place for you in the attached excel sheet (see attachment )to exactly type in whatever row number you want to start sending mails from ....and based on your feedback ,the programme will initialise the loop from that point ,
    so in the attached sheet B2 is the cell wherein you should feed in the exact row number fro which you wanna start ....i keep it at 7 since you mentioned 7 ....
    so for the sheet you will see that the invoice corresponding to row seven is invoice 4 and thats hwo i know it works perfect



    2.)->- Shifting email adress to Cell C instead of G ,


    Luckily when i was making customised solution for sudarshan ,his email address was at C ,so thankfully i dont have to do reexercise and my solution for him will work for you as well but it still needed to be adjusted for row number initiation according to your need



    Third -ALT+enter to add email addreses in the same excel cell



    I think you are missing the point .Just see my attached file and press the preview button in the sheet .You will see that a single outlook mail window with all addressed will come .....


    say i have [email protected],[email protected] ,[email protected],i wanna have all of them in same outlook mail -simple put them in the same cell and seperate them by alt+enter method ....sy you are in cell A1 if you press enter ,you willland in cell A2 ,but if you press alt+enter ,you stay in A1 but to a next line in A1 and so the same cell ,and thats why when we wanna type [email protected],[email protected] ,[email protected], we simply use alt+enter to sperate each entity but in the same cell ....


    Anyways ,if this sounds complicated just see attached file and check it out practically ,you will feel happy whn you see outlook load .....



    4.)Attachment ,body ,subject -At present - i give path to my attacment in the desktop ,i type my own subject body in the sheet ,my own subject in teh attached sheet ...


    Change it to subject body whatever suits you,subject what suits you ,email addresses you want ,CC to people you need to send (Hope this much is clear )

    Doing this ,you will never need to type in outlook again anything ...everything can be customised from excel .....



    Here's the code (but i advise look at the attachment and run it from there by pressing preview button )





    Just note one thing ...if you press 8 in cell B2 ,then row reading will start from 8th row ,if you press 9 in cell b2 ,row reading will start from 9th row ....if you start from 100 ,it should be there in first place ,if not ,it will throw error ,out of
    bound .....


    Also wherever you wanna stop ,just insert a blank row ,by custom i have made it to work in such a way that moment it sees a blabk field in email address column it will stop


    Better still ,check the attachment and press preview button ....if you press preview you will exactly how it loads in outlook ,


    If you press send button ,mails will go without you getting a chance to preview ,



    Your wishes increased ,my solution evolved -Guess thats how life goes ,


    Please tell me anything you have in mind ,i will not ask you to look here there ,i will solve it for you ,



    Look forth to a small thank you .



    Macro tested and works .


    Humble regards ,



    Amlan Dutta

  • Re: Sending email from Excel to email addresses from a worksheet


    Dear Amlan,


    It is very encouraging to see fellow programmers helping each other.


    I will like to thank you for the code you provided to send a file as an attachment.


    In the code you provided you used <.Attachments.Add Filepath> to send the file provided in column B.


    Still I have a question. How about if you want to send multiple files within a Folder. Or better yet,
    how about if you want to send all the files within a Folder.


    Let’s say everything in C:\documents\.


    What will be the code: .Attachments.Add Filepath("") ???????


    Thank you in advance.


    ED


    "Those who do not create something, do not understand the Creator"

  • Re: Sending email from Excel to email addresses from a worksheet


    Hi Amlan,


    Thanks for this solution. I need it and tested it and it should work. There is a small problem that outlook is giving a message that a program is trying to send messages from your outlook and it may be virus and it is asking me to say yes or no for every mail. This is irritating and time consuming. WHat changes should I make.
    But thanks a lot for this wonderful approach to excel addresses being used in outlook.
    Anuj

  • Re: Sending email from Excel to email addresses from a worksheet


    Quote from agarwalanuj;621602

    Hi Amlan,


    Thanks for this solution. I need it and tested it and it should work. There is a small problem that outlook is giving a message that a program is trying to send messages from your outlook and it may be virus and it is asking me to say yes or no for every mail. This is irritating and time consuming. WHat changes should I make.
    But thanks a lot for this wonderful approach to excel addresses being used in outlook.
    Anuj



    Dear Anuj ,


    It is a registry problem !The easiest way to fix this is to have a proper working antivirus programme ,i have Macafee installed and i don't get the alert update ...i hope it works for you too !But as of now i can assure that with a proper working antivirus programme ,this thing works like a breeze ,


    To Chichito ,


    i am sorry for responding late ,for adding more files in the excel sheet ,you simply need to add the file names in teh same cell seperated by alt + enter ,
    Do you want me to attach a working example or you can manage ?


    Regards ,



    Amlan dutta

  • Re: Sending email from Excel to email addresses from a worksheet


    Hi,
    Spl thx to Amlan for beautiful solutions.
    I thought my query below will pertain in the same lines but difference being multiple cells to be sent to one email address.


    Hi,


    Very new to VB and reqt is high.
    I have a requirment where email ahs to be sent to different people.
    i.e. different email content and different subject line to different people based on value.


    Will explain now.




    Input DATA:


    Column A: Serial NO
    Column D: Destination Email ID
    Column H: Is the subject line of the email
    Column E, F, G: Content of email.
    Column J: If attachment is there, then we will send that attachment as well.


    Apart from this there should be a standard One liner in the mail.
    + signature.
    (Guess no need to put it in excel. Macro might embedd that one liner + signature.



    Requirement:
    Will try to explain with example that I have attached.


    Lets start at A1 i.e. Serial NO is 1 that means I will send email to "[email protected]" (D2) with
    contents from E2 to E7, F2 to F7 and G2 to G7 and subject as in H2.


    For Serial NO 2 ie. A8, email will be sent to "[email protected]" (D8) with
    contents from E8 to E13, F8 to F13 and G8 to G13 and subject as in H8.


    and so on..



    AND
    we have to have a standard content as well in email.
    eg: "Please send price tag for the components mentooned below" (shall we have it in one cell or we shall code in VB itself?)



    Hence, I shall have 20 or more (assumption)such Serial No. but one button click will send emails.
    So, its not mail merge where same content is sent to different people.
    Column J: If attachment is there, then we will send that attachment as well.
    Here people change and also content and that too with one click.


    Hope I was clear.



    IS this possible ?

  • Re: Sending email from Excel to email addresses from a worksheet


    Welcome to Ozgrid, eliteraj.


    Please don't post questions in threads started by other members. Start your own thread, give it a clear and concise title and explain your issue fully.


    If you think this thread can help explain or clarify your problem, you can include a link to it by copy the URL form the address bar of your browser and pasting into your message.


    This thread is closed.