Import form results

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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).
Code:
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top