Hello everybody, Ive been struggling with this for the last day or so. I've tried 2 ways. I am trying to insert text (names/address etc) into the body of an email (with terms and conditions etc) that has been loaded as a template in MS Access.
The problem is I need a check box in the email body to select to show that the person reading the email has understood the terms and conditions. I thought it would be easier to create this template in outlook (with the check box) and then load it into Access and then insert the name/address etc into the body of the email - but this then just overwrites the text in the template. Ive tried it the other way and included the names/address etc in the vba code - but then cant figure out how to put a check box in vba. Ive included the code below (which works with the check box/template - but I cant figure out how to include the names in the body).
Or if its easier to include a check box in vba with the body of the email in the code (which I also got to work) - but as I say i couldnt do that bit with the checkbox
I hope that makes sense, somebody may have better suggestions - code for loaded template with check box without names/address below
The problem is I need a check box in the email body to select to show that the person reading the email has understood the terms and conditions. I thought it would be easier to create this template in outlook (with the check box) and then load it into Access and then insert the name/address etc into the body of the email - but this then just overwrites the text in the template. Ive tried it the other way and included the names/address etc in the vba code - but then cant figure out how to put a check box in vba. Ive included the code below (which works with the check box/template - but I cant figure out how to include the names in the body).
Or if its easier to include a check box in vba with the body of the email in the code (which I also got to work) - but as I say i couldnt do that bit with the checkbox
I hope that makes sense, somebody may have better suggestions - code for loaded template with check box without names/address below
Code:
Private Sub cmdesign_Click()
Dim rs As DAO.Recordset
Dim olApp As Outlook.Application
Dim myitem As Outlook.MailItem
Dim olNS As Outlook.NameSpace
Dim email As String
Dim fullname As String
Dim clientID As String
Dim incidentdate As String
Dim message As String
Dim SQL As String
Set rs = CurrentDb.OpenRecordset( _
"Select A.fldClientID, A.fldIncidentDate, A.fldDateCreated,B.fldEmailAddress, B.fldFullName FROM tbl_Instructions AS A INNER JOIN tbl_ClientDetails AS B ON A.fldClientID=B.fldClientID " & _
"WHERE A.fldClientID = " & Me!txtClientID & "", dbOpenSnapshot, dbReadOnly)
'DoCmd.RunSQL SQL
Do Until rs.EOF
' put required fields check in here
If Me!txtClientID = rs![fldClientID] Then
incidentdate = rs![fldIncidentDate]
fullname = rs![fldFullName]
clientID = rs![fldClientID]
email = rs![fldEmailAddress]
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set myitem = olApp.CreateItemFromTemplate("C:\Users\steph\AppData\Roaming\Microsoft\Templates\CompanyFOAR.oft")
With myitem
.To = email
.Subject = "Company" & "-" & " " & fullname
' .Body = message
.Display
End With
Set olApp = Nothing
End Sub
but was thinking you would just have
