Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

If there is one than one address in a cell, are the addresses separated by a
comma?
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

Yes, they are separated by a comma with no spaces.

"Gary''s Student" wrote:

If there is one than one address in a cell, are the addresses separated by a
comma?
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

If your data is in column A, then this:

Sub mailum()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, ",") 0 Then
s = Split(v, ",")
For j = LBound(s) To UBound(s)
Cells(k, 2).Value = s(j)
k = k + 1
Next
Else
Cells(k, 2).Value = v
k = k + 1
End If
Next
End Sub

will fill column B with single email addresses
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

Works great! Thank you!

"Gary''s Student" wrote:

If your data is in column A, then this:

Sub mailum()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, ",") 0 Then
s = Split(v, ",")
For j = LBound(s) To UBound(s)
Cells(k, 2).Value = s(j)
k = k + 1
Next
Else
Cells(k, 2).Value = v
k = k + 1
End If
Next
End Sub

will fill column B with single email addresses
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

It's working fine and everything, but only for that document. If I exit
Excel and open it again, the macro is gone. How can I have it available every
time I open Excel?

"Gary''s Student" wrote:

If your data is in column A, then this:

Sub mailum()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, ",") 0 Then
s = Split(v, ",")
For j = LBound(s) To UBound(s)
Cells(k, 2).Value = s(j)
k = k + 1
Next
Else
Cells(k, 2).Value = v
k = k + 1
End If
Next
End Sub

will fill column B with single email addresses
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

If you need the macro only with the workbook, then save the workbook and the
macro will be saved with it.

If you need the macro as a general tool, put the macro in an otherwise empty
workbook and save the workbook as an addin (.xla). Then with one click you
can always include the macro whenever you bring up Excel.
--
Gary''s Student - gsnu200828


"dleo" wrote:


It's working fine and everything, but only for that document. If I exit
Excel and open it again, the macro is gone. How can I have it available every
time I open Excel?

"Gary''s Student" wrote:

If your data is in column A, then this:

Sub mailum()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, ",") 0 Then
s = Split(v, ",")
For j = LBound(s) To UBound(s)
Cells(k, 2).Value = s(j)
k = k + 1
Next
Else
Cells(k, 2).Value = v
k = k + 1
End If
Next
End Sub

will fill column B with single email addresses
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

I want to have this as a general tool. Let me make sure I am doing this
right. I go to Tools -- Macro -- Visual Basic Editor. Insert -- Module.
Paste in the code. File -- Close and Return to Excel. Then let's say if I
want to make the shortcut key Ctrl+e, I could just go to Tools -- Macro --
Macros and click on options and set it there.

I cannot figure out how to save as an addin. I use Save As, but there is no
option in there. Any advice?

Thanks.

"Gary''s Student" wrote:

If you need the macro only with the workbook, then save the workbook and the
macro will be saved with it.

If you need the macro as a general tool, put the macro in an otherwise empty
workbook and save the workbook as an addin (.xla). Then with one click you
can always include the macro whenever you bring up Excel.
--
Gary''s Student - gsnu200828


"dleo" wrote:


It's working fine and everything, but only for that document. If I exit
Excel and open it again, the macro is gone. How can I have it available every
time I open Excel?

"Gary''s Student" wrote:

If your data is in column A, then this:

Sub mailum()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, ",") 0 Then
s = Split(v, ",")
For j = LBound(s) To UBound(s)
Cells(k, 2).Value = s(j)
k = k + 1
Next
Else
Cells(k, 2).Value = v
k = k + 1
End If
Next
End Sub

will fill column B with single email addresses
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!

FileSave AsSave asType

Scroll way down to bottom of list to find Excel Add-in(*.xla).

Ot Excel 2007 Add-in(*.xlam)


Gord Dibben MS Excel MVP

On Wed, 21 Jan 2009 14:59:02 -0800, dleo
wrote:


I want to have this as a general tool. Let me make sure I am doing this
right. I go to Tools -- Macro -- Visual Basic Editor. Insert -- Module.
Paste in the code. File -- Close and Return to Excel. Then let's say if I
want to make the shortcut key Ctrl+e, I could just go to Tools -- Macro --
Macros and click on options and set it there.

I cannot figure out how to save as an addin. I use Save As, but there is no
option in there. Any advice?

Thanks.

"Gary''s Student" wrote:

If you need the macro only with the workbook, then save the workbook and the
macro will be saved with it.

If you need the macro as a general tool, put the macro in an otherwise empty
workbook and save the workbook as an addin (.xla). Then with one click you
can always include the macro whenever you bring up Excel.
--
Gary''s Student - gsnu200828


"dleo" wrote:


It's working fine and everything, but only for that document. If I exit
Excel and open it again, the macro is gone. How can I have it available every
time I open Excel?

"Gary''s Student" wrote:

If your data is in column A, then this:

Sub mailum()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, ",") 0 Then
s = Split(v, ",")
For j = LBound(s) To UBound(s)
Cells(k, 2).Value = s(j)
k = k + 1
Next
Else
Cells(k, 2).Value = v
k = k + 1
End If
Next
End Sub

will fill column B with single email addresses
--
Gary''s Student - gsnu200828


"dleo" wrote:

Every month I send out an e-newsletter to the people in my database. To get
the addresses I simply export their information into an Excel file,
copy/paste into Word, then copy/paste them into the e-mail.

In the exporting process however, multiple e-mails may be put into the same
Excel cell (ex. "). That means every
month I have to visually look down a long list to make sure each e-mail is on
its own line.

Is there some sort of macro or script I could create to eliminate this step?
Something along the lines of "If there is a "," move the rest of the cell
contents into a new cell below".

I am new to macros/scripts, so any help would be appreciated.

Thanks!