VBA to send email to group

[brunette]

Good morning,

Using Excel 2003, my goal is to use excel to send a selection of text from a cell as the body of an email using Outlook, to a group of email addresses, located in column I.

I've got the whole thing to work perfectly except for getting it to send to more than one person in the "to" field.

How do I change the code so that it send to every email address located in Column I?

piece of code currently (thanks to Ron de Bruin)

With OutMail
.To = [needs to be every address in column i]
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send
End With

Thanks for your help

 

[brunette]

Re: [VBA] Send email to group - [SOLVED]

Hi everyone,

Just to let you know that I've managed to find a solution to my problem. My VBA knowledge is a little limited but I got it to do what I wanted it to, so I'm happy.

What I was after was to take the contents of a particular cell (in this case B3) and copy that into the body of an email and email that to the group of email addresses in Column I.

Sub Error_Alert()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

Dim strbody As String
For Each cell In Range("B5:B5")
strbody = strbody & cell.Value & vbNewLine
Next

On Error GoTo cleanup
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "ALERT - Application Error"
.Body = strbody
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

The only "issue" (albeit minor and easily fixed by hiding the column) was that in order for it to send ONE email to ALL the people in the distribution list, I had to put all the email addresses in one cell, separated by a ";", as opposed to being able to put each email address in its own cell. In my case, it's no big deal because the list of people doesn't change and if it should, I can easily just do a quick "replace". I hid the column so that it didn't look ugly and, voilá, problem solved!

Basically, the aim of this whole project was to create a method whereby our application operators could alert a particular group of people that there's a problem or error with an application.

Our company has it set up so that you can email a company email address and your message is received as a text message on your mobile phone, using Outlook. So now my operators have a way of letting the bosses know that something is wrong and no more "oh but I wasn't told so nothing got fixed..." :D

I'm pretty chuffed at my meagre accomplishment

 

[Ruddles]

Could you do something like this:-

for each cell in range([I]whatever[/I])
  .to=.to & ";" & cell.value
next cell
.to = mid(.to,2) [COLOR=green]' get rid of the first semicolon[/COLOR]