Unanswered: Email to Specific Address based on form field
Hi all, this one is a two parter that I need some help on. I am trying to email to a specific person based on a no. in a field which references a query that houses the email address. I can do the basic email in a macro, but because of the specific identifier and the need to have it pull the Worksheet No. into the email...I have no clue how to do this in plain access, which means coding it. Unfortunately Im not very good with vb although I am starting to catch on thanks to many people's help in here.
Part 1: Sending the email..
I have a worksheet no: [CRID]
I have a query with the email list: [14_03-SQEMGboth]
I have a field that touches both: [MtrlGrp]
The email address field is: [14_03-SQEMGboth]![Email]
The notify button [Notify] would be on the main form: [00_02_03-PRall]
The box that would tell it where to look is [MtrlGrp], which links to the query that contains the email address to pull.
Part 2: Putting the worksheet no. in the email.
Is there anyway to tell it to put the [CRID] number in the email.
"New worksheet [CRID] has been entered. Please review in the database." or something like that.
below is a generalised outlook mailer that i usually put in a global module so any form can call it. you just need to call it with your data. e.g.
isDraft = true or false to suit what you want to do
isTo = [14_03-SQEMGboth]![Email]
isSubj = "New Worksheet " & [CRID]
isBody = New worksheet " & [CRID] & " has been entered. Please review in the database."
ignore the optional isFile unless you want to attach a copy of [CRID]
the call is
if izyMailer(isDraft, isTo, isSubj, isBody) then
msgbox "Mailing OK"
msgbox "Mailing error"
Public Function izyMailer(isDraft As Boolean, isTo As String, isSubj As String, isBody As String, Optional isFile As String = "NONE") As Boolean
' isDraft TRUE saves the mail in drafts, FALSE sends immediate
' isTo the recipient's mail address (addrFirst; addrMiddle; addrLast if a distribution list)
' isSubj the mail subject
' isBody the mail body text
'and optionally accepts
' isFile the drive:/path/name.extension of a file to attach
'caller is responsible for validating isFile
'function returns TRUE on success, else FALSE
On Error GoTo err_izyMailer
Dim objOutlook As Outlook.Application
Dim objOutMail As Outlook.MailItem
Dim objOutDist As Outlook.Recipient
Dim objOutFile As Outlook.Attachment
Set objOutlook = CreateObject("Outlook.Application")
Set objOutMail = objOutlook.CreateItem(olMailItem)
Set objOutDist = objOutMail.Recipients.Add(isTo) 'make distribution list
objOutDist.Type = olTo
objOutMail.Subject = isSubj 'make subject
objOutMail.Body = isBody 'make message body
If Not isFile = "NONE" Then Set objOutFile = objOutMail.Attachments.Add(isFile) 'attachment
objOutMail.Save 'save as draft
If Not isDraft Then objOutMail.Send '...and possibly send
izyMailer = True 'success return
Set objOutDist = Nothing 'tidy up
Set objOutFile = Nothing
Set objOutMail = Nothing
Set objOutlook = Nothing
Exit Function 'and exit
izyMailer = False 'failure return
MsgBox Err.Description, vbCritical, "izyMailer Error"
Got this resolved I actually ended up getting it to work in a reg. Im sharing it in case anyone ever needs it. Here's the Access macro.
Object Type:Blank 'not sending a document with the email
Object Name: Blank 'same
Output Format: Blank 'same
Output To: =[SFSQE]![Email] 'chose the email address in the subform
CC: firstname.lastname@example.org 'cc this persons email addy
Subject: ="New Worksheet " & [CRID]
Message: ="New worksheet " & [CRID] & " has been entered as of" & Now() & ". Please review in the database."
EditMessage: NO 'change if you want email editable
Template File: Blank 'not needed for this
I had to change it so my subform had a hidden email field so its there, but not showing, thus the email can pull it to send to. =)