Send email by looking up the address in a table

[Psiren17]

I am working on a database for measurement requests and i want the database to send an email to the administrator when a button is pressed. I can get everything to work apart from getting the database to look up the name of the admin in a seperate table.

I have been using the following code for a slightly different email, one which sends a message to the originator of the request but this code looks at the requestor id box on the form. How do i make it look at the "Administrators" table, "admin1" field instead?

Private Sub BTN_SAVESEND_Click()
On Error GoTo Err_BTN_SAVESEND_Click

strto = Me.Requestor_CDS_ID_

DoCmd.SendObject , , , strto & "@companyname.com", , , "Your Metrology Request: Job Number " & Me![Job number:] & " has been received.", "Your request will be completed as soon as possible and you will be notified by email when it is completed. ", False

Exit_BTN_SAVESEND_Click:
Exit Sub

Err_BTN_SAVESEND_Click:
MsgBox Err.Description
Resume Exit_BTN_SAVESEND_Click

End Sub

Any help would be greatly appreciated!

 

[pbaldy]

The simplest way is probably a DLookup().

 

[Psiren17]

Hi - how would i do that? I'm pretty new to all this so i cant write code - just adapt it.

 

[Psiren17]

are there any examples anywhere? I cant find anything.

 

[apophis4u]

DLookup("[Field Name to find]", "[Table Name to look into]", "FieldIDToMatch=YourValueAsInputCiteria")

Should do it; at least it worked for me.

with a variabel in VBA:

DLookup("[Field Name to find]", "[Table Name to look into]", "FieldIDToMatch=" & YourVariable )

 

[Psiren17]

Thanks - what do you mean by:

"FieldIDToMatch=YourValueAsInputCiteria"

I'm not quite sure what to put in those bits. I have the following so far:

STRTOADMIN1 = DLookup("[admin1]", "[Administrators]", "FieldIDToMatch=YourValueAsInputCiteria")

MsgBox "IMPORTANT: IF YOU SEE A WARNING MESSAGE SAYING THAT A PROGRAM IS TRYING TO SEND MAIL ON YOUR BEHALF PLEASE CLICK YES. "

DoCmd.SendObject , , , STRTOADMIN1 & "@companyname.com", , , "Metrology request Job Number: " & Me.Job_number_ & " has been entered into the request system. Please go into the database and authorise this request.", "N:B: This job requires a specific time/date as follows: " & Me.SMT_details_, True

 

[apophis4u]

You have a table with at least to fields:
Field A
Field B

DLookup( "Field A","Table 1","Field B = Criterium")

Example:
STRTOADMIN1 = DLookup("[admin1]", "[Administrators]", "AdminID=1")
or
STRTOADMIN1 = DLookup("[admin1]", "[Administrators]", "AdminID=" & STRTO )

Please notice that STRTO in this case has to be a Number; if this is a text value you have to use single quotes around the string from STRTO.

STRTOADMIN1 = DLookup("[admin1]", "[Administrators]", "AdminID='" & STRTO & "'")
(that last bit is this without spaces: " ' " )

 

[apophis4u]

Here are two more good example pages:

http://www.techonthenet.com/access/functions/domain/dlookup.php

http://www.everythingaccess.com/tutorials.asp?ID=Getting-a-value-from-a-table:-DLookup()

 

[Psiren17]

Hmmm.. i think i'm making a real mess of this because i'm bodging bits of code without really understanding them. I dont think i actually need to use a DLookup because there will only be one value in the table - i just need the code to pick up the one entry in there and add @companyname.com to the end of it.

Its basically because the emails always need to go to the manager but if he is on holiday or something he wants them to go to someone else as his deputy. I was hoping to be able to give him a simple little form so that when he goes away he effectively changes the name that is in the table (without knowing it of course!).

Is there any simple way to do this? The only other option is that i put his email address right into the code but this would mean me having to change it every time he is away.

 

[boblarson]

I dont think i actually need to use a DLookup because there will only be one value in the table - i just need the code to pick up the one entry in there and add @companyname.com to the end of it.

Actually a Dlookup is the best for that, so the syntax is just:


And if there will always be only ONE entry you can use:

strto = DLookup("YourFieldNameFromTheTableHere","YourTableNameHere")

Replace the parts that say YourFieldNameFromTheTableHere and YourTableNameHere with the actual field and table name in your database that you are trying to get the information out of.

 

[Psiren17]

That works exactly how i need it to - thanks so much for your help and patience!