Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Link To Multiple Email Addresses From Cell

  1. #1
    Join Date
    27th June 2008
    Posts
    5

    Link To Multiple Email Addresses From Cell

    Please forgive my ignorance but I have a very basic working knowledge of Excel and am stuck with trying to create a Hyperlink to a list of email addresses. I have read some of the other posts on similar topics but cannot understand the answers (I am not familiar with codes at all).

    To explain:

    Cell A1 contains the text "Email All"

    Cells L4:L50 contain the relevant email addresses

    I have tried to create a Hyperlink but there is a limit to how many addresses I can fit in. How can I get round this problem?

    Any help gratefully received - please assume I am a complete idiot when responding!

    Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th March 2008
    Posts
    449

    Re: Link To Multiple Email Addresses From Cell

    I can't figure out what you want to do. Can you post a sample file with what you have achieved ?
    Regards.
    Daniel

  3. #3
    Join Date
    27th June 2008
    Posts
    5

    Re: Link To Multiple Email Addresses From Cell

    Hi,

    I am attaching a sample file. Basically, I have a list of email addresses and I want to be able to send one group email to all of them with one 'click' on the cell that contains 'Email all'.

    Hope that makes sense,

    Thanks.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th March 2008
    Posts
    449

    Re: Link To Multiple Email Addresses From Cell

    Have a look at the attached file. The macro is fired by a double click on the desired cell (I don't know how to do it with a simple click). Here is the code :
    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        Dim OlApp As Object, M As Object, c As Range 
        If Target.Address <> "$A$3" Then Exit Sub 
        Cancel = True 
         ' Creates an instance of Outlook
        Set OlApp = CreateObject("Outlook.application") 
         'Creation of the message
        Set M = OlApp.CreateItem(olMailItem) 
        With M 
            .Subject = "Subject" 
            .Body = "Body" 
             ' Adding the recipients
            For Each c In Range("B6", Range("B60000").End(xlUp)) 
                .Recipients.Add c.Value 
            Next c 
            .display ' Use this line instead of the next one if you want to see / edit
             ' the Message
            .Send 
        End With 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Regards.
    Daniel

  5. #5
    Join Date
    27th June 2008
    Posts
    5

    Re: Link To Multiple Email Addresses From Cell

    Daniel,

    This is great and seems to be just what I need. Thank you!

    I have never worked with codes before and am trying to work out how to copy this code into my actual document. Any chance you could explain how to do this?

    Lora

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th March 2008
    Posts
    449

    Re: Link To Multiple Email Addresses From Cell

    From the Exel window :
    Open both source and target workbooks, then
    "Tools", "Macro", "Visual Basic Editor" (or ALT+F11).
    From the VBE window, left side, find the source workbook, then the sheet (indicated : "Feuil1 (Sheet1)". Double-click on that name and copy the code. Locate your workbook and double click on the sheet, then paste the code on the right.
    Regards.
    Daniel

  7. #7
    Join Date
    27th June 2008
    Posts
    5

    Re: Link To Multiple Email Addresses From Cell

    Hi Daniel,

    I have managed to copy the code (finally!) and it's all working great! There's just one more thing...

    As well as being able to 'Email All' I would also like to be able to select a sub-group from the list and email them. Is it possible to have another code in the same worksheet? How would I set up another code, and what would I need to do to alter the range selected?

    I have attached an example document.

    I am so grateful for all your help!

    Lora
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th March 2008
    Posts
    449

    Re: Link To Multiple Email Addresses From Cell

    Hi, Lora.
    Here the new code :
    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        Dim OlApp As Object, M As Object, c As Range 
        If Target.Address = "$A$3" Then 
            Cancel = True 
             ' Creates an instance of Outlook
            Set OlApp = CreateObject("Outlook.application") 
             'Creation of the message
            Set M = OlApp.CreateItem(olMailItem) 
            With M 
                .Subject = "Subject" 
                .Body = "Body" 
                 ' Adding the recipients
                For Each c In Range("C6", Range("C60000").End(xlUp)) 
                    .Recipients.Add c.Value 
                Next c 
                .display ' Use this line instead of the next one if you want to see / edit
                 ' the Message
            End With 
        ElseIf Target.Address = "$A$4" Then 
            Cancel = True 
             ' Creates an instance of Outlook
            Set OlApp = CreateObject("Outlook.application") 
             'Creation of the message
            Set M = OlApp.CreateItem(olMailItem) 
            With M 
                .Subject = "Subject" 
                .Body = "Body" 
                 ' Adding the recipients
                For Each c In Range("C6", Range("C60000").End(xlUp)) 
                    If c.Offset(, -1) = "Manager" Then 
                        .Recipients.Add c.Value 
                    End If 
                Next c 
                .display ' Use this line instead of the next one if you want to see / edit
                 ' the Message
            End With 
        End If 
    End Sub 
    
    
    You can go even further on with a drop down list containing the categories (eg. Managers) to be mailed.
    Regards.
    Daniel

  9. #9
    Join Date
    27th June 2008
    Posts
    5

    Re: Link To Multiple Email Addresses From Cell

    This is brilliant!

    Merci beaucoup!
    Lora

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st June 2010
    Posts
    1
    This is my 1st posting on this forum. I have just located this post and find that it will very likely do what I need. Since this post is about 2 years old, will someone advise me if it is proper to post here since I want to use this code. I do have some questions regarding the code.

    Thanks
    Matt Gentry Sr.
    Ontario, Calif.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Extract Duplicate Email Addresses In Same Cell
    By zapszipszops in forum EXCEL HELP
    Replies: 6
    Last Post: October 25th, 2007, 00:18
  2. Macro To Automatically email Sheet To Two Email Addresses
    By x-cell in forum Excel and/or Email Help
    Replies: 1
    Last Post: April 8th, 2007, 01:41
  3. email multiple sheets to specific addresses w/outlook
    By amnicbra in forum Excel and/or Email Help
    Replies: 8
    Last Post: June 16th, 2005, 23:16
  4. email groups - list addresses for mass email
    By ACAnderson in forum Excel and/or Email Help
    Replies: 3
    Last Post: September 12th, 2004, 07:02
  5. Email: Capturing email addresses via a UserForm MultiSelect
    By VBApilot in forum Excel and/or Email Help
    Replies: 2
    Last Post: September 1st, 2003, 07:01

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
  •  
porno