Problem: I want to use send mail to send an email to multiple recipients.
Here is my code.
Private Sub SubmitEngTstPlanBtn_Click()
Dim stWhere As Variant '-- Project Engineer assigned to project
Dim varTo As Variant '-- Email Address for Lab Engineer
Dim stText As String '-- E-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stReqNo As String '-- The requset no
Dim userName As String '-- Get user name
Dim strRequestr As Variant '-- Person requesting pur req approval
Dim OutStart '--Needed to start Outlook
Dim strLevel As String '--Level 'A' for Lab Engineer
On Error GoTo Err_SubmitEngTstPlanBtn_Click
strLevel = "A"
stReqNo = Forms![engineering Test plan]![Test ID]
userName = Environ("Username")
strRequestr = DLookup("[Name]", "Employees", "[usrname] ='" & userName & "'")
stWhere = DLookup("[Name]", "Employees", "[level] ='" & strLevel & "'")
'-- Looks up email address from Employees Table
varTo = DLookup("[email]", "Employees", "name = '" & stWhere & "'")
'--composing the email
stSubject = ":: Test Request #" & stReqNo & " Submitted for Approval ::"
stText = "Test request #" & stReqNo & " has been submitted by " & strRequestr & _
" for approval." & Chr$(13) & Chr$(13) & _
"This is an automated message. Please do not respond to this e-mail."
'--Starting outlook here
OutStart = Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE /recycle", vbMinimizedNoFocus)
'--Write the e-mail content for sending to assignee (-1 or true alows abbilty to change email.
'0 or false does not alow changing of email.
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, True
'--Update the following fields only if the email is sent
[Forms]![engineering Test plan]![Status].vale = Submitted
'--Close engineering Test plan form
DoCmd.Close acForm, "engineering Test plan"
'--Refresh main form
[Forms]![Main Switchboard Form].SetFocus
[Forms]![Main Switchboard Form].Refresh
'MsgBox Err.Description '-- Can be used to show system generated error
'Message returned if email not sent
MsgBox "In order to process this requisition you must send the email.", vbCritical, "Try again"
The code works fine except I want to send an email to all employees that are level "A". I'm pretty sure I can do this using a loop, but I haven't ever used one.
I'd sugest you try sending to many recipients, perhaps the BCC be appropriate rather than a single recipient per email. Some MTA's have a problem with a null recipient, some have a limit on the length of the send string. Might be worth working out if proposed means of sending emails limits are.
you can add recipients if they are separated by a semi colon.
you don't actualy need to shell out to outlook, it can be controlled via Access or you can attach to the libraires & datasoures direct. The one downside with the attach is that yur email doesn't get sent by that process, it sits in th queue untill outlook sends the email itself.
returns the "first" employee name rather than all?
A few quickies:
Try not to use Variants - strings will do fine in your case.
You obviously don't have Option Explicit set (Tools-> Options-> Require Variable Declaration). It will mean you need to declare your objects like this:
Dim OutStart as Object
but it will save you many hours tracking down a typo bug.
Environ is not well recommended for obtaining values. May be a bit pedantic, but try googling for an API function (GetUserName rings a bell - its the name of the function I use anyway).
To answer the question - you will want to pop the relevent email addresses in a recordset (ADO, DAO - the exciting choice is yours) and then loop i.e.
Do While Not MyRecordset.EOF
varTo = varTo & ";" & MyRecordset.Fields("email").Value
varTo = Mid(varTo, 1, LEN(varTo) - 1)
Environ is not well recommended for obtaining values. May be a bit pedantic, but try googling for an API function (GetUserName rings a bell - its the name of the function I use anyway)......
PKStormy had a neat soluton togetting the network logon using the system object (I Think) This thread show the dirty deed in all its glory. It looks a far neater way of getting the info then Dev Ashish's API call. Mu only reservation is which versions of Access will support that method. - still works on the ones I use so am happy.
I'm kinda a newbe when it come to VBA. When do I want to declare a variant and when do I want to delcare a string. I thought I read somewhere that if I use dlookup I need to declare.
A variant is a bulky variable. The reason for this is that it can contain any value that any other variable can contain. It is a sort of lowest common denominator variable. You only really want to use it where you don't know in advance what data type it will contain.
The reason it has been suggsted is that DLookup returns null if there is no return. Instead, try:
Dim strRequestr As String '-- Person requesting pur req approval
strRequestr = NZ(DLookup("[Name]", "Employees", "[usrname] ='" & userName & "'"), "")
If DLookup returns null, the code converts it to a zero length string ("") which a string variable can accept.
Ayhoo - lots of off topic text.
If your BE is Access\ Jet, check out DAO in help. Specifically you want to look at how to instantiate a DAO Recordset object using the OpenRecordset method of the DAO Database object. See how you go and post if you get stuck.