Import form results

[bobmc]

I have an online form, the results of which are submitted as email to the recipient. The email that shows up to the recipient looks like this:

Name: Joe Smith
Address: 123 Cherry Lane
City: Los Angeles
Submit: Submit

Here's my question. How do I import this text into Excel so the field names ("Name", "Address", etc.) show up in, say, column A and the field entries ("Joe Smith", "123 Cherry Lane", etc.) show up in column B?

I am successful in this endeavor if I copy the email text, paste it into notepad (or any other text editor), save it as a .txt file, then import to Excel, indicating a colon as the delimiter. Is there a more direct way to do this?

Any help is greatly appreciated!

[Al Chara]

I was hoping someone else would post, because I'm a little embarassed of my following code. I assume there is a lot easier to import an email to excel (but hey, I don't know how to do it). So you can try the following code (no laughing at my procedure).

Public Const SW_RESTORE As Long = 9&
Public Const GW_CHILD As Long = 5&
Public Const GW_HWNDNEXT As Long = 2&

Declare Function GetDesktopWindow& Lib "user32" ()
Declare Function GetWindow& Lib "user32" (ByVal hWnd&, ByVal wCmd&)
Public Declare Function GetWindowText& Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd&, ByVal lpString$, ByVal cch&)
Declare Function ShowWindow& Lib "user32" (ByVal hWnd&, ByVal nCmdShow&)
Public Declare Function SetForegroundWindow& Lib "user32" (ByVal hWnd&)
Sub Main()
Const BaseCaption As String = " - Message"
Dim AppWindow&, sTemp$, StartTime, CycleTime

StartTime = Now
AppWindow = GetWindow(GetDesktopWindow(), GW_CHILD)
Do While CycleTime - StartTime < TimeValue("00:00:01")
sTemp = String$(180, False)
Call GetWindowText(AppWindow, sTemp, 179)
If InStr(sTemp, BaseCaption) Then
ActivateWindow AppWindow
Exit Sub
End If
AppWindow = GetWindow(AppWindow, GW_HWNDNEXT)
CycleTime = Now
Loop
MsgBox "The window is not open"
End Sub
Private Sub ActivateWindow(ByVal AppWindow&)
Call ShowWindow(AppWindow, SW_RESTORE)
Call SetForegroundWindow(AppWindow)
End Sub
Sub ExtractEmail()
Call Main
SendKeys "%(E)", True
SendKeys "L", True
SendKeys "%(E)", True
SendKeys "C", True
AppActivate "Microsoft Excel"
Workbooks("Book2.xls").Activate
Worksheets("Sheet1").Select
Range("a1").Select
ActiveSheet.Paste
Columns("A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=":", FieldInfo:=Array(1, 1)

End Sub

Run ExtractEmail
Change Const BaseCaption As String = " - Message"
So that " - Message" is a text string that appears in the window ID. I chose " - Message" because I am using Outlook and it is in every email's window ID.

[bobmc]

Thanks for the reply, Al...

Two quick questions about this code.

1. Where exactly should I place it? Does it go in one of the sheets, or does it go in "This Workbook"?

2. What do you mean by "Run" ExtractEmail?

(I know basically nothing about VB code.)

Thanks!

[Al Chara]

On 2002-04-17 23:50, bobmc wrote:
Two quick questions about this code.

1. Where exactly should I place it? Does it go in one of the sheets, or does it go in "This Workbook"?

----It goes in a standard module

2. What do you mean by "Run" ExtractEmail?

----The macro that makes it all happen is ExtractEmail. So when you want to extract an email, make sure the email is open and then run the ExtractEmail macro (Alt+F8, in excel).

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-18 06:45 ]

[bobmc]

OK...

I see how the "run" function works now. Once I ran it I got some errors...might be because I'm a little confused about the "Window ID" you speak of. Is that the very top app window heading, i.e.(I also use Outlook and mine says "Inbox - Microsoft Outlook".)

Please advise. And thanks for the help!

[Al Chara]

Yes, for Outlook it would say "Inbox - Microsoft Outlook". But my code looks for open messages. Since I do not know exactly what the window ID will be, I used a search string " - Message" That string appears in the Window ID of every Outlook Message, ie Message Subject "- Message"

Since you are using Outlook, do not change my code at all. It should run for you.