Hi Robert,
I could send you a copy of the excel workbook what i am trying to
create, if you
provide me with your email address. Also, could send you the Mail
Message from
which i extract data & create the text file for Reading into Excel
Sheet.
Here's the code:
'====================================
Option Explicit
Sub ProcessAll()
Dim rngSubject
Dim retVal
Set rngSubject = shtSetup.Range("A14")
Do While rngSubject.Value < ""
retVal = ProcessOutlookMessages(Trim(rngSubject.Value), _
Trim(rngSubject.Offset(0, 1).Value),
_
Trim(rngSubject.Offset(0, 2).Value))
If retVal = 0 Then
rngSubject.Offset(0, 3).Value = rngSubject.Offset(0, 3).Value +
retVal
Else
'negative return value means error
MsgBox "Could not process mails"
Exit Sub
End If
Set rngSubject = rngSubject.Offset(1, 0)
Loop
End Sub
' Extract information from mail items with defined subject and
attachment
Function ProcessOutlookMessages(MailSubject As String, DataSheet As
String, _
ProcessedFolder As String)
Dim olApp As Outlook.Application
Dim fInbox As MAPIFolder
Dim olFolderArchive As Object
Dim olInboxCollection As Object
Dim olInboxItem As Object
Dim TempPath As String
Dim itemCount, i, n, iCount
'temp files saved here
TempPath = ThisWorkbook.Path & "\TempFile.txt"
'If this is the first time calling the sub - get a ref to Outlook
' (must be already running)
If olApp Is Nothing Then
Set olApp = GetOutlook()
End If
'should have it by now....
If olApp Is Nothing Then
ProcessOutlookMessages = -1
Exit Function
End If
'Get the inbox folder
Set fInbox =
olApp.GetNamespace("MAPI").GetDefaultFolder(olFold erInbox)
Set olInboxCollection = fInbox.Items
itemCount = olInboxCollection.Count
iCount = 0
For n = itemCount To 1 Step -1
Set olInboxItem = olInboxCollection(n)
'check it's an email and not something else
If TypeName(olInboxItem) = "MailItem" Then
If StrComp(Trim(olInboxItem.Subject), MailSubject) = 0 Then
'Look for archive folder and create if doesn't exist.
Set olFolderArchive = EnsureInboxFolder(fInbox,
ProcessedFolder)
olInboxItem.SaveAs TempPath, olTXT
ProcessFile TempPath, DataSheet
'move the mail to the archive folder
olInboxItem.Move olFolderArchive
iCount = iCount + 1
End If
End If
Next n
ProcessOutlookMessages = iCount
Exit Function
haveError:
If Err < 0 Then MsgBox "Error:" & vbCrLf & Err.Description
End Function
Sub ProcessFile(TempFilePath As String, WorkSheetName As String)
'#######################
'code goes here to process the temp file and extract the contents. NEED
CODE FOR THIS SUB...
'#######################
End Sub
Function GetOutlook() As Object
Dim olApp As Object
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
MsgBox "Outlook is not running: please open the application first"
End If
Set GetOutlook = olApp
End Function
Function EnsureInboxFolder(oInbox, FolderName) As Object
Dim oFold As Object
'Look for archive folder and create if doesn't exist.
On Error Resume Next 'ignore error
Set oFold = oInbox.Folders(FolderName)
If Err.Number < 0 Then Err.Clear
If oFold Is Nothing Then
Set oFold = oInbox.Folders.Add(FolderName, olFolderInbox)
End If
Set EnsureInboxFolder = oFold
End Function
'=================================
Also, Here's the Source code of the MS Outlook Mail Message in html.
Copy this
into Frontpage & then SaveAs Modem Order.Msg file.
'===================================
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<HTML<HEAD
<META content="MSHTML 6.00.2800.1515" name=GENERATOR</HEAD
<BODY
<DIV<FONT face=Arial size=2
<DIV class=style1 align=center<STRONG
<DIV class=style1 align=center<FONT color=#ff0000New Modem Escalation
Form</FONT</DIV
<DIV class=style1 align=center </DIV</STRONG<FONT face=Arial
size=2</FONT</DIV</FONT</DIV
<DIV id=Layer1
<TABLE width=476 align=center border=4
<TBODY
<TR
<TD width=277CSA NAME: </TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005James
Roy</SPAN</FONT</TD</TR
<TR
<TDTeam Leader's name:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005Cyrus
Broacha</SPAN</FONT</TD</TR
<TR
<TDDate:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-2809200529/09/2005</SPAN</FONT</TD</TR
<TR
<TDTime:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-2809200512:00
pm</SPAN</FONT</TD</TR
<TR
<TDCustomer Forename:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005raj</SPAN</FONT</TD</TR
<TR
<TDCustomer Surname:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005jutsi</SPAN</FONT</TD</TR
<TR
<TDCustomer Email Address:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005<A
</A</SPAN</FONT</TD</TR
<TR
<TDAddress 1 (House Number/Name):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005abc</SPAN</FONT</TD</TR
<TR
<TDAddress 2 (Road):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005def</SPAN</FONT</TD</TR
<TR
<TDAddress 3 (Town/City):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005jkl</SPAN</FONT</TD</TR
<TR
<TDAddress 4 (Country):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005mno</SPAN</FONT</TD</TR
<TR
<TDPostcode:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005be 23
of1</SPAN</FONT</TD</TR
<TR
<TDFSBB Number:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005FSBB1234</SPAN</FONT</TD</TR</TBODY</TABLE</DIV
<DIV<FONT face=Arial size=2</FONT </DIV
<DIV<FONT face=Arial size=2</FONT </DIV
<DIV id=Layer2
<TABLE height=51 cellSpacing=0 cellPadding=0
<TBODY
<TR
<TD width=711 bgColor=#cccccc height=42
<H3 align=centerThe following part has to be pasted in VV as a
part of
the resolution before the modem escalation. Answer Applicable
questions in
Y/N Format. </H3
<DIV
<TABLE width=661 align=center border=4
<TBODY
<TR
<TD width=645Modem light status & Error No :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDPowercycled the modem :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDReinstalled the modem software :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005n</SPAN</FONT</TD</TR
<TR
<TDSwapped the USB ports :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDTried other USB devices in the same port :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDUnplugged all other USB devices and then solely plugged
the USB
modem :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDMade a manual connection and selected the USB modem (if
applicable) :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR
<TR
<TDChecked for the BB setup (if applicable) :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR
<TR
<TDConnected the modem to the master socket (if applicable)
:</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR
<TR
<TDSwapped the filters (if applicable)</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2n</FONT</SPAN</TD</TR
<TR
<TDEscalated for a woosh, If yes, does it suggest a Faulty
Equipment Issue:</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2n</FONT</SPAN</TD</TR
<TR
<TDDid a BT Escalation?, If yes, then does the Fault Report
Suggest
a Modem Fault:</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2n</FONT</SPAN</TD</TR
<TR
<TDChecked for Power Management System: </TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR
<TR
<TDChecked Device Manager for USB chipset (IF status says
"VIA" or
"SYS", cmr needs to upgrade chipset)</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR
<TR
<TDDisabled Firewall :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR
<TR
<TDIf all the above steps are satisfied only then a modem
replacement can be sent for. :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial
size=2y</FONT</SPAN</TD</TR</TBODY</TABLE</DIV<SPAN
class=538401720-28092005<FONT face=Arial
size=2</FONT</SPAN</TD</TR</TBODY</TABLE</DIV<BR
<DIV<FONT face=Arial size=2</FONT </DIV</BODY</HTML
'=========================================
Now, Here's the text file which gets generated!
'=========================================
'=========================================
Hope This Helps!
Rgds,
Sifar