Announcement

Collapse
No announcement yet.

Link To Multiple Email Addresses From Cell

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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 :
        Code:
        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
        Regards.
        Daniel

        Comment


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

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                Re: Link To Multiple Email Addresses From Cell

                Hi, Lora.
                Here the new code :
                Code:
                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

                Comment


                • #9
                  Re: Link To Multiple Email Addresses From Cell

                  This is brilliant!

                  Merci beaucoup!
                  Lora

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Matt,

                      Please start a new thread in the appropriate forum and if you wish place a link back to this thread stating your requirements.

                      This information is in the forum rules please read them.
                      Reafidy

                      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                      Comment

                      Working...
                      X