As a matter of fact I have an excel-question, but it also concerns databases so I'll give it a try.
I wrote a module in excel which opens an excel-file and change its layout. Then it should be mailed to a person whose intitials are in the filename.
Knowing this initials I should query a database to retrieve the appropriate mailaddress.
Everything is OK, except I have no idea how to get that mailaddress from the external database.
To connect to the database I have a fileDSN named 'Dunning.dsn'.
In code I already have something like this :
Public Sub OpenFile()
Dim fileName as String
Dim agentMailAddress as String
fileName = "FiGoFA.xls" 'where FA are the initials of the agent
agentMailAddress = GetMailAddress ("FA")
With ....... 'all the mailstuff
.Recipients = agentMailAddress
Function GetMailAddress (agentCode as String) As String
Here I don't know where to start:
- make a connection to SQL-database using a fileDSN
- Getting the query to work
"SELECT mailAddress FROM tblAgents WHERE code = " & agentCode
I hope some of you can bring some light into the darkness.