use excel to distribute emails to different e

March 11, 2011 at 16:22:10
Specs: Windows XP, quad core 3.4ghz/ 4gb
ok here's the deal, every week i need to send 100+ emails to people depending on the server it is for as a notification.

i have a distribution list with each server and who to contact - Up to 10 people sometimes per server.

I want make a script or something to do the following:
select multiple servers i want to send to.
each server has a list of different email addresses and each server is related to few different studies which i need to list in the email.

for example: my distribution list is like this.

Server | Study | Email
1 | X |
1 | X2 |,
2 | Y |,
2 | Y2 |,
3 | Z |,

Ok so i select server 1 and 2, then it will write separate email to the people that is listed to sever 1 and 2.
However in the email there is one part that i need to list the name of the study like so.


Dear ALL,

this server is under maintenance for next hour.
the study that will be effected are:



Please give me some advise on how to do this.

Thank you so much.

See More: use excel to distribute emails to different e

March 16, 2011 at 12:13:02
no one can help?

Report •

March 16, 2011 at 12:32:24

Report •

March 16, 2011 at 18:41:47
Thanks for the website: i'm currently stuck on one bit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim StrBody As String
With ThisWorkbook.Sheets("SendRow")
StrBody = "Dear Gamers," & "

" & _
"The servers listed below will be unavailable for scheduled maintenance." & "

" & _
"The games will be unavailable from:" & "

" & _
"Schedule:" & "
" & _
"DATE: 19 March 2011" & "
" & _
"TIME: 8:00 PM EDT TO 2:00 AM EDT" & "

" & "

End With

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then

Ash.Range("A1:O100").AutoFilter Field:=1, Criteria1:=cell.Value

With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Maintenace"
.HTMLBody = StrBody & RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

This is the script im using right now, however its not exactly how i want it.
How can i not display all the cells in the row of table im sending?
is it possible to only show some of the cells of that row?

Also is it possible to add some more StrBody after the table in the body of the email?

Report •
Related Solutions

Ask Question