E-mail to every e-mail address in an Excel column?

Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?

Hi

Try this one with the e-mail addresses in "Sheet1" column C
Visit also my site for more examples

Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?

See Ron de Bruin's site for his SendMail add-in.

Excellent utility for your purpose.

http://www.rondebruin.nl/sendmail.htm


Gord Dibben Excel MVP

On Mon, 31 Jan 2005 11:51:07 -0800, MrMan&Fam
wrote:

Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?

Thanks so much for the prompt response. Your work looks brilliant but it's
wasted on me. I don't know anything about programming or programming in
Excel. I don't even know what to do with your program. However, if this is
what it takes, I guess there's no easy way. Thanks, anyway.

"Ron de Bruin" wrote:

Hi

Try this one with the e-mail addresses in "Sheet1" column C
Visit also my site for more examples

Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?

Here we Go

Only working if you use Outlook this example

Open a new workbook
InsertModule from the menu bar
Paste the sub in there
Change the path/filename to the word Doc in the code
Alt-Q to go back to Excel

Now in the C column of "Sheet1" fill in your addresses
Save the file

If you do Alt-F8 you get a list of your macro's
Select "Mail_workbook_Outlook" and press Run

You can also use a cell in "Sheet1" with the path/filename to the word Doc
if you want.(post back if you need help with that)

For testing you can change .Send to .display in the code
You can see how it look like and press the Send button manual then

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Thanks so much for the prompt response. Your work looks brilliant but it's
wasted on me. I don't know anything about programming or programming in
Excel. I don't even know what to do with your program. However, if this is
what it takes, I guess there's no easy way. Thanks, anyway.

"Ron de Bruin" wrote:

Hi

Try this one with the e-mail addresses in "Sheet1" column C
Visit also my site for more examples

Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?

LOL - it looks a lot scarier than it really is - try this for a step by step
guide using Ron's code.

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for your workbook's name, and when you find it you may
need to click on the + to expand it. Within that you should see the
following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to
what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in Ron's code starting at the
Sub Mail_workbook_Outlook()
bit and finishing at the End Sub bit.


Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

The ranges in Ron's code will likely need changing to suit your data, eg in
the line that says

For Each cell In ThisWorkbook.Sheets("Sheet1")

you will need to either change the Sheet1 to the name of your sheet or vice
versa. Also, Ron's code assumes the addresses are in Col C on that sheet,
hence the line that says

.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)

so if it's not Col C then just change the C to whatever it actually is in
your sheet.

Then hit File / Close and return to Microsoft Excel and save the file. Now
just do Tools / Macro / Macros / Mail_workbook


You can stop at that point, but if for any reason you then want to get rid
of the macro, then simply do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question
re exporting, just hit no. Then hit File / Close and return to Microsoft
Excel and save the
file.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
snip

Your English is much better Keng

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ken Wright" wrote in message ...
LOL - it looks a lot scarier than it really is - try this for a step by step
guide using Ron's code.

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for your workbook's name, and when you find it you may
need to click on the + to expand it. Within that you should see the
following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to
what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in Ron's code starting at the
Sub Mail_workbook_Outlook()
bit and finishing at the End Sub bit.


Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

The ranges in Ron's code will likely need changing to suit your data, eg in
the line that says

For Each cell In ThisWorkbook.Sheets("Sheet1")

you will need to either change the Sheet1 to the name of your sheet or vice
versa. Also, Ron's code assumes the addresses are in Col C on that sheet,
hence the line that says

.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)

so if it's not Col C then just change the C to whatever it actually is in
your sheet.

Then hit File / Close and return to Microsoft Excel and save the file. Now
just do Tools / Macro / Macros / Mail_workbook


You can stop at that point, but if for any reason you then want to get rid
of the macro, then simply do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question
re exporting, just hit no. Then hit File / Close and return to Microsoft
Excel and save the
file.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
snip

LOL - But it's your code Ron, so hey, that's what teamwork is all about :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
snip

I can NOT believe it! Without knowing what the hell I was doing, I followed
your instructions and did (I believe) everything right. As a test, I created
a NEW column "C" with only 3 e-mail addresses (all mine), I edited the
attachment name, the "CC", all went well. I even got a message saying that
"the computer is sending mail. This could be a virus..." BUT, it is only
sending e-mail to the 1st address. It's listing all the other addresses in
the "TO" section but it is not sending to the other addresses. Only the 1st
one. Also, can I have it put only ONE ""TO" address and make a separate
e-mail for each address (not putting them all on the "TO" section). Thanks.
Great job!

"Ron de Bruin" wrote:

Here we Go

Only working if you use Outlook this example

Open a new workbook
InsertModule from the menu bar
Paste the sub in there
Change the path/filename to the word Doc in the code
Alt-Q to go back to Excel

Now in the C column of "Sheet1" fill in your addresses
Save the file

If you do Alt-F8 you get a list of your macro's
Select "Mail_workbook_Outlook" and press Run

You can also use a cell in "Sheet1" with the path/filename to the word Doc
if you want.(post back if you need help with that)

For testing you can change .Send to .display in the code
You can see how it look like and press the Send button manual then

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Thanks so much for the prompt response. Your work looks brilliant but it's
wasted on me. I don't know anything about programming or programming in
Excel. I don't even know what to do with your program. However, if this is
what it takes, I guess there's no easy way. Thanks, anyway.

"Ron de Bruin" wrote:

Hi

Try this one with the e-mail addresses in "Sheet1" column C
Visit also my site for more examples

Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?

Hi MrMan&Fam

it is only sending e-mail to the 1st address
That's because they all yours and you will receive them.

See this example on my website
http://www.rondebruin.nl/mail/folder2/files.htm

It is bed time for me now ( 23:31) but is if you need help post back and I will help
you tomorrow after work.



--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
I can NOT believe it! Without knowing what the hell I was doing, I followed
your instructions and did (I believe) everything right. As a test, I created
a NEW column "C" with only 3 e-mail addresses (all mine), I edited the
attachment name, the "CC", all went well. I even got a message saying that
"the computer is sending mail. This could be a virus..." BUT, it is only
sending e-mail to the 1st address. It's listing all the other addresses in
the "TO" section but it is not sending to the other addresses. Only the 1st
one. Also, can I have it put only ONE ""TO" address and make a separate
e-mail for each address (not putting them all on the "TO" section). Thanks.
Great job!

"Ron de Bruin" wrote:

Here we Go

Only working if you use Outlook this example

Open a new workbook
InsertModule from the menu bar
Paste the sub in there
Change the path/filename to the word Doc in the code
Alt-Q to go back to Excel

Now in the C column of "Sheet1" fill in your addresses
Save the file

If you do Alt-F8 you get a list of your macro's
Select "Mail_workbook_Outlook" and press Run

You can also use a cell in "Sheet1" with the path/filename to the word Doc
if you want.(post back if you need help with that)

For testing you can change .Send to .display in the code
You can see how it look like and press the Send button manual then

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Thanks so much for the prompt response. Your work looks brilliant but it's
wasted on me. I don't know anything about programming or programming in
Excel. I don't even know what to do with your program. However, if this is
what it takes, I guess there's no easy way. Thanks, anyway.

"Ron de Bruin" wrote:

Hi

Try this one with the e-mail addresses in "Sheet1" column C
Visit also my site for more examples

Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String

For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\test.doc")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"MrMan&Fam" wrote in message ...
Say I have an Excel spreadsheet with one column of e-mail addresses. I want
to send the same piece of e-mail (a WORD file) to each address. Is there an
easy way?