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")
or
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....
Sub Get_Data()
Dim conn as ADODB.Connection
Dim rs as ADODB.recordset
Set conn = new ADODB.Connection
conn.Open "DSN=Access1"
'put your SQL query here
sqlquery = "Select *.... "
Set rs = conn.Execute(sqlquery)
If rs.EOF = False then
'to return whole recordset to A2 (ie not looping the recordset)
Cells(2,1).CopyFromRecordset rs
'or to loop the recordset instead of the above - pasting the whole recordset
rs.movefirst
n = 1
'Do Until rs.EOF = True
'Cells(n,1) = rs.Fields("fieldname").Value
'Cells(n,2) = rs.Fields("otherfieldname").Value
'n = n + 1
'rs.movenext
'Loop
End If
rs.close
conn.close
End SubAnd 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-DennisHi,
ADO.Net will be replacing ADO as time goes by. This article may therefore be of interest:
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 SubYou 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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(65536, 3) <> Cells(1, 3) Or Cells(65536, 4) <> Cells(1, 4) Then
If Cells(1, 3) < Cells(1, 4) Then
Set aOutlook = GetObject(, "Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
'set Importance
aEmail.Importance = 2
'Set Subject
aEmail.Subject = "C1 less than C2 Alert..."
'Set Body for mail
aEmail.Body = Cells(1, 2) & vbLf & vbLf & Cells(4, 8)
'Set attachment if required
'aEmail.ATTACHMENTS.Add ActiveWorkbook.FullName
'Set Recipient
aEmail.Recipients.Add Cells(1, 1)
'Send Mail
aEmail.Send
Cells(65536, 3) = Cells(1, 3)
Cells(65536, 4) = Cells(1, 4)
End If
End If
End SubRemember that not all SQL dbs support nested sub queries - ie MySQL (pre v5.0).
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.
[vba]
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
Loop
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
Else
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
100
Next
End Sub
[/vba]
also check the VBA to ensure you do not have a worksheet change event (look in the sheet that is jumping).