send e-mail - get address from field in table 1

[timotai]

Hi all

Ok
Here is my problem:

I am using the blow code to send an e-mail with an attached object. I can't find a way to link this code to a field alled Mail ID in table TeamMembers.

Here is the code:

Function Mail()

DoCmd.SendObject acSendReport, "nextweek", acFormatRTF, (!!E-mail address here!!), , , "The Rota for next week starting- " & Date - (DatePart("w", Date, 2) - 8), "Next weeks Rota for the Odin BSS team is attached" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Many Thanks" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Automated Rota Datasbase E-mail", 1

End Function

(!!E-mail address here!!) is where the code needs to go to for the e-mail addresses.

I want a bit of code so that when a run this script it populates the e-mail, takes all the addresses from the field in the table and then the e-mail is ready to send.

Any Ideas?????

All help is appreciated.

Many Thanks
Tim

"knowledge is someone else's inspiration."

 

[JeremyNYC]

Check into the DLookup function for a slow way to do it. A faster way would be to use a recordset.

Dim db as dao.database
dim rst as dao.recordset
dim StrSql as string

StrSql = "SELECT [Mail ID] as MailID FROM TeamMembers WHERE " 'You'll have to come up with the criteria to determine which record to grab

set db = currentdb
set rst = db.openrecordset(strSql, dbopensnapshot)
call DoCmd.SendObject (acSendReport, "nextweek", acFormatRTF, rst("MailID&quot, , , "The Rota for next week starting- " & Date - (DatePart("w", Date, 2) - 8), "Next weeks Rota for the Odin BSS team is attached" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Many Thanks" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Automated Rota Datasbase E-mail", 1)

rst.close
set rst = nothing
db.close
set db = nothing
=========
I know it's a lot more code than using DLookup, but it's a much better solution. Also, if you need to loop through several, you can do this:

set db = currentdb
set rst = db.openrecordset(strSql, dbopensnapshot)

if rst.bof and rst.eof then
call msgbox("No Team Members meet the criteria.&quot
else
do until rst.eof
call DoCmd.SendObject (acSendReport, "nextweek", acFormatRTF, rst("MailID&quot, , , "The Rota for next week starting- " & Date - (DatePart("w", Date, 2) - 8), "Next weeks Rota for the Odin BSS team is attached" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Many Thanks" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Automated Rota Datasbase E-mail", 1)
rst.movenext
loop
end if

rst.close
set rst = nothing
db.close
set db = nothing

==========
Also, get rid of the spaces in the names of your fields (and tables, and other objects). They'll just cause you pain.

Hope this helps.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[timotai]

how does it know what the table is for the second option.

whenever I run it I get

Run-time error '3078'

the Microsoft Jet Database engine cannot find the input table or query". Make sure it exists and that it's name is spelled correctly.

and it is stopping on line

Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)

 

[JeremyNYC]

I missed when I cut and paste from the first example. You'll need to include this code, too:
Dim db as dao.database
dim rst as dao.recordset
dim StrSql as string

StrSql = "SELECT [Mail ID] as MailID FROM TeamMembers WHERE " 'You'll have to come up with the criteria to determine which record to grab

Probably the easiest way to build this Sql is to build a query and then view the Sql of it. Then you'll be able to copy and paste.

If there are places in the sql that use ", you'll have to change those to '. Also, put it all on one line, as it will be on multiple lines in the query window.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[timotai]

Just hat I needed, works like a charm.

I have never seen the bof and eof properties before but since you gave me this codin I have been reading up on them and think they will be very useful in the future.

Thanks you very much for your help and increasing my knowledge.

This well deserves a star

Many Thanks

Tim