Email using user selected HTML Template with optional report as attachment

[russ0670]

Hi am am really struggling with this piece of coding.
I have an access form. It has

-Two textboxes with client first and last name
-Two textboxes with a contact 'email1' and 'email2' for the client
-A combobox with a list of templates to use for the email.
-A checkbox to include a copy of the statement on the email.

I want the user to be able to press a button which does the following



-Sends an email to both 'email1' and 'email2'.
-Attaches a secific report as PDF if the user has selected the checkbox
-The body of the email includes a greeting line using the clients name from the record.
-The body of the message includes below the greeting line a html email template depending on the users combobox selection.


I have searched so many forums and i can find pleanty of code to send an email but i cannot get any of it to work for the above example.Any help would be greatly appreciated.

[June7]

What does 'cannot get any of it to work' mean - error message, wrong results, nothing happens? Exactly what is the issue? What method are you using? Post attempted code. Maybe you just need:

strEmail = Me.textbox1 & "," & Me.textbox2

Will there ALWAYS be two emails?

I have never used email template.

[russ0670]

SOLVED Kind of.

Used the following code and works great.

However does anyone know if there is a way to add dynamic text above the content of a .oft template in the email. e.g Set a greeting line based on the form data and then put this greeting line above the generic content from the template??

I have tried adding a .body however this then overrides the .oft template and only this displays on the email.

Private Sub Emailer2()

DoCmd.SetWarnings False
On Error GoTo cmdEmail_Err

Dim oItem As Outlook.MailItem
Dim oOutlookApp As Outlook.Application
Dim strAtt
DoCmd.Hourglass True

If oOutlookApp Is Nothing Then

Set oOutlookApp = CreateObject("Outlook.application")

Else

Set objOutlook = GetObject(, "Outlook.Application")

End If


Set oItem = oOutlookApp.CreateItemFromTemplate("C:\FastFile\System 3.0\3.0\BLANKTEMP.oft")
    strAtt = "C:\FastFile\System 3.0\3.0\sampleatt.pdf"
    With oItem
        .Subject = "SAMPLE EMAIL SUBJECT"
        .Attachments.Add strAtt, olByValue, 24
        .Display
        .To = "test2@testing.com"

    End With
Set oItem = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub
cmdEmail_Err:
    DoCmd.Hourglass (False)
    MsgBox "You cancelled and did not send the email", vbExclamation
    DoCmd.SetWarnings True
    
End Sub

[June7]

I've never used Outlook template but my guess, as well as based on what you already experienced, is would have to modify the template. It seems to be an either/or situation.

[Micron]

I've never used this method either, because email had to be sent unattended for the app I built. Could you not just move your template body text to the sub and modify the body? You might have to wrap the body text in HTML tags to control the font.

svName = Forms!frmMyForm.txtFName & " " & Forms!frmMyForm.txtLName
svBody = "Hello " & svName & ";" & vbCrLf
svBody = svBody & 'your template text

With oItem

.Subject = "SAMPLE EMAIL SUBJECT"
.Attachments.Add strAtt, olByValue, 24
.Display
.To = "test2@testing.com"

.Body = svBody

End With

[ItsMe]

Maybe the .Body property is readable.

svBody = oItem.Body
svBody = "Hello " & svName & ";" & vbCrLf & svBody

But, maybe a template is not the best solution in this case ...

[russ0670]

Maybe the .Body property is readable.

svBody = oItem.Body
svBody = "Hello " & svName & ";" & vbCrLf & svBody

But, maybe a template is not the best solution in this case ...

This does put the dynamic greeting line above the template but the template looses all formatting and repeates the template content twice.

Using the templates is unfortunatly essential as there are about 20 stock emails to send to a client depending on their status that the user needs to select and these templates need to be able to be edited easily on occassion.

[ItsMe]

I will guess there is a way to have Access build the mail message in a way that matches your template. If there is a way to make your template dynamic, I am not seeing how and it is seeming that building the entire mail message will be the path of least resistance.

[Micron]

Maybe the .Body property is readable.

svBody = oItem.Body
svBody = "Hello " & svName & ";" & vbCrLf & svBody

But, maybe a template is not the best solution in this case ...

Good idea and close. As I already mentioned, if you've lost your formatting, you can try wrapping your body text in HTML tags. I may not have it exactly right here, and may not have the correct document type specification. The tags I used are deprecated in favour of CSS, so it is probably necessary to use the right type. Also, I replaced the double quotes with single in the specification. If that's an issue, you will have to parse the strings and use the correct character code for the quotes.

svTagStart = "<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'> & vbcrlf
svTagStart = svTagStart & "<HTML><HEAD><BODY><FONT face='Arial'>" & vbCrLf
svTagEnd = "</FONT></BODY></HEAD></HTML>
svBody = svTagStart & "Hello " & svName & ";" & vbCrLf & oItem.Body & svTagEnd
oItem.Body = svBody

[ItsMe]

Good idea and close. As I already mentioned,..

Seems like a lot of work to recreate something. Why not just create it, one time? This way the application is not dependent on an external file, directory, resource, etc.

[russ0670]

Having the templates hard coded would not be an option as it would require a member from IT going in and adjusting the code (and every client front end) every time we update a standard template (there are lots of them). I used .html body and used html tags as Micron suggested referencing the template with a short intro text using form fields. Solved it. Now the system uses templates from a server directory and updating a template is as simple as overwriting the outlook template file in that one location.

Thanks everyone +++

[ItsMe]

Having the templates hard coded would not be an option...

First, I am glad you found a solution. However, I thought the issue was that your templates were hard coded and you were unable to edit them. From my perspective, I would use code to dynamically create the email. This would allow for the most flexibility and avoid issues where you would need many templates or be restricted to using only the templates.