gold007eye
Registered User.
- Local time
- Today, 08:59
- Joined
- May 11, 2005
- Messages
- 260
How do I send email from a table list?
Can anyone please help me or lead me in the right direction as to how I can have this code be more effecient, by storing the e-mail address information in a table and having this code look to the table or a query to figure out which e-mail address(es) to send to? See the code in red that is where I would rather have something like:
That is just my thoughts of what I am trying to accomplish with it. Also keeping in mind that either of those may need to be sent to multiple e-mail addresses. So I'm not sure how to account for that as well. As in Outlook where you would enter a ";" between e-mail addresses.
Any help would be much appreciated, because having to modify a form everytime someone is added or removed is not very efficient; especially when you have to jump through hoops to move a front end from Test to Production.
Can anyone please help me or lead me in the right direction as to how I can have this code be more effecient, by storing the e-mail address information in a table and having this code look to the table or a query to figure out which e-mail address(es) to send to? See the code in red that is where I would rather have something like:
Code:
.To: = [Table]![Send Mail]![Email Address] Where [Table]![Send Mail]![To] = True And [Table]![Send Mail]![AR Code]="V1"
.CC: = [Table]![Send Mail]![Email Address] Where [Table]![Send Mail]![CC] = True And [Table]![Send Mail]![AR Code]="V1"
.BCC: = [Table]![Send Mail]![Email Address] Where [Table]![Send Mail]![BCC] = True And [Table]![Send Mail]![AR Code]="V1"That is just my thoughts of what I am trying to accomplish with it. Also keeping in mind that either of those may need to be sent to multiple e-mail addresses. So I'm not sure how to account for that as well. As in Outlook where you would enter a ";" between e-mail addresses.
Any help would be much appreciated, because having to modify a form everytime someone is added or removed is not very efficient; especially when you have to jump through hoops to move a front end from Test to Production.
Code:
'==== A/R 06,40,71,97,V1 Code Start ====
If Me![A/R Code] = "06" Or _
Me![A/R Code] = "40" Or _
Me![A/R Code] = "71" Or _
Me![A/R Code] = "97" Or _
Me![A/R Code] = "V1" Then
Dim mail
Set mail = Nothing
' Send by connecting to port 25 of the SMTP server.
Dim iMsg
Dim iConf
Dim Flds
Dim strHTML
Const cdoSendUsingPort = 2
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
'ToDo: Enter name or IP address of remote SMTP server.
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "usahm204.amer.corp.eds.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With
' Build HTML for message body.
strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "This is an automated e-mail to let you know that <b><font color=#FF0000>" & [Name of Requestor] & "</b></font> from <b><font color=#FF0000>" & [Department of Requestor] & "</b></font> has submitted a new <b><font color=#FF0000>A/R " & [A/R Code] & "</b></font> request in PERD."
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"
' Apply the settings to the message.
With iMsg
Set .Configuration = iConf
[COLOR="Red"].To = "<Test.Email@eds.com>;<Test2.Email@eds.com>" 'ToDo: Enter a valid email address.
.Cc = "<Test.CC@eds.com>"
.Bcc = "<Test.BCC@eds.com>",[/COLOR]
.From = "PERD Request<Test.Dummy@eds.com>" 'ToDo: Enter a valid email address.
.Subject = "New A/R " & [A/R Code] & " Request"
.HTMLBody = strHTML
.Send
End With
' Clean up variables.
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
'==== A/R 06,40,71,97,V1 Code End ====
Last edited:
