Solved

Sending an MS Outlook email with data from a MS Access database

Posted on 2003-12-04
5
275 Views
Last Modified: 2008-03-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)
    With newMail
        .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

        With .Recipients
                .Add(tRecipients).Type = olTo
                 
        End With    
       
        'Send the mail message
        .Send
       
    End With
   
    'Release memory
    Set ol = Nothing
    Set ns = Nothing
    Set newMail = Nothing

End Function
0
Comment
Question by:bchess1
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 500 total points
ID: 9877413
do a recordset to generate all records into one string.

example-code for extracting a group of mailadresses from a qury into one string:

Private Sub mailadr_Click()
   Dim rst As Recordset
   Dim i As Integer
   Dim stradr, strsql As String
   
   strsql = "SELECT mailtest.PersEMail FROM mailtest WHERE (((mailtest.cat)= '" & Me.mailcategory & "'));"
   Set rst = CurrentDb.OpenRecordset(strsql)
   rst.MoveFirst

   Do While Not rst.EOF
       stradr = stradr & rst.Fields(0) & ";"
   rst.MoveNext
Loop
   stradr = Left(stradr, Len(stradr) - 1) 'get rid of the last ;
   Me!testfield = stradr
End Sub

you can look at aworking example at: http://www.tplus.at/~sissi-franz/test/mailtest.mdb

regards, Franz
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9877435
analog to the query selecting several mailadresse you can doe a query on your subform pulling all records to one message-string

Regards, Franz
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9878828
Hi bchess1

I created a table to store mail messages that are constructed from database data.

tblMail: sorta like an inhouse outbox (not to be confused with an outhouse inbox)
ID,TextBody,ToName,FromAddress,Subject,ToEmail,WebApplyId,LoanID,bSend

This table serves as a complete history of mail corespondence for all applicants.
Periodically I run a procedure not unlike yours to send all mail from tblmail where bSend = true
By doing it this way I can build my mail messages seperately from the mail sending routine.
The final part of my SendMail function sets bSend to false, the default for bSend is false.
So you can take as long as you like to build a mail message, it wont get sent untill bSend = True

This will enable you to prepare mail mesages in bulk, similar to Fanz's most excellent example, and when you have finished preparing a mesage, set bSend to true.

You could also have other forms and other users that create mail messages

Then the only source for your SendMail function is tblMail, really simplifies things, dont you think.

So you would open a recordset on tblMail where bSend = true

   With newMail
        .Subject = rs!Subject
        .Body = rs!TextBody
        .Importance = olImportanceHigh

        'etc...
        .Send
       
    End With

Alan :)



0
 
LVL 32

Expert Comment

by:jadedata
ID: 10264436
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: bonjour-aut {http:#9877413}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now