Posts by lasw10

    ok well i notice a quick problem with the way you're referencing cells...

    in vba there are two methods of referring to cells...

    1) Range
    2) Cells

    Range works like normal XL

    ie Range("A1") refers to A1

    Cells works like R1C1 formula in XL

    ie Cells(2,1) refers to A2 (row2,column1)

    so you can't use

    recipients.add Cells(a1)

    you would use either

    recipients.add Range("A1")
    recipients.add Cells(1,1)

    in VBA you should use the latter (better 'practice' as VBA programmers go)

    MS Query is pants.

    I STRONGLY recommend you do not use it.

    Suggest you do the following:

    1. Go into your Control Panel - and go to ODBC Data Sources (if Win2K above it will be in Administration Tools or something along those lines)
    2. Create a System DSN connection to your Access db using the MS Access ODBC Driver (which will be available automatically) - call this DSN something like Access1.
    3. Go back to XL - hit Alt F11 to go into the VB Editor.
    4. Create a Module.
    5. Go to References and tick Microsoft Active X Data Objects Library (2.5 or above)
    6. Now to create a simple macro to get your data from Access....

    And there you go, you have your own module to run queries against your database, you don't have to create the DSN but it makes the connection string far simpler for you.

    Benefits of ADO vs MS Query
    a) quicker and more efficient
    b) easier to adapt (your SQL queries)
    c) more robust
    d) easier to extend capability (ie looping the recordset)
    e) more elegant

    Hope that helps

    Quote from XL-Dennis


    ADO.Net will be replacing ADO as time goes by. This article may therefore be of interest:

    Link to the MSDN-article

    Dennis - don't know about you but I despise ASP.Net already... :rambo:
    It has some wonderful server side capabilities but is infuriating at times, the best of the lot is that it only posts (form) to itself...

    what do you want the trigger for the email to be? someone changing the value of C1 or D1?

    Assuming that you want to test for the email requirement when either C1 or D1 is changed then:

    First you need to establish the current values prior to any changes - do this first off by using a worksheet activate event on the sheet you're working on

    Private Sub Worksheet_Activate()
    Cells(65536, 3) = Cells(1, 3)
    Cells(65536, 4) = Cells(1, 4)
    End Sub

    You then need to set up a selection change event that tests the values in C1 and D1 to those original values of C1 & D1 which are stored in row 65536. If they are the same then you don't need to go any further. If they have changed you then need to test them.

    The below code does not use the Outlook object model (though you could use this if you so desired), this makes mailing workbooks across different operating versions far easier though it requires a little more delving around to find the correct syntax as opposed to using OL's built in olmailitem.

    Personally I don't like selection_change events as they will run everytime you move cell which is not particularly efficient. But if this is how you want to do it then this should work for you.

    Some basic OL VBA I use, the event here is a new mail event but you could obviously tailor this.

    Denis is right re: the Rule Wizard but doing it yourself in VBA gives you greater control and of course you can if necessary control other MS Apps as part of the handler.
    Private Sub Application_NewMail()
    Dim Item2 As MailItem
    Dim n As Integer
    Dim d As Integer
    Dim today As Integer
    today = Day(Date)
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myfolders = myNameSpace.Folders
    n = 1
    Do Until myfolders.Item(n) = "Mailbox - FinanceDaemon"
    n = n + 1
    Set myfolder = myfolders.Item(n)
    Set myInbox = myfolder.Folders("Inbox")
    On Error GoTo 100
    For Each Item2 In myInbox.Items
    d = Day(Item2.ReceivedTime)
    If (Item2.Subject <> "PO Request for Authorisation Issued" And _
    Item2.Subject <> "PO Rejected" And Item2.Subject <> "PO Number Issued") Or d <> today Then
    GoTo 100
    Set movefolder = myfolder.Folders("PO Emails")
    If Item2.Subject = "PO Request for Authorisation Issued" Then
    Set movefolder2 = movefolder.Folders("App Requests")
    End If
    If Item2.Subject = "PO Rejected" Then
    Set movefolder2 = movefolder.Folders("Rejections")
    End If
    If Item2.Subject = "PO Number Issued" Then
    Set movefolder2 = movefolder.Folders("POs Issued")
    End If
    Item2.Move movefolder2
    Item2.UnRead = False
    End If
    End Sub