send e-mail via query search criteria from access

[Thomas Johansen]

Hi I have used jephens.com 's example and modified it for my needs.

url here: http://www.jephens.com/howtoemail.asp


It works fine until I use this like "*" &[search] to do a search criteria, in the query.


The query exist of a 2 fields with one with emailaddresses


Plz help me to do a "asked search criteria" wtih this code.

Thanks in advange

Thomas

Here is my code:

Option Compare Database
Option Explicit
' You need to declare a reference to the Outlook library, and the filesystemobject.
' this is not as hard as it sounds.
'
' Look in the menu above, and click Tools, then select References
'
' Scroll down the list until you see
' Microsoft Scripting Runtime -- and put a check next to it (if one is not there already)
'
' Microsoft Outlook Object Library -- check that.
'           There will be some version number there as well; it doesn't matter.
'           This will work with Outlook98 and Outlook2000 and OutlookXP.  It hasn't been tested on Outlook 2003 yet.


Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

  
 
   ' Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application


 ' Set up the database and query connections

    Set db = CurrentDb()

    Set MailList = db.OpenRecordset("MyEmailAddresses")

 ' now, this is the meat and potatoes.
 ' this is where we loop through our list of addresses,
 ' adding them to e-mails and sending them.

    Do Until MailList.EOF

        ' This creates the e-mail
        
        Set MyMail = MyOutlook.CreateItem(olMailItem)
            
            ' This addresses it
            MyMail.To = MailList("email")
            
            'This gives it a subject
            MyMail.Subject = Subjectline$
            
            'This gives it the body
            MyMail.Body = MyBodyText


            

            ' To briefly describe:
            ' "c:\myfile.txt" = the file you want to attach
            '
            ' olByVaue = how to pass the file.  olByValue attaches it, olByReference creates a shortcut.
            '      the shortcut only works if the file is available locally (via mapped or local drive)
            '
            ' 1 = the position in the outlook message where to attachment goes.  This is ignored by most
            '      other mailers, so you might want to ignore it too.  Using 1 puts the attachment
            '      first in line.
            '
            ' "My Displayname" = If you don't want the attachment's icon string to be "c:\myfile.txt" you
            '      can use this property to change it to something useful, i.e. "4th Qtr Report"



            'This sends it!
            'MyMail.Send'

            'Some people have asked how to see the e-mail
            'instead of automaticially sending it.
            'Uncomment the next line
            'And comment the "MyMail.Send" line above this.

            MyMail.Display


        
    'And on to the next one...
    MailList.MoveNext

Loop

 'Cleanup after ourselves

Set MyMail = Nothing


'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function