I trimed out some of the code - but this basically creates an Email -
adds a introductory paragraph, inserts the data from a form ( I have
other versions that use a recordset) and then add more text after, it
then create a record for tracking purposes. The trick is to concatenate
your mail item body. I don't think it's possible to add formatting to
the body except for carriage returns and the like.
-------Code Below-----------------------------------
Dim outlk As Outlook.Application
Dim outns As Outlook.NameSpace
Dim mitem As Outlook.MailItem
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strBM_name As String
Dim strbm_lang As String
Dim intbm_num As Integer
Dim strBM_email As String
Dim strcnslt_num As String
Dim strcnslt_nam As String
Dim strclient_num As String
Dim strclient_nam As String
Dim intSpace As Integer
Dim inttest As Integer
Dim strRiskTolerance As String
Dim strassessment As String
On Error GoTo Err_handler
Set dbs = CurrentDb
Set outlk = CreateObject("Outlook.application")
Set outns = outlk.GetNamespace("MAPI")
'Create Email and attach all Files recorded in log
strGeographic_area = Forms![Concentration
Form].[cmbAreas].Column(1)
strBM_name = Forms![Concentration
Form].subformEmailClient.Form.bm_name
strbm_lang = Forms![Concentration
Form].subformEmailClient.Form.bm_lang
intbm_num = Forms![Concentration
Form].subformEmailClient.Form.bm_id
strBM_email = Forms![Concentration
Form].subformEmailClient.Form.bm_email
'strBM_email = "Compliance Reporting" ' Testing
strCNSLT_id = Forms![Concentration
Form].subformEmailClient.Form.CNSLT_ID
strcnslt_name = Forms![Concentration
Form].subformEmailClient.Form.CNSLT_NAME
strclient_id = Forms![Concentration
Form].subformEmailClient.Form.CLIENT_ID
strclient_name = Forms![Concentration
Form].subformEmailClient.Form.CLIE_NAME
strplan_id = Forms![Concentration
Form].subformEmailClient.Form.Plan_id
strRiskTolerance = Forms![Concentration
Form].subformEmailClient.Form.RISK_TOLERANCE_NAM
strassessment = Forms![Concentration
Form].subformEmailClient.Form.FIT_ASSESSMENT_NAM
strper = Forms![Concentration Form].subformEmailClient.Form.txtper
intplannum = Forms![Concentration
Form].subformEmailClient.Form.txtplannum
Set mitem = outlk.CreateItem(olMailItem)
mitem.Recipients.Add strBM_email
'mitem.DeleteAfterSubmit = True
mitem.SentOnBehalfOfName = strGeographic_area
mitem.Body = mitem.Body & "In order to meet Tier Two supervisory
requirements under the MFDA, the IGFS Compliance Department conducts
monthly reviews of client plans for high levels of concentration in our
highest risk funds. " _
& " This review has been completed and has resulted in the
following plans being identified:" & vbCrLf _
&
"---------------------------------------------------------------------------------------------------------------------------------"
& vbCrLf _
& "Consultant Name/Number: " & strcnslt_name & "/" & strCNSLT_id &
vbCrLf _
& "Client Name/Plan Number:" & strclient_name & "/" & strplan_id &
vbCrLf _
& "Risk Tolerance: " & strRiskTolerance & " - % Concentrated: " &
Format(strper * 100, "#0.0") & "% - " _
& " KYC Status: " & strassessment & vbCrLf
mitem.Subject = strBM_name & " Client Concentration"
mitem.Body = mitem.Body & vbCrLf & "For client plans too
aggressive or KYC missing, the information must be updated by
requesting the Consultant obtain a signed KYC form and submit it to the
Region Office for updating. Please confirm this has been completed
within 10 business days." _
& vbCrLf & vbCrLf & "In addition, please review clients with a KYC
Match. Clients who are concentrated in their plan holdings (especially
our highest risk funds) are subject to increased risk. These plans
should be reviewed and the client should be made aware of this
additional risk." _
& vbCrLf & vbCrLf & "Please email your geographical Compliance
emailbox if you have any questions regarding the review process." _
& vbCrLf & vbCrLf & "Thank You"
' Email Successfull Send now Update Tracking Tables with Particular
Set rst = dbs.OpenRecordset("tblConcentrationTracking")
rst.AddNew
rst.Fields("plan_id") = strplan_id
rst.Fields("plan_num") = intplannum
rst.Fields("bm_address") = strBM_email
rst.Fields("email_send") = Now()
rst.Fields("email_body") = mitem.Body
rst.Update
rst.Close
DoCmd.Hourglass False
mitem.Send
MsgBox ("Email Sent")
-------------Code End -------------------------------------
Lateral said:
Hi guys
I am using the Issue database template from Microsoft and are making some
enhancements.
I want to be able to generate an email and have the body of the email
contain the report data.
I can create attachments ok but I really want to be able to "merge" the
report into the body of the actual email.
Any help is appreciated.
Regards
Greg
