Hi,
I've been using the following code to find a name and return the associated address from a closed file to automatically address email in Outlook. The result of the search is currently placed in a cell on the activeworksheet but I want it to return to a variable or put it straight into the To box in Outlook.
I've found several other methods but none incorporate the 'find' function I require and all my efforts to get it to work have failed.
I want it to work in Excel 97 & 2000 and would prefer not to use ActiveX unless it can be programmatically activated.
Any help greatly appreciated.
Pauly
Here's the current code:
Sub GetClientAddress()
Dim strPath As String
Dim strFile As String
Dim connstring As String
Dim sqlstring As String
Dim strClientName As String
strClientName = ActiveSheet.Range("b4")
strPath = frmSetup.TxtMasterFolder & "/" 'important to have the / added
strFile = "Client Addresses.xls"
connstring = _
"ODBC;DBQ= " & strPath & strFile & ";DefaultDir=" & strPath & ";Driver={Microsoft Excel Driver (*.xls)}"
sqlstring = _
"SELECT Addresses.`Client Address` FROM Addresses Addresses WHERE (Addresses.`Client Name`='" & strClientName & "')"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
End With
End Sub
This message was edited by pauly on 2002-02-17 13:39
I've been using the following code to find a name and return the associated address from a closed file to automatically address email in Outlook. The result of the search is currently placed in a cell on the activeworksheet but I want it to return to a variable or put it straight into the To box in Outlook.
I've found several other methods but none incorporate the 'find' function I require and all my efforts to get it to work have failed.
I want it to work in Excel 97 & 2000 and would prefer not to use ActiveX unless it can be programmatically activated.
Any help greatly appreciated.
Pauly
Here's the current code:
Sub GetClientAddress()
Dim strPath As String
Dim strFile As String
Dim connstring As String
Dim sqlstring As String
Dim strClientName As String
strClientName = ActiveSheet.Range("b4")
strPath = frmSetup.TxtMasterFolder & "/" 'important to have the / added
strFile = "Client Addresses.xls"
connstring = _
"ODBC;DBQ= " & strPath & strFile & ";DefaultDir=" & strPath & ";Driver={Microsoft Excel Driver (*.xls)}"
sqlstring = _
"SELECT Addresses.`Client Address` FROM Addresses Addresses WHERE (Addresses.`Client Name`='" & strClientName & "')"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
End With
End Sub
This message was edited by pauly on 2002-02-17 13:39