Create mailing list address array (Excel 2k VBA)

Topic

New Reply

I want to create an easily-manageable mailing list on an Excel sheet that can be read into an array variable and passed to a sendmail command: ActiveWorkbook.SendMail Recipients:=Array(address1,address2,etc.). The addresses would come from a simple table:

	A
1	DistList
2	rasleyd@schwebels.com
3	rasleyd@schwebels.com

I plan to use a little macro to refresh the range, so as addresses are added or removed the range will auto-correct:

Sub macSetAddressRange()
Range(“A1”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
Range(“A1”).Select
End Sub

The next part just doesn’t work. The code reads the range and can string together the addresses, but the sendmail command refuses to accept the variable for its recipients parameter.

Sub macSendMail()
Module1.macSetAddressRange
Set Distlist = Sheets(“Sheet1”).Range(“DistList”)
For Each Address In Distlist
DistArray = DistArray & Chr(34) & “,” & Chr(34) & Address
Next
DistArray = Right(DistArray, Len(DistArray) – 3)
ActiveWorkbook.SendMail Recipients:=Array(DistArray)
End Sub

Can this concept work?

Reply | Quote

Replies

Try this:

    Dim varRecipients
    varRecipients = Sheets("Sheet1").Range("DistList")
    ActiveWorkbook.SendMail Recipients:=varRecipients

HTH

Reply | Quote

Such a simple solution!

Reply | Quote

You can use a dynamic named range instead of using code to update a fixed named range. See (for example) Named Ranges on Chip Pearson’s site.

Reply | Quote