> Hi all!
> I have a little problem here and i hope that someone can help me with it.
> Currently, I am receiving orders in the form of emails. These emails
follow
> the same format and i have been extracting the information i need, such as
> the item number, name of buyer, address, etc manually through a copy and
> paste process and putting it into excel, I would like to inquire how can i
> write a program in VBA to extract these fields automatically and putting
> them into excel? I do know a little about the excel object library but i
am
> totally clueless about the outlook library.

> Thanks in advance,
> Kai

> Chuenkai
> A little while back I wrote a routine to handle orders.  The code is
> attached below.  It is looking for attachments in a certain form - but
> dilligent cut and paste should fix that [grin]
> Andrew Ollivier

> Public Myolapp As Variant
> Public I As Long
> Public J As Long
> Public MyExt As String
> Public MyExts As String
> Public MyFile As String
> Public MyPath As String

> Private Sub Application_NewMail()

> ' Identifies incoming mail that has an attachment that matches our target
> type
> ' if so, asks, and translates it to new format and save

> ' Change the following two constants as required to get things working
right
> ' First is simply a list of valid extensions to trigger reformating action

> MyExts = ".txt.TXT.001"

> ' Second is the path for re-formatted files to go to

> MyPath = "C:\TEMP"

> ' Preliminary setup to view new mail

> Set Myolapp = CreateObject("Outlook.Application")
> Set MyNameSpace = Myolapp.GetNamespace("MAPI")
> Set Myolapp.ActiveExplorer.CurrentFolder = _
>     MyNameSpace.GetDefaultFolder(olFolderInbox)

> ' Identify unread mail and deal with it

> With Myolapp.ActiveExplorer.CurrentFolder
>   For I = 1 To .Items.Count
>     If .Items(I).UnRead = True Then
>       For J = 1 To .Items(I).Attachments.Count
>         MyFile = .Items(I).Attachments(J).DisplayName
>         MyExt = Right(MyFile, 4)
>         MyFile = Left(MyFile, Len(MyFile) - 4)
>         If InStr(1, MyExts, MyExt, vbTextCompare) > 0 Then
>           MyFile = InputBox("Shipping file detected" & vbCrLf & _
>                      "Press OK to reformat '" & MyFile & MyExt & "',
Cancel
> to skip" _
>                      & vbCrLf & "(Change attachment name if necessary)", _
>                      .Items(I).Subject, MyFile)
>           If MyFile <> "" Then
>             MyFile = MyPath & "\" & MyFile & "._B4"
>             .Items(I).Attachments(J).SaveAsFile MyFile
>             ' Reformat MyFile - was a call but it is not relevant in this
> case
>           End If
>           .Items(I).FlagStatus = olFlagComplete
>           .Items(I).FlagRequest = MyFile
>           .Items(I).FlagDueBy = Now()
>           .Items(I).UnRead = False
>         End If
>       Next J
>     End If
>   Next I
> End With

> End Sub
> Sub Reformat(target As String)
> Dim I As Long
> Dim J As Long
> Dim Wanted As Boolean
> Dim DateCol As Long
> Dim MyRec As String
> Dim MyFields(100) As String

> ' This would have been easier in Excel but it cannot refrain from
> re-formating quotes
> ' so - lets just do it the hard way ;-)
> ' Just read the file line by line, break it out into fields, and then
> rewrite

> Open target For Input As #1
> Open Left(target, Len(target) - 4) & ".txt" For Output As #2
> Line Input #1, MyRec
> While Not EOF(1)
>   ' it is easier and less bug-prone to split into fields than try to be
> clever
>   J = 1
>   MyRec = UCase(MyRec)
>   I = InStr(1, MyRec, ",")
>   While I > 0
>     MyFields(J) = Left(MyRec, I - 1)
>     MyRec = Mid(MyRec, I + 1)
>     J = J + 1
>     I = InStr(1, MyRec, ",")
>   Wend
>   MyFields(J) = MyRec

>   ' This is the section that makes the requested changes

>   MyFields(2) = Left(MyFields(2), Len(MyFields(2)) - 1) & "0001"""
>   MyFields(4) = """R00" & MyFields(4) & """"
>   MyFields(5) = """S045" & MyFields(5) & """"
>   ' Set the column for date as the last column - J.
>   ' Why? to protect from possible commas in the text fields ;-)
>   MyFields(J) = Left(MyFields(J), Len(MyFields(J)) - 3) & "20" &
> Right(MyFields(J), 3)

>   ' then we put it all back together and write it out
>   ' unless it is a record that we don't want (Wanted = False)
>   Wanted = True
>   If Wanted Then
>     For I = 1 To J - 1
>       Print #2, MyFields(I); ",";
>     Next I
>     Print #2, MyFields(J)
>   End If
>   Line Input #1, MyRec
> Wend
> Close #1
> Close #2

> End Sub

> > Hi all!
> > I have a little problem here and i hope that someone can help me with
it.
> > Currently, I am receiving orders in the form of emails. These emails
> follow
> > the same format and i have been extracting the information i need, such
as
> > the item number, name of buyer, address, etc manually through a copy and
> > paste process and putting it into excel, I would like to inquire how can
i
> > write a program in VBA to extract these fields automatically and putting
> > them into excel? I do know a little about the excel object library but i
> am
> > totally clueless about the outlook library.

> > Thanks in advance,
> > Kai

> hi!
> Thanks for providing me with some directions on how to go about parsing
the
> information. One quick question. what is the best method to save the text
in
> the email as? and how do i parse the information from there?

> Thanks,
> Kai


> > Chuenkai
> > A little while back I wrote a routine to handle orders.  The code is
> > attached below.  It is looking for attachments in a certain form - but
> > dilligent cut and paste should fix that [grin]
> > Andrew Ollivier

> > Public Myolapp As Variant
> > Public I As Long
> > Public J As Long
> > Public MyExt As String
> > Public MyExts As String
> > Public MyFile As String
> > Public MyPath As String

> > Private Sub Application_NewMail()

> > ' Identifies incoming mail that has an attachment that matches our
target
> > type
> > ' if so, asks, and translates it to new format and save

> > ' Change the following two constants as required to get things working
> right
> > ' First is simply a list of valid extensions to trigger reformating
action

> > MyExts = ".txt.TXT.001"

> > ' Second is the path for re-formatted files to go to

> > MyPath = "C:\TEMP"

> > ' Preliminary setup to view new mail

> > Set Myolapp = CreateObject("Outlook.Application")
> > Set MyNameSpace = Myolapp.GetNamespace("MAPI")
> > Set Myolapp.ActiveExplorer.CurrentFolder = _
> >     MyNameSpace.GetDefaultFolder(olFolderInbox)

> > ' Identify unread mail and deal with it

> > With Myolapp.ActiveExplorer.CurrentFolder
> >   For I = 1 To .Items.Count
> >     If .Items(I).UnRead = True Then
> >       For J = 1 To .Items(I).Attachments.Count
> >         MyFile = .Items(I).Attachments(J).DisplayName
> >         MyExt = Right(MyFile, 4)
> >         MyFile = Left(MyFile, Len(MyFile) - 4)
> >         If InStr(1, MyExts, MyExt, vbTextCompare) > 0 Then
> >           MyFile = InputBox("Shipping file detected" & vbCrLf & _
> >                      "Press OK to reformat '" & MyFile & MyExt & "',
> Cancel
> > to skip" _
> >                      & vbCrLf & "(Change attachment name if necessary)",
_
> >                      .Items(I).Subject, MyFile)
> >           If MyFile <> "" Then
> >             MyFile = MyPath & "\" & MyFile & "._B4"
> >             .Items(I).Attachments(J).SaveAsFile MyFile
> >             ' Reformat MyFile - was a call but it is not relevant in
this
> > case
> >           End If
> >           .Items(I).FlagStatus = olFlagComplete
> >           .Items(I).FlagRequest = MyFile
> >           .Items(I).FlagDueBy = Now()
> >           .Items(I).UnRead = False
> >         End If
> >       Next J
> >     End If
> >   Next I
> > End With

> > End Sub
> > Sub Reformat(target As String)
> > Dim I As Long
> > Dim J As Long
> > Dim Wanted As Boolean
> > Dim DateCol As Long
> > Dim MyRec As String
> > Dim MyFields(100) As String

> > ' This would have been easier in Excel but it cannot refrain from
> > re-formating quotes
> > ' so - lets just do it the hard way ;-)
> > ' Just read the file line by line, break it out into fields, and then
> > rewrite

> > Open target For Input As #1
> > Open Left(target, Len(target) - 4) & ".txt" For Output As #2
> > Line Input #1, MyRec
> > While Not EOF(1)
> >   ' it is easier and less bug-prone to split into fields than try to be
> > clever
> >   J = 1
> >   MyRec = UCase(MyRec)
> >   I = InStr(1, MyRec, ",")
> >   While I > 0
> >     MyFields(J) = Left(MyRec, I - 1)
> >     MyRec = Mid(MyRec, I + 1)
> >     J = J + 1
> >     I = InStr(1, MyRec, ",")
> >   Wend
> >   MyFields(J) = MyRec

> >   ' This is the section that makes the requested changes

> >   MyFields(2) = Left(MyFields(2), Len(MyFields(2)) - 1) & "0001"""
> >   MyFields(4) = """R00" & MyFields(4) & """"
> >   MyFields(5) = """S045" & MyFields(5) & """"
> >   ' Set the column for date as the last column - J.
> >   ' Why? to protect from possible commas in the text fields ;-)
> >   MyFields(J) = Left(MyFields(J), Len(MyFields(J)) - 3) & "20" &
> > Right(MyFields(J), 3)

> >   ' then we put it all back together and write it out
> >   ' unless it is a record that we don't want (Wanted = False)
> >   Wanted = True
> >   If Wanted Then
> >     For I = 1 To J - 1
> >       Print #2, MyFields(I); ",";
> >     Next I
> >     Print #2, MyFields(J)
> >   End If
> >   Line Input #1, MyRec
> > Wend
> > Close #1
> > Close #2

> > End Sub

> > > Hi all!
> > > I have a little problem here and i hope that someone can help me with
> it.
> > > Currently, I am receiving orders in the form of emails. These emails
> > follow
> > > the same format and i have been extracting the information i need,
such
> as
> > > the item number, name of buyer, address, etc manually through a copy
and
> > > paste process and putting it into excel, I would like to inquire how
can
> i
> > > write a program in VBA to extract these fields automatically and
putting
> > > them into excel? I do know a little about the excel object library but
i
> > am
> > > totally clueless about the outlook library.

> > > Thanks in advance,
> > > Kai

> Kai
> Sorry about the delay - I got a little overloaded last week.
> It rather depends on the format of the email.  I assume that the text is
> really text and not HTML[grin]
> If so, and you want an external file - use the 'OPEN .. for output'
> statement (there is an example in the code I sent last time, and just
write
> a file - it, too will be text.

> If you're happy for it to be 'just' text, create an extension .txt and it
> can be opened with notepad or Wordpad
> If you want Excel to parse it, separate any fields with commas, and use an
> extension .csv (Comma Separated Values)
> Excel will then break it into columns and rows e.g.

> Name,Amount,Cost
> J Bloggs,$100,$10
> J Dow,$23.27,$7

> Would become a nice little excel table if put in a .csv.
> The code I previously sent could have had a type of .csv and Excel would
> have loved it.
> HTH
> Andrew

> > hi!
> > Thanks for providing me with some directions on how to go about parsing
> the
> > information. One quick question. what is the best method to save the
text
> in
> > the email as? and how do i parse the information from there?

> > Thanks,
> > Kai


> > > Chuenkai
> > > A little while back I wrote a routine to handle orders.  The code is
> > > attached below.  It is looking for attachments in a certain form - but
> > > dilligent cut and paste should fix that [grin]
> > > Andrew Ollivier

> > > Public Myolapp As Variant
> > > Public I As Long
> > > Public J As Long
> > > Public MyExt As String
> > > Public MyExts As String
> > > Public MyFile As String
> > > Public MyPath As String

> > > Private Sub Application_NewMail()

> > > ' Identifies incoming mail that has an attachment that matches our
> target
> > > type
> > > ' if so, asks, and translates it to new format and save

> > > ' Change the following two constants as required to get things working
> > right
> > > ' First is simply a list of valid extensions to trigger reformating
> action

> > > MyExts = ".txt.TXT.001"

> > > ' Second is the path for re-formatted files to go to

> > > MyPath = "C:\TEMP"

> > > ' Preliminary setup to view new mail

> > > Set Myolapp = CreateObject("Outlook.Application")
> > > Set MyNameSpace = Myolapp.GetNamespace("MAPI")
> > > Set Myolapp.ActiveExplorer.CurrentFolder = _
> > >     MyNameSpace.GetDefaultFolder(olFolderInbox)

> > > ' Identify unread mail and deal with it

> > > With Myolapp.ActiveExplorer.CurrentFolder
> > >   For I = 1 To .Items.Count
> > >     If .Items(I).UnRead = True Then
> > >       For J = 1 To .Items(I).Attachments.Count
> > >         MyFile = .Items(I).Attachments(J).DisplayName
> > >         MyExt = Right(MyFile, 4)
> > >         MyFile = Left(MyFile, Len(MyFile) - 4)
> > >         If InStr(1, MyExts, MyExt, vbTextCompare) > 0 Then
> > >           MyFile = InputBox("Shipping file detected" & vbCrLf & _
> > >                      "Press OK to reformat '" & MyFile & MyExt & "',
> > Cancel
> > > to skip" _
> > >                      & vbCrLf & "(Change attachment name if
necessary)",
> _
> > >                      .Items(I).Subject, MyFile)
> > >           If MyFile <> "" Then
> > >             MyFile = MyPath & "\" & MyFile & "._B4"
> > >             .Items(I).Attachments(J).SaveAsFile MyFile
> > >             ' Reformat MyFile - was a call but it is not relevant in
> this
> > > case
> > >           End If
> > >           .Items(I).FlagStatus = olFlagComplete
> > >           .Items(I).FlagRequest = MyFile
> > >           .Items(I).FlagDueBy = Now()
> > >           .Items(I).UnRead = False
> > >         End If
> > >       Next J
> > >     End If
> > >   Next I
> > > End With

> > > End Sub
> > > Sub Reformat(target As String)
> > > Dim I As Long
> > > Dim J As Long
> > > Dim Wanted As Boolean
> > > Dim DateCol As Long
> > > Dim MyRec As String
> > > Dim MyFields(100) As String

> > > ' This would have been easier in Excel but it cannot refrain from
> > > re-formating quotes
> > > ' so - lets just do it the hard way ;-)
> > > ' Just read the file line by line, break it out into fields, and then
> > > rewrite

> > > Open target For Input As #1
> > > Open Left(target, Len(target) - 4) & ".txt" For Output As #2
> > > Line Input #1, MyRec
> > > While Not EOF(1)
> > >   ' it is easier and less bug-prone to split into fields than try to
be
> > > clever
> > >   J = 1
> > >   MyRec = UCase(MyRec)
> > >   I = InStr(1, MyRec, ",")
> > >   While I > 0
> > >     MyFields(J) = Left(MyRec, I - 1)
> > >     MyRec = Mid(MyRec, I + 1)
> > >     J = J + 1
> > >     I = InStr(1, MyRec, ",")
> > >   Wend
> > >   MyFields(J) = MyRec

> > >   ' This is the section that makes the requested changes

> > >   MyFields(2) = Left(MyFields(2), Len(MyFields(2)) - 1) & "0001"""
> > >   MyFields(4) = """R00" & MyFields(4) & """"
> > >   MyFields(5) = """S045" & MyFields(5) & """"
> > >   ' Set the column for date as the last column - J.
> > >   ' Why? to protect from possible commas in the text fields ;-)
> > >   MyFields(J) = Left(MyFields(J), Len(MyFields(J)) - 3) & "20" &
> > > Right(MyFields(J), 3)

> > >   ' then we put it all back together and write it out
> > >   ' unless it is a record that we don't want (Wanted = False)
> > >   Wanted = True
> > >   If Wanted Then
> > >     For I = 1 To J - 1
> > >       Print #2, MyFields(I); ",";
> > >     Next I
> > >     Print #2, MyFields(J)
> > >   End If
> > >   Line Input #1, MyRec
> > > Wend
> > > Close #1
> > > Close #2

> > > End Sub

> > > > Hi all!
> > > > I have a little problem here and i hope that someone can help me
with
> > it.
> > > > Currently, I am receiving orders in the form of emails. These emails
> > > follow
> > > > the same format and i have been extracting the information i need,
> such
> > as
> > > > the item number, name of buyer, address, etc manually through a copy
> and
> > > > paste process and putting it into excel, I would like to inquire how
> can
> > i
> > > > write a program in VBA to extract these fields automatically and
> putting
> > > > them into excel? I do know a little about the excel object library
but
> i
> > > am
> > > > totally clueless about the outlook library.

> > > > Thanks in advance,
> > > > Kai