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

"Dave Peterson" wrote:

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


BWAnnie wrote:

I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

--

Dave Peterson