Send individual email to members of a club

[Aussiebear]

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?

[Paul_Hossler]

Could you use an Outlook distribution list / contact list? - No programming required

Capture.JPG

Capture2.JPG

Capture3.JPG

[Aussiebear]

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.

[Paul_Hossler]

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

[Aussiebear]

Thank you Paul. Will the text bodies be saved as formal correspondence will need to accounted for?

[Paul_Hossler]

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