Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Import form results

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •