Build a system generated email

[Woodmansterne_Ralph]

I have an Access database which I want to send reminder emails out to people at particular times.



I have built code to generate the emails which all works fine but it is creating the email in my outlook draft folder and I want the emails to appear as if that they have come from the database directly i.e. system generated.

I presume I need to have an email address set up but how do I configure it so when the code runs it uses the email that I need to get set up rather than it coming from the person running the code?

[Bulzie]

I have a process that loops through records and sends an email for each one it finds. I did not write this but use it to send email through the Exchange server, not personal email account. You will need to talk to your email admin on the schemas info.

Call the function:
Sendit = Send_Mail(rs1!AddressEmail, "", "script@aaa.com", "Subject", "Body")


Public Function Send_Mail(ByVal MailTo As String, ByVal Mailcc As String, ByVal MailFrom As String, ByVal MailSubject As String, MailBody As Variant)
On Error GoTo ErrTrap

Dim LoopFlag As Boolean
Dim Pos As Integer
Dim TempString As String

Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1
Set Flds = iConf.Fields
With Flds

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xx.x.x.xx" exchange server IP
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxx"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxx"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

.Update
End With

Do While LoopFlag = False

DoEvents

' Get next recipient from MAILTO List
Pos = InStr(1, MailTo, ";")
If Pos = 0 Then

LoopFlag = True
TempString = MailTo

Else

TempString = Left(MailTo, (Pos - 1))
MailTo = Right$(MailTo, (Len(MailTo) - Pos))

End If
TempString = Trim(TempString)
MailTo = Trim(MailTo)

With iMsg
Set .Configuration = iConf

.To = TempString
.CC = Mailcc
.From = MailFrom
.Subject = MailSubject
.TextBody = MailBody
' If FileName <> "" Then
' .AddAttachment FileName
' End If

.Send
End With

Loop

Set iMsg = Nothing
Set iConf = Nothing
Exit Function

ErrTrap:
' If a crash is necessary (specified by setting the HaltOnError flag)
' script will stop with a popup message, if message is informational,
' the script will resume without sending the message
' If HaltOnError Then
MsgBox "Email Error" + vbCrLf + MessageNoteSubject + vbCrLf + "to Recipient: " + TempString + vbCrLf + "Error: " + Err.DESCRIPTION, vbCritical + vbOKOnly, "Error (" + CStr(Err.Number) + ")"
Close
End
' Else
' Resume Next
' End If
End Function

[Micron]

I like that method and have used it before. In my case, I wasn't sending an email for every record since most of the time, the recipient had more than one item needing attention. We didn't want the records included as an attachment, rather as part of the email body. This made it much more of a challenge. Basically, I passed the recordset (from the query that returned all the necessary data) to a function that first created an email object (because there was also a class object for emails), looped through the recordset and built an html type email for the object. Then the email was added to a collection. When all the looping was done, I might have 1 or many email objects in the collection. The procedure that called this routine then called a procedure similar to Bulzie's - once for each item in the collection. The end result is that the db sent an email to each recipient that had items to review (only their items) but each item in a new line in the email body.

What Bulzie might have forgotten to mention is that to use CDO you need to set a reference to it in your project.

[Woodmansterne_Ralph]

Thanks Bulzie, I'll speak to the admin team and get back to you.