Scripts / Macros in Word and Excel

[dleo]

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. "(e-mail address removed),[email protected]"). 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!

 

[Gary''s Student]

If there is one than one address in a cell, are the addresses separated by a
comma?

 

[dleo]

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

 

[Gary''s Student]

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

 

[dleo]

Works great! Thank you!

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

 

[dleo]

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]

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.

 

[dleo]

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.

 

[Gord Dibben]

File>Save As>Save 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

 

[dleo]

Alright, so it's saved as an add-in now. Now what? I don't understand how
saving that allows me to have access to that macro every time I open Excel.
Should I be saving it a certain name or in a certain location? I just named
it the same as the macro name in all caps and put it in the MS Office library.

 

[Gord Dibben]

Now you go to Tools>Add-ins and enable the add-in by checkmarking it.

With the sheet of doubled-up email addresses active, just hit CTRL + e and
the macro will run and clean them up.

Note: macros in add-ins will not show up in the Tools>Macro>Macros dialog
so must be run via shortcut or assigned to a button or typed into the above
dialog refedit box.

To avoid any potential conflict I would not give the add-in and the macro
the same name.

Easier at this point to change the macro name to something else.


Gord

 

[dleo]

Alright, it's working now. Thank you so much for your help. I realized that
when saving the doc as an add-in, the bottom selection is called "Excel 2007
Add-in." That one didn't work, so I used the choice called "Microsoft Office
Excel Add-in." Took me a long time to realize that.

Thanks again!