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

MrMan&Fam · 01-31-2005, 04:06 PM

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?

Ron de Bruin · 01-31-2005, 05:06 PM

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(xlCellTypeConstants)
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" <[email protected]> wrote in message news:[email protected]...
> 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?

Gord Dibben · 01-31-2005, 05:06 PM

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
<[email protected]> 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?

MrMan&Fam · 01-31-2005, 05:06 PM

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(xlCellTypeConstants)
> 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" <[email protected]> wrote in message news:[email protected]...
> > 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?
>
>
>

Ron de Bruin · 01-31-2005, 05:06 PM

Here we Go

Only working if you use Outlook this example

Open a new workbook
Insert>Module 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" <[email protected]> wrote in message news:[email protected]...
> 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(xlCellTypeConstants)
>> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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?
>>
>>
>>

Ken Wright · 01-31-2005, 05:06 PM

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(xlCellTypeConstants)
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(xlCellTypeConstants)

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>

Ron de Bruin · 01-31-2005, 06:06 PM

Your English is much better Ken<g>

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

"Ken Wright" <[email protected]> wrote in message news:[email protected]...
> 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(xlCellTypeConstants)
> 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(xlCellTypeConstants)
>
> 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>
>
>

Ken Wright · 01-31-2005, 06:06 PM

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>

MrMan&Fam · 01-31-2005, 07:06 PM

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
> Insert>Module 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" <[email protected]> wrote in message news:[email protected]...
> > 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(xlCellTypeConstants)
> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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?
> >>
> >>
> >>
>
>
>

Ron de Bruin · 01-31-2005, 07:06 PM

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" <[email protected]> wrote in message news:[email protected]...
>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
>> Insert>Module 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" <[email protected]> wrote in message news:[email protected]...
>> > 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(xlCellTypeConstants)
>> >> 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" <[email protected]> wrote in message news:[email protected]...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>

MrMan&Fam · 01-31-2005, 07:06 PM

You made it easy for even ME to do and I think I did it perfect (and I didn't
have a clue as to what I was doing). The only problem is that it is sending
to only the 1st e-mail address. The piece of e-mail has all the addresses,
but Outlook is only sending to the frst one. Personally, I'd rather have ONE
address per piece of e-mail (so that a column of 20 addresses will send 20
pieces of e-mail, each with only one e-mail address. Thanks.

"Ken Wright" wrote:

> 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(xlCellTypeConstants)
> 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(xlCellTypeConstants)
>
> 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>
>
>
>

MrMan&Fam · 01-31-2005, 07:06 PM

Each of my three e-mail addresses is a different account with a different
name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??

"Ron de Bruin" wrote:

> 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" <[email protected]> wrote in message news:[email protected]...
> >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
> >> Insert>Module 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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(xlCellTypeConstants)
> >> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >> > 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ron de Bruin · 02-01-2005, 11:06 AM

Hi

> Each of my three e-mail addresses is a different account with a different
> name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??

It is working, disable one account and send the mail,you will get one mail.
Enabled the other account and you will see you will receive a mail

I live in the Netherlands in Europe

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

"MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> Each of my three e-mail addresses is a different account with a different
> name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
>
> "Ron de Bruin" wrote:
>
>> 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" <[email protected]> wrote in message news:[email protected]...
>> >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
>> >> Insert>Module 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" <[email protected]> wrote in message news:[email protected]...
>> >> > 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(xlCellTypeConstants)
>> >> >> 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

MrMan&amp;Fam · 02-01-2005, 01:06 PM

You must be right. I found the 2nd of the 3 pieces of mail I sent myself
later that evening. Later THIS evening I want to run the macro on a subset of
the REAL list.

What about my question regarding sending the same document to only ONE
address at a time instead of the entire column? Among other things, some
e-mail programs look at mail with multiple "TO" addresses as SPAM but I also
don't want any of the recipients to know how many I'm sending out.

"Ron de Bruin" wrote:

> Hi
>
> > Each of my three e-mail addresses is a different account with a different
> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
>
> It is working, disable one account and send the mail,you will get one mail.
> Enabled the other account and you will see you will receive a mail
>
> I live in the Netherlands in Europe
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> > Each of my three e-mail addresses is a different account with a different
> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >
> > "Ron de Bruin" wrote:
> >
> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >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
> >> >> Insert>Module 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" <[email protected]> wrote in message news:[email protected]...
> >> >> > 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(xlCellTypeConstants)
> >> >> >> 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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ron de Bruin · 02-01-2005, 03:06 PM

See the link I posted or use
To for your own address and BCC for all other addresses

>> >> http://www.rondebruin.nl/mail/folder2/files.htm

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

"MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> You must be right. I found the 2nd of the 3 pieces of mail I sent myself
> later that evening. Later THIS evening I want to run the macro on a subset of
> the REAL list.
>
> What about my question regarding sending the same document to only ONE
> address at a time instead of the entire column? Among other things, some
> e-mail programs look at mail with multiple "TO" addresses as SPAM but I also
> don't want any of the recipients to know how many I'm sending out.
>
> "Ron de Bruin" wrote:
>
>> Hi
>>
>> > Each of my three e-mail addresses is a different account with a different
>> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
>>
>> It is working, disable one account and send the mail,you will get one mail.
>> Enabled the other account and you will see you will receive a mail
>>
>> I live in the Netherlands in Europe
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
>> > Each of my three e-mail addresses is a different account with a different
>> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> 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" <[email protected]> wrote in message news:[email protected]...
>> >> >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
>> >> >> Insert>Module 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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(xlCellTypeConstants)
>> >> >> >> 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" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > 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?
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

MrMan&amp;Fam · 02-01-2005, 04:08 PM

Following the procedure I used with the 1st file you sent, I did the same
with the second file (and I made a special spreadsheet with 3 columns. One
for name...) yet I can't get the macro to show up when I click
TOOLS-MACRO-MACRO in my special spreadsheet) so I can't run it. Why?

"Ron de Bruin" wrote:

> See the link I posted or use
> To for your own address and BCC for all other addresses
>
> >> >> http://www.rondebruin.nl/mail/folder2/files.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> > You must be right. I found the 2nd of the 3 pieces of mail I sent myself
> > later that evening. Later THIS evening I want to run the macro on a subset of
> > the REAL list.
> >
> > What about my question regarding sending the same document to only ONE
> > address at a time instead of the entire column? Among other things, some
> > e-mail programs look at mail with multiple "TO" addresses as SPAM but I also
> > don't want any of the recipients to know how many I'm sending out.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi
> >>
> >> > Each of my three e-mail addresses is a different account with a different
> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >>
> >> It is working, disable one account and send the mail,you will get one mail.
> >> Enabled the other account and you will see you will receive a mail
> >>
> >> I live in the Netherlands in Europe
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> >> > Each of my three e-mail addresses is a different account with a different
> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >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
> >> >> >> Insert>Module 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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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(xlCellTypeConstants)
> >> >> >> >> 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" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > 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?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

MrMan&amp;Fam · 02-01-2005, 08:06 PM

Whenever I close up the spreadsheet with the macro I made with your help,
when I go back to the spreadsheet again the macro is gone. I have to cut and
paste all over again. Why?

"Ron de Bruin" wrote:

> See the link I posted or use
> To for your own address and BCC for all other addresses
>
> >> >> http://www.rondebruin.nl/mail/folder2/files.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> > You must be right. I found the 2nd of the 3 pieces of mail I sent myself
> > later that evening. Later THIS evening I want to run the macro on a subset of
> > the REAL list.
> >
> > What about my question regarding sending the same document to only ONE
> > address at a time instead of the entire column? Among other things, some
> > e-mail programs look at mail with multiple "TO" addresses as SPAM but I also
> > don't want any of the recipients to know how many I'm sending out.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi
> >>
> >> > Each of my three e-mail addresses is a different account with a different
> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >>
> >> It is working, disable one account and send the mail,you will get one mail.
> >> Enabled the other account and you will see you will receive a mail
> >>
> >> I live in the Netherlands in Europe
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> >> > Each of my three e-mail addresses is a different account with a different
> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >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
> >> >> >> Insert>Module 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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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(xlCellTypeConstants)
> >> >> >> >> 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" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > 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?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Don Guillett · 02-01-2005, 10:06 PM

You did save the workbook afterwards...

--
Don Guillett
SalesAid Software
[email protected]
"MrMan&Fam" <[email protected]> wrote in message
news:[email protected]...
> Whenever I close up the spreadsheet with the macro I made with your help,
> when I go back to the spreadsheet again the macro is gone. I have to cut
and
> paste all over again. Why?
>
> "Ron de Bruin" wrote:
>
> > See the link I posted or use
> > To for your own address and BCC for all other addresses
> >
> > >> >> http://www.rondebruin.nl/mail/folder2/files.htm
> >
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> >
> > "MrMan&Fam" <[email protected]> wrote in message
news:[email protected]...
> > > You must be right. I found the 2nd of the 3 pieces of mail I sent
myself
> > > later that evening. Later THIS evening I want to run the macro on a
subset of
> > > the REAL list.
> > >
> > > What about my question regarding sending the same document to only ONE
> > > address at a time instead of the entire column? Among other things,
some
> > > e-mail programs look at mail with multiple "TO" addresses as SPAM but
I also
> > > don't want any of the recipients to know how many I'm sending out.
> > >
> > > "Ron de Bruin" wrote:
> > >
> > >> Hi
> > >>
> > >> > Each of my three e-mail addresses is a different account with a
different
> > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where
are you??
> > >>
> > >> It is working, disable one account and send the mail,you will get one
mail.
> > >> Enabled the other account and you will see you will receive a mail
> > >>
> > >> I live in the Netherlands in Europe
> > >>
> > >> --
> > >> Regards Ron de Bruin
> > >> http://www.rondebruin.nl
> > >>
> > >>
> > >>
> > >> "MrMan&Fam" <[email protected]> wrote in message
news:[email protected]...
> > >> > Each of my three e-mail addresses is a different account with a
different
> > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where
are you??
> > >> >
> > >> > "Ron de Bruin" wrote:
> > >> >
> > >> >> 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" <[email protected]> wrote in message
news:[email protected]...
> > >> >> >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
> > >> >> >> Insert>Module 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" <[email protected]> wrote in
message
> > >> >> >> news:[email protected]...
> > >> >> >> > 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(xlCellTypeConstants)
> > >> >> >> >> 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" <[email protected]> wrote in
message
> > >> >> >> >> news:[email protected]...
> > >> >> >> >> > 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?
> > >> >> >> >>
> > >> >> >> >>
> > >> >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

MrMan&amp;Fam · 02-01-2005, 11:06 PM

This might sound stupid to you but in fact, I did NOT. The spreadsheet has
138 e-mail addresses and I did not want to send to all at once. So, I would
clear contents from a group of e-mail address cells leaving only the group I
wanted to mail to. Once I ran the macro, I closed the file DELIBERATELY
without saving. This way, when I opened it again, ALL the addresses would be
back on. Then I would clear contents of another group of cells in order to
send to a different set of addresses, etc. SO, unless you save the
spreadsheet, the macro doesn't get saved? Thanks.

"Don Guillett" wrote:

> You did save the workbook afterwards...
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "MrMan&Fam" <[email protected]> wrote in message
> news:[email protected]...
> > Whenever I close up the spreadsheet with the macro I made with your help,
> > when I go back to the spreadsheet again the macro is gone. I have to cut
> and
> > paste all over again. Why?
> >
> > "Ron de Bruin" wrote:
> >
> > > See the link I posted or use
> > > To for your own address and BCC for all other addresses
> > >
> > > >> >> http://www.rondebruin.nl/mail/folder2/files.htm
> > >
> > >
> > > --
> > > Regards Ron de Bruin
> > > http://www.rondebruin.nl
> > >
> > >
> > >
> > > "MrMan&Fam" <[email protected]> wrote in message
> news:[email protected]...
> > > > You must be right. I found the 2nd of the 3 pieces of mail I sent
> myself
> > > > later that evening. Later THIS evening I want to run the macro on a
> subset of
> > > > the REAL list.
> > > >
> > > > What about my question regarding sending the same document to only ONE
> > > > address at a time instead of the entire column? Among other things,
> some
> > > > e-mail programs look at mail with multiple "TO" addresses as SPAM but
> I also
> > > > don't want any of the recipients to know how many I'm sending out.
> > > >
> > > > "Ron de Bruin" wrote:
> > > >
> > > >> Hi
> > > >>
> > > >> > Each of my three e-mail addresses is a different account with a
> different
> > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where
> are you??
> > > >>
> > > >> It is working, disable one account and send the mail,you will get one
> mail.
> > > >> Enabled the other account and you will see you will receive a mail
> > > >>
> > > >> I live in the Netherlands in Europe
> > > >>
> > > >> --
> > > >> Regards Ron de Bruin
> > > >> http://www.rondebruin.nl
> > > >>
> > > >>
> > > >>
> > > >> "MrMan&Fam" <[email protected]> wrote in message
> news:[email protected]...
> > > >> > Each of my three e-mail addresses is a different account with a
> different
> > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where
> are you??
> > > >> >
> > > >> > "Ron de Bruin" wrote:
> > > >> >
> > > >> >> 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" <[email protected]> wrote in message
> news:[email protected]...
> > > >> >> >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
> > > >> >> >> Insert>Module 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" <[email protected]> wrote in
> message
> > > >> >> >> news:[email protected]...
> > > >> >> >> > 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(xlCellTypeConstants)
> > > >> >> >> >> 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" <[email protected]> wrote in
> message
> > > >> >> >> >> news:[email protected]...
> > > >> >> >> >> > 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?
> > > >> >> >> >>
> > > >> >> >> >>
> > > >> >> >> >>
> > > >> >> >>
> > > >> >> >>
> > > >> >> >>
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>

Don Guillett · 02-02-2005, 11:06 AM

I think you've got it!! Create your macro>save the workbook>run your macro
and close without saving...

--
Don Guillett
SalesAid Software
[email protected]
"MrMan&Fam" <[email protected]> wrote in message
news:[email protected]...
> This might sound stupid to you but in fact, I did NOT. The spreadsheet has
> 138 e-mail addresses and I did not want to send to all at once. So, I
would
> clear contents from a group of e-mail address cells leaving only the group
I
> wanted to mail to. Once I ran the macro, I closed the file DELIBERATELY
> without saving. This way, when I opened it again, ALL the addresses would
be
> back on. Then I would clear contents of another group of cells in order to
> send to a different set of addresses, etc. SO, unless you save the
> spreadsheet, the macro doesn't get saved? Thanks.
>
> "Don Guillett" wrote:
>
> > You did save the workbook afterwards...
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "MrMan&Fam" <[email protected]> wrote in message
> > news:[email protected]...
> > > Whenever I close up the spreadsheet with the macro I made with your
help,
> > > when I go back to the spreadsheet again the macro is gone. I have to
cut
> > and
> > > paste all over again. Why?
> > >
> > > "Ron de Bruin" wrote:
> > >
> > > > See the link I posted or use
> > > > To for your own address and BCC for all other addresses
> > > >
> > > > >> >> http://www.rondebruin.nl/mail/folder2/files.htm
> > > >
> > > >
> > > > --
> > > > Regards Ron de Bruin
> > > > http://www.rondebruin.nl
> > > >
> > > >
> > > >
> > > > "MrMan&Fam" <[email protected]> wrote in message
> > news:[email protected]...
> > > > > You must be right. I found the 2nd of the 3 pieces of mail I sent
> > myself
> > > > > later that evening. Later THIS evening I want to run the macro on
a
> > subset of
> > > > > the REAL list.
> > > > >
> > > > > What about my question regarding sending the same document to only
ONE
> > > > > address at a time instead of the entire column? Among other
things,
> > some
> > > > > e-mail programs look at mail with multiple "TO" addresses as SPAM
but
> > I also
> > > > > don't want any of the recipients to know how many I'm sending out.
> > > > >
> > > > > "Ron de Bruin" wrote:
> > > > >
> > > > >> Hi
> > > > >>
> > > > >> > Each of my three e-mail addresses is a different account with a
> > different
> > > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM.
Where
> > are you??
> > > > >>
> > > > >> It is working, disable one account and send the mail,you will get
one
> > mail.
> > > > >> Enabled the other account and you will see you will receive a
mail
> > > > >>
> > > > >> I live in the Netherlands in Europe
> > > > >>
> > > > >> --
> > > > >> Regards Ron de Bruin
> > > > >> http://www.rondebruin.nl
> > > > >>
> > > > >>
> > > > >>
> > > > >> "MrMan&Fam" <[email protected]> wrote in message
> > news:[email protected]...
> > > > >> > Each of my three e-mail addresses is a different account with a
> > different
> > > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM.
Where
> > are you??
> > > > >> >
> > > > >> > "Ron de Bruin" wrote:
> > > > >> >
> > > > >> >> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > > >> >> >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
> > > > >> >> >> Insert>Module 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" <[email protected]> wrote in
> > message
> > > > >> >> >> news:[email protected]...
> > > > >> >> >> > 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(xlCellTypeConstants)
> > > > >> >> >> >> 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" <[email protected]> wrote
in
> > message
> > > > >> >> >> >>
news:[email protected]...
> > > > >> >> >> >> > 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?
> > > > >> >> >> >>
> > > > >> >> >> >>
> > > > >> >> >> >>
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >>
> > > > >> >>
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >

MrMan&amp;Fam · 02-02-2005, 12:06 PM

Yes! That's the solution! I would not have figured that out without your
assistance. It got so that I cut and pasted the macro (with MY specifics)
onto a Word file so I could cut and paste it into the VBE each time! When I
read Ken Wright's instructions
again (which are VERY clear), he tells me to save the spreadsheet!! Oh
well!! Thanks for all your help and the help of all you gentlemen. The macro
is AWESOME!

"Don Guillett" wrote:

> I think you've got it!! Create your macro>save the workbook>run your macro
> and close without saving...
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "MrMan&Fam" <[email protected]> wrote in message
> news:[email protected]...
> > This might sound stupid to you but in fact, I did NOT. The spreadsheet has
> > 138 e-mail addresses and I did not want to send to all at once. So, I
> would
> > clear contents from a group of e-mail address cells leaving only the group
> I
> > wanted to mail to. Once I ran the macro, I closed the file DELIBERATELY
> > without saving. This way, when I opened it again, ALL the addresses would
> be
> > back on. Then I would clear contents of another group of cells in order to
> > send to a different set of addresses, etc. SO, unless you save the
> > spreadsheet, the macro doesn't get saved? Thanks.
> >
> > "Don Guillett" wrote:
> >
> > > You did save the workbook afterwards...
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "MrMan&Fam" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Whenever I close up the spreadsheet with the macro I made with your
> help,
> > > > when I go back to the spreadsheet again the macro is gone. I have to
> cut
> > > and
> > > > paste all over again. Why?
> > > >
> > > > "Ron de Bruin" wrote:
> > > >
> > > > > See the link I posted or use
> > > > > To for your own address and BCC for all other addresses
> > > > >
> > > > > >> >> http://www.rondebruin.nl/mail/folder2/files.htm
> > > > >
> > > > >
> > > > > --
> > > > > Regards Ron de Bruin
> > > > > http://www.rondebruin.nl
> > > > >
> > > > >
> > > > >
> > > > > "MrMan&Fam" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > > > You must be right. I found the 2nd of the 3 pieces of mail I sent
> > > myself
> > > > > > later that evening. Later THIS evening I want to run the macro on
> a
> > > subset of
> > > > > > the REAL list.
> > > > > >
> > > > > > What about my question regarding sending the same document to only
> ONE
> > > > > > address at a time instead of the entire column? Among other
> things,
> > > some
> > > > > > e-mail programs look at mail with multiple "TO" addresses as SPAM
> but
> > > I also
> > > > > > don't want any of the recipients to know how many I'm sending out.
> > > > > >
> > > > > > "Ron de Bruin" wrote:
> > > > > >
> > > > > >> Hi
> > > > > >>
> > > > > >> > Each of my three e-mail addresses is a different account with a
> > > different
> > > > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM.
> Where
> > > are you??
> > > > > >>
> > > > > >> It is working, disable one account and send the mail,you will get
> one
> > > mail.
> > > > > >> Enabled the other account and you will see you will receive a
> mail
> > > > > >>
> > > > > >> I live in the Netherlands in Europe
> > > > > >>
> > > > > >> --
> > > > > >> Regards Ron de Bruin
> > > > > >> http://www.rondebruin.nl
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >> "MrMan&Fam" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > > >> > Each of my three e-mail addresses is a different account with a
> > > different
> > > > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM.
> Where
> > > are you??
> > > > > >> >
> > > > > >> > "Ron de Bruin" wrote:
> > > > > >> >
> > > > > >> >> 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" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > > > >> >> >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
> > > > > >> >> >> Insert>Module 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" <[email protected]> wrote in
> > > message
> > > > > >> >> >> news:[email protected]...
> > > > > >> >> >> > 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(xlCellTypeConstants)
> > > > > >> >> >> >> 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" <[email protected]> wrote
> in
> > > message
> > > > > >> >> >> >>
> news:[email protected]...
> > > > > >> >> >> >> > 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?
> > > > > >> >> >> >>
> > > > > >> >> >> >>
> > > > > >> >> >> >>
> > > > > >> >> >>
> > > > > >> >> >>
> > > > > >> >> >>
> > > > > >> >>
> > > > > >> >>
> > > > > >> >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Don Guillett · 02-02-2005, 02:06 PM

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"MrMan&Fam" <[email protected]> wrote in message
news:[email protected]...
> Yes! That's the solution! I would not have figured that out without your
> assistance. It got so that I cut and pasted the macro (with MY specifics)
> onto a Word file so I could cut and paste it into the VBE each time! When
I
> read Ken Wright's instructions
> again (which are VERY clear), he tells me to save the spreadsheet!! Oh
> well!! Thanks for all your help and the help of all you gentlemen. The
macro
> is AWESOME!
>
> "Don Guillett" wrote:
>
> > I think you've got it!! Create your macro>save the workbook>run your
macro
> > and close without saving...
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "MrMan&Fam" <[email protected]> wrote in message
> > news:[email protected]...
> > > This might sound stupid to you but in fact, I did NOT. The spreadsheet
has
> > > 138 e-mail addresses and I did not want to send to all at once. So, I
> > would
> > > clear contents from a group of e-mail address cells leaving only the
group
> > I
> > > wanted to mail to. Once I ran the macro, I closed the file
DELIBERATELY
> > > without saving. This way, when I opened it again, ALL the addresses
would
> > be
> > > back on. Then I would clear contents of another group of cells in
order to
> > > send to a different set of addresses, etc. SO, unless you save the
> > > spreadsheet, the macro doesn't get saved? Thanks.
> > >
> > > "Don Guillett" wrote:
> > >
> > > > You did save the workbook afterwards...
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > [email protected]
> > > > "MrMan&Fam" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Whenever I close up the spreadsheet with the macro I made with
your
> > help,
> > > > > when I go back to the spreadsheet again the macro is gone. I have
to
> > cut
> > > > and
> > > > > paste all over again. Why?
> > > > >
> > > > > "Ron de Bruin" wrote:
> > > > >
> > > > > > See the link I posted or use
> > > > > > To for your own address and BCC for all other addresses
> > > > > >
> > > > > > >> >> http://www.rondebruin.nl/mail/folder2/files.htm
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Regards Ron de Bruin
> > > > > > http://www.rondebruin.nl
> > > > > >
> > > > > >
> > > > > >
> > > > > > "MrMan&Fam" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > > > You must be right. I found the 2nd of the 3 pieces of mail I
sent
> > > > myself
> > > > > > > later that evening. Later THIS evening I want to run the macro
on
> > a
> > > > subset of
> > > > > > > the REAL list.
> > > > > > >
> > > > > > > What about my question regarding sending the same document to
only
> > ONE
> > > > > > > address at a time instead of the entire column? Among other
> > things,
> > > > some
> > > > > > > e-mail programs look at mail with multiple "TO" addresses as
SPAM
> > but
> > > > I also
> > > > > > > don't want any of the recipients to know how many I'm sending
out.
> > > > > > >
> > > > > > > "Ron de Bruin" wrote:
> > > > > > >
> > > > > > >> Hi
> > > > > > >>
> > > > > > >> > Each of my three e-mail addresses is a different account
with a
> > > > different
> > > > > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM.
> > Where
> > > > are you??
> > > > > > >>
> > > > > > >> It is working, disable one account and send the mail,you will
get
> > one
> > > > mail.
> > > > > > >> Enabled the other account and you will see you will receive a
> > mail
> > > > > > >>
> > > > > > >> I live in the Netherlands in Europe
> > > > > > >>
> > > > > > >> --
> > > > > > >> Regards Ron de Bruin
> > > > > > >> http://www.rondebruin.nl
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >> "MrMan&Fam" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > > >> > Each of my three e-mail addresses is a different account
with a
> > > > different
> > > > > > >> > name. Thanks for your help. I'm in New York. It is 5:40 PM.
> > Where
> > > > are you??
> > > > > > >> >
> > > > > > >> > "Ron de Bruin" wrote:
> > > > > > >> >
> > > > > > >> >> 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" <[email protected]> wrote in
> > message
> > > > news:[email protected]...
> > > > > > >> >> >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
> > > > > > >> >> >> Insert>Module 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" <[email protected]> wrote
in
> > > > message
> > > > > > >> >> >>
news:[email protected]...
> > > > > > >> >> >> > 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(xlCellTypeConstants)
> > > > > > >> >> >> >> 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" <[email protected]>
wrote
> > in
> > > > message
> > > > > > >> >> >> >>
> > news:[email protected]...
> > > > > > >> >> >> >> > 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?
> > > > > > >> >> >> >>
> > > > > > >> >> >> >>
> > > > > > >> >> >> >>
> > > > > > >> >> >>
> > > > > > >> >> >>
> > > > > > >> >> >>
> > > > > > >> >>
> > > > > > >> >>
> > > > > > >> >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

MrMan&Fam · 02-03-2005, 12:06 PM

When I try the second macro (i.e., the one that will send one email with 1
address, I get the error "User defined type not defined" and this line is
highlighted: "Dim Outapp as Outlook.Application". Any ideas? P.S. What does
"Dim" mean? Thanks.

"Ron de Bruin" wrote:

> See the link I posted or use
> To for your own address and BCC for all other addresses
>
> >> >> http://www.rondebruin.nl/mail/folder2/files.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> > You must be right. I found the 2nd of the 3 pieces of mail I sent myself
> > later that evening. Later THIS evening I want to run the macro on a subset of
> > the REAL list.
> >
> > What about my question regarding sending the same document to only ONE
> > address at a time instead of the entire column? Among other things, some
> > e-mail programs look at mail with multiple "TO" addresses as SPAM but I also
> > don't want any of the recipients to know how many I'm sending out.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi
> >>
> >> > Each of my three e-mail addresses is a different account with a different
> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >>
> >> It is working, disable one account and send the mail,you will get one mail.
> >> Enabled the other account and you will see you will receive a mail
> >>
> >> I live in the Netherlands in Europe
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> >> > Each of my three e-mail addresses is a different account with a different
> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >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
> >> >> >> Insert>Module 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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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(xlCellTypeConstants)
> >> >> >> >> 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" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > 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?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ron de Bruin · 02-03-2005, 02:06 PM

Hi MrMan&Fam

You can read this on the webpage

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number

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

"MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
> When I try the second macro (i.e., the one that will send one email with 1
> address, I get the error "User defined type not defined" and this line is
> highlighted: "Dim Outapp as Outlook.Application". Any ideas? P.S. What does
> "Dim" mean? Thanks.
>
> "Ron de Bruin" wrote:
>
>> See the link I posted or use
>> To for your own address and BCC for all other addresses
>>
>> >> >> http://www.rondebruin.nl/mail/folder2/files.htm
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
>> > You must be right. I found the 2nd of the 3 pieces of mail I sent myself
>> > later that evening. Later THIS evening I want to run the macro on a subset of
>> > the REAL list.
>> >
>> > What about my question regarding sending the same document to only ONE
>> > address at a time instead of the entire column? Among other things, some
>> > e-mail programs look at mail with multiple "TO" addresses as SPAM but I also
>> > don't want any of the recipients to know how many I'm sending out.
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi
>> >>
>> >> > Each of my three e-mail addresses is a different account with a different
>> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
>> >>
>> >> It is working, disable one account and send the mail,you will get one mail.
>> >> Enabled the other account and you will see you will receive a mail
>> >>
>> >> I live in the Netherlands in Europe
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "MrMan&Fam" <[email protected]> wrote in message news:[email protected]...
>> >> > Each of my three e-mail addresses is a different account with a different
>> >> > name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you??
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >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
>> >> >> >> Insert>Module 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" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > 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(xlCellTypeConstants)
>> >> >> >> >> 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" <[email protected]> wrote in message
>> >> >> >> >> news:[email protected]...
>> >> >> >> >> > 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?
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

milos · 07-19-2006, 04:40 AM

Hi there, I'm using your code you posted here. And it works when tha data in
column C are plain e-mail adresses. But problem is that my adresses in column
C are "=concatenate(A1;B1)", so they are result of a formula. What should I
change in the code to make it work? I'm not very familiar with VBA.
Thanks.
Milos

Ron de Bruin pÃ*Å¡e:

> 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(xlCellTypeConstants)
> 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" <[email protected]> wrote in message news:[email protected]...
> > 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?
>
>
>

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions