Outlook Macro to get data from a field in an MS Access DB

[nickrugado]

I want to get data from an ms access database using outlook to create a formatted e mail that can be sent out by the user.

This e mail would use about 10 fields in a temporary table. In the table the user name of the person wondering if the person's e mail address could be used and linked to the record that they placed in the table.

Any Ideas on how to accomplish this?? I'm trying to streamline a process.

We were using ms access before to create an e mail but because we access, ms access in two ways, 1 through terminal services with NO Email option and 1 through the desktop and that user can use the DB to create an e mail.

 

[SkipVought]

Hi,

What application are you coding in?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Try something like this...

Function GetHrsOfOper(sResource As String, dDateIn As Date)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    
    sPath = "\\SomeServer\Some Folder"
    sDB = "APS UNIVERSE"
    
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & sPath & "\" & sDB & ".mdb;"
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT * "

    sSQL = sSQL & "FROM YourTable"
        
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        On Error Resume Next
        .MoveFirst
        If Err.Number = 0 Then
    'process your data here
        End If
        
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NashProjects]

this would work:

Option Explicit

Public adoconn As ADODB.Connection
Public adors As ADODB.Recordset
Public adoconn2 As ADODB.Connection
Public adors2 As ADODB.Recordset
Public sql, ssql, ssql2 As String
Public sTable As String
Public filenm, MyField

Public Sub getrs()

sql = "Select * from Table1"
filenm = "C:\Data\sampledb.mdb"

Call GetCn(adoconn, adors, sql, filenm, "", "")

'you then have your information in a recordset.
'this would pop up with the value in your first field
msgbox adors.Fields(1).Value
'this would pop up with the value in your second field
msgbox adors.Fields(2).Value
'and so on

call CloseDb
End Sub

Public Sub CloseDb()

On Error Resume Next
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing

End Sub
Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _
sqlstr, dbfile, usernm As String, pword As String)

Set dbcon = New ADODB.Connection
dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
usernm, pword
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon

End Sub

Kind regards

Lee Nash

http://www.Nashprojects.com

 

[HarleyQuinn]

I would just add, when declaring variables like this:

Public sql, ssql, ssql2 As String

Only ssql2 is actually a string type, the other two's type will be set when they are assigned values. This is also the case when declaring Subs and/or Functions. This could potentially lead to problems with incorrect datatypes being passed and possibly causing errors.

If one line declarations are needed use:

Public sql As String, ssql As String, ssql2 As String

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[NashProjects]

Yes sorry I was being lazy there and should really have split those out..

Hope the rest of the code helps though.

Kind regards

Lee Nash

http://www.Nashprojects.com