Nice one Dave. But I think Annie wants a list that she can copy.
Annie
If a user has entered addresses in comments it is hard to imagine how she
may have entered it - and this would be depend on the solution. The first
macro will show a message with the text in each comment in turn.
Sub ShowCmtText()
Dim cmt
Dim sMsg As String
For Each cmt In ActiveSheet.Comments
sMsg = cmt.Text
MsgBox sMsg
Next cmt
End Sub
If the Names and addresses are shown in separate lines then the following
sub will copy the details to sheet2 (Change this to suit). If the address
details are separated by a comma you will have to change the code as detailed
in the sub below
Sub CommentTextToSheet2()
' sheet with comments variables
Dim cmt
Dim sMsg As String
Dim colCount As Integer
Dim iLen As Integer
Dim iIndex As Integer
Dim client As Variant
' Destination sheet variables
Dim wksDest As Worksheet
Dim lNextrow As Long
Dim rngTemp As Range
Set wksDest = Sheets("Sheet2")
For Each cmt In ActiveSheet.Comments
colCount = 1
sMsg = cmt.Text
iLen = Len(sMsg)
For iIndex = 1 To iLen
If Mid(sMsg, iIndex, 1) = vbLf Then
colCount = colCount + 1
End If
Next iIndex
client = Split(sMsg, vbLf)
'or if comma separated use - put an apostophe before the line above
' and remove the apostrophe on the next line
' client = split(smasg,","
With Sheets("Sheet2")
lNextrow = .Range("A1").CurrentRegion.Rows.Count + 1
Set rngTemp = .Range("A" & lNextrow & ":" & Cells(lNextrow,
colCount).Address)
rngTemp = client
End With
Next cmt
End Sub
If the details are separated by spaces, this will not do - 21 The Hi Road
would shown in four columns. If this is the case, Each comment will have to
be edited & a hard return made between each field. Address1, Address2 and so
on.
Once you have the list on a separate sheet you can think about importing the
data int Outlook - I have not done this I'm afraid.
Regards
Peter Atherton
