Feedback/Ideas for Email Group Generation

[Alphacsulb]

Hello, I'm looking for feedback on developing an email group database that will turn into a macro to create a lists.


I have 6 sheets:
Primary (Primary Directors)
Alternate (Alternate Directors)
Secondary (Second Alternate Directors)
Managers (Other Staff who also receive information)
Staff (Support Staff for Directors)
Committees (Committees composed of Directors)


Emails in each sheet reside in Column D starting in row 10.


My question is, what is best practice for establishing different types of lists? Helper Columns with X's for a category?


Examples of list I would want to generate:
All emails from Primary and Alternate sheets.
All emails from Primary, Alternate, and Staff where "X" is marked in Column AA.


Thanks for the feedback, and I'm also looking looking for guidance whether each list should be its own macro or whether it should be maybe a data validation list?

 

[baitmaster]

Best practices = single source of the truth, and DRY (don't repeat yourself)


I would always seek to have one single list of staff with criteria against each one, rather than 6 separate worksheets - do you gain anything from having 6 separate sheets (i.e. is there some other info that I'm not aware of?) or have you just laid it out this way? 6 lists = 6 columns to be added whenever you need a new field. Promotions = having to move a user from one sheet to another, risking having them in both... 1 list = just change the grade


Macros will process one single block of data much quicker and easier than multiple blocks of data - for example you could instantly extract all Directors using one single-line SQL query (see example below), rather than have to first decide which sheet you're looking at. The nature of the query would be different depending which email list type you want to process, but everything else would be the same. Adding additional sub-lists would be just adding certain categories to that one main data table with whatever values you wanted - "X", department, user group, years service....


If you wanted to go down the SQL route - easily done - then you would need one worksheet with one header row containing categories 'name', 'grade', 'email', then any other categories you want e.g. 'list1', 'list2' etc. Within the VB Editor you'll also need to go Tools > References > Microsoft ActiveX Data Objects x.xx Library


Code needed to make a workbook perform SQL queries on itself:

Option Explicit

' this module contains features that support the use of SQL to quickly return data subsets


' database connection, table & field variables
Public adConn As ADODB.Connection




Sub connectionOpen()


' set ADODB connection to thisworkbook
Set adConn = New ADODB.Connection
With adConn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With


End Sub




Sub connectionClose()


' close connections to remove from memory
adConn.Close
Set adConn = Nothing


End Sub

code to return an ADO Recordset (set of database results) from Sheet1 (assumed name of your worksheet) where we define a specific grade, so we can process the contents

Function rsRecords(strGrade As String) As ADODB.Recordset

' create SQL string
Dim strSql As String
strSql = "SELECT * " & _
         "FROM [Sheet1$] " & _
         "WHERE [Sheet1$].[Grade] = '" & strGrade & "';"    ' note the ' character around the text string, and the spaces at the end of each line. You don't need to split lines using " _" but I find it easier to read


' assign results to function
Set rsRecords = CreateObject("ADODB.Recordset"): rsRecords.Open strSql, adConn, adOpenStatic, adLockOptimistic, adCmdText


End Function

example code to pull together the above and turn the results into an email list:

Sub processEmail()

Dim strEmail As String
Dim rs As ADODB.Recordset


' configure data connection
call connectionOpen


' retrieve recordset for Director grade
Set rs = rsRecords("Director")


' loop through all records and extract the "email" field
With rs
    .MoveFirst
    Do
        strEmail = strEmail & rs.Fields("email") & "; "    ' "; " is the standard email delimiter
        .MoveNext
    Loop Until .EOF ' means End Of File
End With


' remove connection from memory
call connectionClose


' confirm results to User
MsgBox strEmail


End Sub

To use this approach you'll need to get to grips with (1) basic SQL statements using the starting point I've given above, and (2) nuances of how to write these in VBA, such as [sheet name].[field name] using the square brackets and the . notation

This code is mostly taken from working systems but the overall result is untested