# Outlook VBA to fetch data from Access table



## jsp49 (May 13, 2009)

Hi,

Within Outlook, I am looking to add a Toolbar button that calls a vba procedure that does the following:
-Prompt user for a code
-Find the code in fldCode in tblLookup in an Access database
-Fill in the To, Subject, Body, etc in a new mail message based on contents from the tblLookup

Any ideas. I have been scratching my head in defeat for quite a long time - any help would be truly appreciated!!


----------



## terabytecomputer (Apr 20, 2009)

This should get you started. Make sure you add in the 'Microsoft ActiveX Data Objects 2.8 Library' (I added 2.8, other versions may work) by opening Outlook Visual Basic and going to 'Tools', 'References'.

Create a Macro in Outlook and code it to connect to your database something like this:

Sub GetMyData()
Dim myCn As New ADODB.Connection
Dim myCmd As New ADODB.Command
Dim myRs As New ADODB.Recordset

Set myItem = Outlook.CreateItem(olMailItem)

myCn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=databasename.mdb;" & _
"DefaultDir=databaselocation (c:\whatever\whatever);"

Set myRs = myCn.Execute("Select * from tablename")

'Replace field names in my example below to match your database fields
myItem.To = myRs.Fields("EmailAddress")
myItem.Subject = myRs.Fields("Subject")
myItem.Send

'Be sure to close the recordset and connection when you're done
Set myRs = Nothing 
Set myCn = Nothing

End Sub

After you create that macro, you can add it as a button to a toolbar. In Outlook, select 'Tools', 'Customize', 'Commands' tab, 'Macros'. Drag your macro to the toolbar.

Hope this helps.


----------

