Consulting

Results 1 to 6 of 6

Thread: Send individual email to members of a club

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,125
    Location

    Send individual email to members of a club

    Recently a sports club of which I'm a member, has started sending emails out on the same subject matter. Over time the list of recipients is growing, showing names of members and their respective email addresses. The following code provided by Microsoft.com as a learning aid suggests that its possible to send each of the members the same email

    Sub Sample()
       'Setting up the Excel variables.
       Dim olApp As Object
       Dim olMailItm As Object
       Dim iCounter As Integer
       Dim Dest As Variant
       Dim SDest As String
          'Create the Outlook application and the empty email.
       Set olApp = CreateObject("Outlook.Application")
       Set olMailItm = olApp.CreateItem(0)
          'Using the email, add multiple recipients, using a list of addresses in column A.
       With olMailItm       SDest = ""
           For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
               If SDest = "" Then
                   SDest = Cells(iCounter, 1).Value
               Else               SDest = SDest & ";" & Cells(iCounter, 1).Value
               End If
           Next iCounter
               'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
           .BCC = SDest
           .Subject = "FYI"
           .Body = ActiveSheet.TextBoxes(1).Text
           .Send
       End With
          'Clean up the Outlook application.
       Set olMailItm = Nothing
       Set olApp = Nothing
    End Sub
    How does this work? What procedures does the Secretary take to set up the process?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,380
    Location
    Could you use an Outlook distribution list / contact list? - No programming required

    Capture.JPG

    Capture2.JPG

    Capture3.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,125
    Location
    I guess she could do that. Committee, Individual members of the Committee, All Club Members, Male, Female, Juniors etc. The lists (Groupings) could be defined within Excel and transferred to Outlook.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,380
    Location
    This is just a concept using Excel and Outlook sort of like a MS Word Mail Merge

    I made up a simple database with some filterable fields.

    Filter the names based on what ever, and click the button to show a userform for the Subject and Body

    Capture0.JPG
    There are replaceable entities in the Body that will be replaced with field values to customize the Body. Don't have to use them to send email

    Attachment 29625

    Capture3.JPG

    BTW, the SendWithOutlook module is one that I use so it includes things that you might not need

    If you don't want to use it, it's OK -- you won't hurt my feelings
    Attached Images Attached Images
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,125
    Location
    Thank you Paul. Will the text bodies be saved as formal correspondence will need to accounted for?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,380
    Location
    The emails will show up in Outlook's Sent Items folder

    You could capture the Date / Time / Recipient / Body in a log file or on another worksheet
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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