Hi,

I receive email messages with user data which I need to paste into an
Excel worksheet. If possible I want to copy the data portion of the
email message to the clipboard and have an Excel macro convert it and
paste the different fields into a worksheet.

The text copied into clipboard have this format (fieldname | data
separator is <space>:<space>) :

Forename : John
Family name : Doe
Address : Sunset blvd.
City : San Fransisco

The worksheet columns are as follows:

Name | Address | City

The data from the clipboard should be processed and put into the
worksheet's current row like this:

Doe, John | Sunset blvd. | San Fransisco

Thanks a lot if someone can help us with this

regards

Geir

Geir,
See if this does what you need. Assumes you have copied from the email and a
Text format is available:

Private Sub CommandButton2_Click()
Dim Clip As DataObject
Dim Info As Variant

On Error GoTo Handler

Const TextFormat As Long = 1
Set Clip = New DataObject

With Clip
.GetFromClipboard
If .GetFormat(TextFormat) = True Then
Info = Split(.GetText(TextFormat), " : ")
Else
MsgBox "No text available"
Exit Sub
End If
End With

With ActiveCell
.Value = Split(Info(2), vbCr)(0) & ", " & Split(Info(1), vbCr)(0)
.Offset(0, 1).Value = Split(Info(3), vbCr)(0)
.Offset(0, 2).Value = Info(4)
End With

Exit Sub
Handler:
MsgBox "Text not in correct structure:" & vbNewLine &
Clip.GetText(TextFormat)
End Sub

There is a recent thread on "microsoft.public.vb.general.discussion",
subject "Something like a Project_Activate event?" which may be useful, if
you wish the improve/extend on the above technique.

NickHK

"Geir Holmavatn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I receive email messages with user data which I need to paste into an
> Excel worksheet. If possible I want to copy the data portion of the
> email message to the clipboard and have an Excel macro convert it and
> paste the different fields into a worksheet.
>
> The text copied into clipboard have this format (fieldname | data
> separator is <space>:<space>) :
>
> Forename : John
> Family name : Doe
> Address : Sunset blvd.
> City : San Fransisco
>
> The worksheet columns are as follows:
>
> Name | Address | City
>
> The data from the clipboard should be processed and put into the
> worksheet's current row like this:
>
> Doe, John | Sunset blvd. | San Fransisco
>
> Thanks a lot if someone can help us with this
>
> regards
>
> Geir

On Jan 3, 8:19 am, "NickHK" <TungChe...@Invalid.com> wrote:
> Geir,
> See if this does what you need. Assumes you have copied from the email and a
> Text format is available:
>
> Private Sub CommandButton2_Click()
> Dim Clip As DataObject

The DataObject is accessed by setting a reference in your VBA project
to the Microsoft Forms 2.0 object library. (Use Tools..References)

There's a useful article here:

http://www.cpearson.com/excel/clipboar.htm

HTH,

Mike