Sending an MS Outlook email with data from a MS Access database
Posted on 2003-12-04
I’m trying to use Access to send an email to specified recipients via Outlook based off of data in a form and its subform. The parent form is “FrmQryUniqueUser” and the subform is “FrmQryUniquePermissions”. The parent form lists an employee, his/her email address, and title. The subform lists all the permissions the employee has in a continuous form. Everything works great, except when the email is generated, only the first item in the subform is listed in the body of the email. I have two questions:
1. Any thoughts on how can I get all records in the subform into the body of the email?
2. I may need to send this email to multiple people. Is there a better way to do this rather than using the forms I’ve created to basically mass mail to people in my office?
Below is the code I’ve written thus far. The string “tRecipients” is a control on the parent form. The string “tPara2” is the control that lists the permissions on the subform. You will see that in the body of the email I have several lines of text and carriage returns between the lines. Thanks for any help you can offer.
Public Function WelcomeNote()
Dim tRecipients As String
Dim tPara1 As String
Dim tPara2 As String
Dim tPara3 As String
Dim tPara4 As String
tRecipients = Forms!FrmQryUniqueUser!EMPLE_EMAIL_ADDR_TXT
tPara1 = "Please ensure you have the following permissions set:"
tPara2 = Forms!FrmQryUniqueUser!FrmQryUniquePermissions.Form!SYS_DESCR_TXT_2
tPara3 = "Please notify your administrator if any items are missing."
TPara4 = "Thank you."
Dim ol As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim newMail As MailItem
'Return a reference to the MAPI layer
Set ns = ol.GetNamespace("MAPI")
'Create a new mail message item
Set newMail = ol.CreateItem(olMailItem)
.Subject = “Welcome”
.Body = tPara1 _
& (Chr(13) & Chr(10)) & (Chr(13) & Chr(10)) & tPara2 _
& (Chr(13) & Chr(10)) & (Chr(13) & Chr(10)) & tPara3 _
& (Chr(13) & Chr(10)) & (Chr(13) & Chr(10)) & tPara4
.Importance = olImportanceHigh
.Add(tRecipients).Type = olTo
'Send the mail message
Set ol = Nothing
Set ns = Nothing
Set newMail = Nothing