SQL statements

[Wendy]

Hi

Is there a reference on the web which would show me how to run sql
statements from within my vb module. Or could someone show me how to get
started. I want to use Dev Ashish's Send Email Outlook module but take the
attachment names from my data table and cannot get it to work.

Thanks

Wendy

 

[Douglas J. Steele]

Assuming you're talking about the code in
http://www.mvps.org/access/modules/mdl0019.htm, you could either use the
DLookup function to get the attachment name, as in replacing

MAPIAddAttachment stFile:="C:\config.sys"

with

Dim varFile As Variant

varFile = DLookup("[FileName]", "[MyTable]", "User =
'(e-mail address removed)'")
If IsNull(varFile) = False Then
MAPIAddAttachment stFile:=varFile
End If

or you could open a recordset that returns the details of what you want to
send to whom, and loop through that recordset. The basic approach to using a
recordset is:

Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim clMAPI As clsMAPI

Set clMAPI = New clsMAPIEmail

strSQL = "SELECT EMailAddress, FileName FROM MyTable"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
Do Until rsCurr.EOF
With clMAPI
.MAPILogon
.MAPIAddMessage
.MAPISetMessageBody = "Test Message"
.MAPISetMessageSubject = "Some Test"
.MAPIAddRecipient stPerson:=rsCurr!EmailAddress, _
intAddressType:=1
.MAPIAddAttachment stFile:=rsCurr!FileName
.MAPIUpdateMessage
.MAPISendMessage boolSaveCopy:=False
.MAPILogoff
End With
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing