Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook Excel Solution (2000)

    Heres a scenario which im facing

    company B have lots of offices, over the UK , they want to coallate complaintts infrination from all of their offices
    up to head office.obvioulsy cant say who my client is , but typical of these type of people there IT infrastructure
    is pants and they wont spend any money.

    Firstly lacking is there not all on the same network and getting them all on the same network isnt possible, they wont
    spend money on geting access, but however they all have excel and they all have outlook, i hate using excel as a database but
    dont have much choice here.

    What i was thinking however is using outlook as a back end for my "database" and excel as a front end. what i am thinking is
    i have a form in excel for entering a new record, when the user hits saves via VBA the from sends an e-mail up to head office
    at head office, the person there has a message rule which moves the message to a specified folder in the outlook folders, then
    in the excel spreadhseet there, using VBA on open, it lists all the e-mails in that folder on a spreadsheet.

    Only thing is this complaints thing has 20 odd fields i can only see how i could get the subject heading of the mail listed on a excel spreadsheet
    how could i get detailed infomation ?

    Also another point is if the user wants to change a record how would i approach that ?

    or is this totally the wrong approach ?

    Chance

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    Just another idea...

    If the users are really that disparate with regards to their platforms, etc. I'd suggest a web based approach would be the easiest. I.e. set up a simple website with a page with a form on it which, on submit, writes a record into an access database. Frontpage has various widgets which automate all of this for you.

    If, however, you want users to be able to change records this opens up a whole bundle of new questions, such as which fields can they change? which user can change which record? do you want an audit trail of change?, etc. It might be easier for the user to simply submit a new record with intention of overwriting the old one and perhaps you should manage overwrite centrally.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    web based approach not <img src=/S/bash.gif border=0 alt=bash width=35 height=39> ] possilbe!

    interesting thought re managing changes centrally with the user resubmitting, still need to figure a way of getting the fields from the e-mail

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    What you need is a standard way of setting out all the different bits of information in the email. There are various recognised ways of doing this and the most fashionable at the moment is XML.

    The email in XML format would look something like the following:-

    <pre><complaint>
    <name>Fred Bloggs</name>
    <address>...</address>
    <description>
    My beer was warm.
    </description>
    </compliant>
    </pre>


    The tags, e.g. <complaint>, are completely arbitrary, I made them up. You can create any tag you like to suffice your needs. All you need to do is make sure your Excel VBA writes out the XML properly with the tags you choose and then at the other end you can throw the XML at any standard XML parser and have the information presented to you in a constant manner.

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    There running Office 2000 here, is it possible to use XML Tags in 2000 ?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    Yes, you can use them in any version. I just meant you should use these in the body of the mail. You don't have to use XML of course, you could just lay the body of the mails out in any way which you are comfortable with.

    Perhaps I misread your inital problem, I thought you wanted a standard way of setting out the fields in the body of the mail. But if your issue is simply how to read the body of the mail then you just use <pre>MailItem.Body</pre>

    which returns the body of the mail as a String.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Washington, USA
    Posts
    750
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    I too assumed the data would be in the body. Perhaps we should explicitly ask if custom fields are being planned. Boy, I'd shy away from that in this environment! I don't think one could get it to work; but even so, it would be a maintenance nightmare.

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    yeap the data would be in the body of the e-mail, what i was refering to was how would i get it to make sense in Excel,



    Dim Myfolder as folder
    Dim Myoutlook as outlook
    dim myitem as item
    Dim Mystr as string

    set outlook = blah blah blah
    set myfolder = outlook.folders(folder with all the e-mails in it)

    For each myitem in myfolder
    Mystr = myitem.body
    Activeworksheet.cells(x,1) = mystr
    x = x + 1
    next

    that's roughly would be my approach if i just had one bit of data in the body.
    but with the XML , how would i in my VBA code reconise each part of the XML ? so i can identify each field and place it accordingly, ive noticed in the references there is a microsoft XML componenent 3.

    would it be something like
    ?

    Dim Myfolder as folder
    Dim Myoutlook as outlook
    dim myitem as item
    Dim MyXML as XML

    set outlook = blah blah blah
    set myfolder = outlook.folders(folder with all the e-mails in it)

    For each myitem in myfolder
    MyXML= myitem.body
    Activeworksheet.cells(x,1) = MyXML("ComplainT")
    Activeworksheet.cells(x,2) = MyXML("date")
    Activeworksheet.cells(x,3) = MyXML("Address")




    x = x + 1
    next

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Washington, USA
    Posts
    750
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    Well I would have done string parsing because I'm not familiar with XML techniques. For grins I went to msdn.microsoft.com, then to the code center, and did a search on XML. There are some pretty cool samples starting on pg 2. I think I gotta get me some...

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    Yes, that's the right idea. You could parse the XML yourself using basic VBA string manipulation, or use one of the prebuilt parsers such as the one you mention (better). Again, using XML isn't necessary, but it's a nice, neat, recognised way of doing it and will be more easily supportable by others.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    Out of interest, why is the website approach such a no-go? It involves no coding and all the work happens on a single machine.

    The Outlook-Excel idea could work, but I think the distribution and support is likely to cause you quite a few headaches, not to mention the initial cost of coding, testing, etc.

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    going to look into XML a bit more before making a decision, but if it is that simple via a parser then it does seem a logical step.

    re why a web solution isnt possible, is partly down to who my client is and the data involved, secondly all there web stuff is handled by an outsourcing company, thirdly they got zero budget and lastly im only really doing VBA code and a few things for them , because i am bored out of my head in a Non IT assignment which the pay here daily doesnt even come to 2 hours of what i charge as a developer. got to be honest!

  13. #13
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    just out of interest as well , where do you reckong problems with support would occur ?

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    Well, it sounds like you're rolling out your software to lots of different people in different locations. You're also writing VBA in Excel which automates Outlook and I'd be very suprised if all your users had matching office installations. Of course, in theory it should be ok, but from experience I'd say you'll have quite a few teething problems getting everything to run properly on everyone's machines. I'm not saying this will definitely be the case, but when compared to a web-based solution where the code runs on a single machine the risk is significantly higher.

  15. #15
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Excel Solution (2000)

    just thought i would give a update ot how this is going, intitally all signs are good, Ive created my Excel spreadsheet and created an option to export to XML, This creates a XML file using The clumn headings in the sheets as tags then saves it into a tempoary file, then e-mails it as a attachments to the designated the sender.

    Then the sender has code to read through the folders in outlook find the XML files, compare it with what is in his spreadhseet and if it already exists it does nothing, if does already exists then it checks for diffrerence and promprts the user if they want to update , and if it doesnt exist it adds it to there spreadsheet.

    Below is basically the code Im using to write and read the XML , and below that is the code for sending and reading the e-mails

    Public Function ExportToXML(FullPath As String, wrKsht As String, strXMLn As String) As Boolean


    On Error GoTo ErrorHandler


    Dim colIndex As Integer
    Dim rwIndex As Integer
    Dim asCols() As String
    Dim oWorkSheet As Worksheet
    Dim sName As String
    Dim lCols As Long, lRows As Long
    Dim iFileNum As Integer


    Set oWorkSheet = ThisWorkbook.Worksheets(wrKsht)
    sName = oWorkSheet.Name
    lCols = oWorkSheet.Columns.Count
    lRows = oWorkSheet.Rows.Count


    ReDim asCols(lCols) As String

    iFileNum = FreeFile
    Open FullPath For Output As #iFileNum

    For i = 1 To lCols - 1

    If Trim(oWorkSheet.Cells(2, i + 1).Value) = "" Then Exit For
    asCols(i) = oWorkSheet.Cells(2, i + 1).Value
    Next i

    If i = 0 Then GoTo ErrorHandler
    lCols = i

    Print #iFileNum, "<?xml version=""1.0""?>"
    Print #iFileNum, "<" & sName & ">"
    For i = 3 To lRows
    If Trim(oWorkSheet.Cells(i, 2).Value) = "" Then Exit For
    Print #iFileNum, "<" & strXMLn & ">"

    For j = 2 To lCols


    Print #iFileNum, " <" & asCols(j - 1) & ">"
    Print #iFileNum, Trim(oWorkSheet.Cells(i, j).Value);
    Print #iFileNum, "</" & asCols(j - 1) & ">"


    Next j
    Print #iFileNum, "</" & strXMLn & ">"
    Next i

    Print #iFileNum, "</" & sName & ">"

    Close #iFileNum
    ExportToXML = True
    ErrorHandler:
    If iFileNum > 0 Then Close #iFileNum
    Exit Function
    End Function



    Public Sub psubReadandWriteXML(stRsht As String, stRran1 As String, STrran2 As String, strNode As String)
    Dim myXMLDoc As New DOMDocument
    Dim CClist1 As IXMLDOMNodeList
    Dim itmXML As IXMLDOMElement
    Dim ChlXML As IXMLDOMElement
    Dim Sht3 As Worksheet
    Dim Rng3 As Range
    Dim RngAdd As Range
    Dim BlnExist As Boolean
    Dim StrTest, strReplace As String
    Dim IntR, IntC, IntRX As Integer
    Dim Response
    Set Sht3 = ThisWorkbook.Sheets(stRsht)
    Set Rng3 = Sht3.Range(stRran1)
    Set RngAdd = Sht3.Range(STrran2)
    myXMLDoc.Load ("H:CCTempX.xml")

    Set CClist1 = myXMLDoc.documentElement.childNodes

    'Move Down through the XML File

    For Each itmXML In CClist1

    Set Rng3 = Sht3.Range(STrran2)
    Set RngAdd = Sht3.Range(stRran1)

    StrTest = itmXML.selectSingleNode(strNode).Text
    BlnExist = False
    IntR = RngAdd.Row
    IntC = 2

    For Each Var In Rng3
    If StrTest = Var.Value2 Then
    BlnExist = True
    IntRX = Var.Row
    End If
    Next

    '----- If the record doesnt exist then Add It.
    If BlnExist = False Then
    For Each ChlXML In itmXML.childNodes
    Sht3.Cells(IntR, IntC) = ChlXML.Text
    IntC = IntC + 1
    Next
    ThisWorkbook.Names(stRran1).Delete
    ThisWorkbook.Names(STrran2).Delete

    ThisWorkbook.Names.Add Name:=stRran1, RefersToR1C1:="=" & stRsht & "!R" & IntR + 1 & "C2"
    ThisWorkbook.Names.Add Name:=STrran2, RefersToR1C1:="=" & stRsht & "!R3C2:R" & IntR & "C2"

    End If

    '------ If the Record Does Exist then Check for differences
    If BlnExist = True Then
    For Each ChlXML In itmXML.childNodes
    If ChlXML.Text <> Sht3.Cells(IntRX, IntC) Then
    If InStr(1, ChlXML.Text, "/") Then
    strReplace = Format(ChlXML.Text, "MM/DD/YYYY")
    If strReplace = Sht3.Cells(IntRX, IntC) Then
    Exit For
    End If
    Else
    strReplace = ChlXML.Text
    End If

    Response = MsgBox("The Data for record complaint no: " & itmXML.selectSingleNode(strNode).Text & " " & _
    " " & Chr(13) & "Field: " & ChlXML.nodeName & Chr(13) & "Would you like to replace:" & _
    Chr(13) & Sht3.Cells(IntRX, IntC) & Chr(13) & " With :" & Chr(13) & strReplace & " ?", vbYesNo)

    If Response = vbYes Then
    Sht3.Cells(IntRX, IntC).Value = strReplace
    End If

    End If
    IntC = IntC + 1
    Next
    End If

    Next

    Set myXMLDoc = Nothing
    Set CClist1 = Nothing


    End Sub

    Public Sub psubEmailOverXML(strFile As String, StrSubject As String)

    Dim olOutlook As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim StrEmail As String

    StrEmail = Range(ActiveWorkbook.Names("SettingEmail")).Value2

    Set olOutlook = CreateObject("Outlook.Application")
    Set outMail = olOutlook.CreateItem(olMailItem)

    With outMail
    .To = StrEmail
    .Attachments.Add (strFile)
    .Subject = StrSubject
    .send
    End With

    Set outMail = Nothing
    Set olOutlook = Nothing

    End Sub


    Sub psubGetEmailData(strFolder As String, StrShta As String, StrR1 As String, Strr2 As String, StrNodea As String)

    Dim olOutlook As Outlook.Application
    Dim ns As Outlook.NameSpace
    Dim fld As Outlook.MAPIFolder
    Dim itm As Object
    Dim MyMail As Outlook.MailItem
    '-----------


    Application.ScreenUpdating = False


    Set olOutlook = CreateObject("Outlook.Application")
    Set ns = olOutlook.GetNamespace("MAPI")
    Set itm = ns.GetDefaultFolder(olFolderInbox)
    Set itm = itm.Folders(strFolder)

    For Each MyMail In itm.Items
    MyMail.Attachments.Item(1).SaveAsFile "H:CCTempX.xml"
    psubReadandWriteXML StrShta, StrR1, Strr2, StrNodea
    Next

    For Each MyMail In itm.Items
    MyMail.Delete
    Next


    Set olOutlook = Nothing
    Set ns = Nothing
    Set itms = Nothing
    Set MyMail = Nothing

    End Sub

Posting Permissions

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