Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export delimitted text body to .txt file or Access (Outlook 2K)

    I'm not too bad with MS Access, but I have a scripting question in Outlook. I have a web site which email's registration information to a distribution list. The registration info is stored in two lines of the body of the message and is delimitted with commas.

    Sample of the body of a message:

    "PlayerFirstName","PlayerLastName","PhoneNumber"," R1"
    "Dave","Mack","1112223333","Y"

    If this data were taken from the message and stored into a .txt file, My problem would be over. Best case would be to export it to MS access and append the data to a table. If I can figure out how to write the code to get at the text, the rest would be pretty easy. A friend has shared code to deal with attachments, but nothing with the text directly in the body. Sure, I can select the text and paste it for now, but what I really want to do is have Outlook keep checking the InBox for a message with a particular subject line and send the data over to Access. If I can just get the strRegistrationData variable loaded with the text. I'd really be happy. Actually, if we could just read line 2, it would even be better (but I'm sure I can do it via access).

    Thank you,

    Dave

    Here is where I am now

    Sub ReadBody()
    Dim myOlApp As Outlook.Application
    Dim myFolder As Folders
    Dim myItem As MailItem
    Dim myNameSpace As Outlook.NameSpace
    Dim strRegistrationData As String
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myItem = myFolder.Items("Data posted to form 1 of http://revolutionsoccerclub.com/Online _ Registration Form.htm")

    With myItem
    strRegistrationData = .Body
    'read the delimited text "strRegistrationData" _
    'in the body into a variable _
    'pass the string off to Access...
    .Delete 'delete the message from Outlook
    End With

    End Sub

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Export delimitted text body to .txt file or Access (Outlook 2K)

    I don't think you can get into the Items collection with the subject line; you could use the Find method of the Items collection for that. Depending on how much stuff you keep in your Inbox, you also could do it this way:

    <pre>Sub ReadBody()
    Dim myOlApp As Outlook.Application
    Dim myFolder As MAPIFolder
    Dim myItem As MailItem
    Dim myNameSpace As Outlook.NameSpace
    Dim strRegistrationData As String
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    For Each myItem In myFolder.Items
    If InStr(1, myItem.Subject, "Online_Registration Form.htm", _
    vbTextCompare) Then

    strRegistrationData = myItem.Body
    'your code here
    End If
    Next

    'destroy your objects here
    End Sub</pre>

    Hope this helps.

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export delimitted text body to .txt file or Access (Outlook 2K)

    Thank you. You have greatly increased my understanding. "MAPIFolder," that's the first big clue. Iterating through the items collection is a real good idea. I had it .delete after finding the first and then had to run it again to find other instances. Using the NewMail event seems to be the way to go for me. I just added my error handler and exit routine and I'm up and running!

    Thank you VERY much.

    Dave

Posting Permissions

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