Looking how to write HTML Email body within MS Access VBA

[03Explorer]

I am building an Access application that needs to send out an email. I would rather the email be formatted vs straight text. I am uncertain how to make a static HTML email within VBA. Can someone help either with samples or point fingers to some good FAQ/s? I have Redemption installed (not sure if that matters). Goal is to have this look as professional as possible (hence HTML(RTF) vs straight text).

This is new to me (making Emails HTML). I have very little experience with building emails (straight text) as well.

THANK YOU!

I have this posted on another Forum Link

 

[sxschech]

Check out this site. Has an example of html email body text and how to add signature.

http://www.rondebruin.nl/win/s1/outlook/signature.htm

Here is a sample of how I used it (see stBody in the code). Since you mentioned "static", if you need more html formatting, you could probably create the text in an html editor and copy into the vba stbody variable by adding quotes around the text and html tag statements.

Private Sub btnSendEmail_Click()
'Code to add signature to email
'[URL unfurl="true"]http://www.rondebruin.nl/win/s1/outlook/signature.htm[/URL]
'20160826
    Dim objOutlook As Object    'Use for late binding
    Dim objNameSpace As Object  'Use for late binding
    Dim MailOutLook As Object   'Use for late binding
    Dim stBody As String
    Dim SigString As String
    Dim Signature As String
    Dim stCC As String
    Dim stSubject As String
    Dim stsql As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim yesno
    
    'Check if should send an email if an email was previously sent
    'for this document number
    stlogdate = DMax("sentdate", "tblEmailLog", "documentnumber='" & Forms!frm.DocumentNumber & "'")
    If Not IsNull(stlogdate) Then
        yesno = MsgBox("An email for " & Me.txtDocumentNumber & " was already sent on " & stlogdate & ".  Would you like to send a new email? " & vbCrLf & vbCrLf, vbYesNo + vbQuestion, "Previously Emailed")
        If yesno = vbNo Then
            MsgBox "Email cancelled.", vbOKOnly + vbInformation, "Email Aborted"
            GoTo Finished
        End If
    End If
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblEMailLog")
    
    '*************************************************
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    
    If objOutlook Is Nothing Then
        Set objOutlook = CreateObject("Outlook.Application")
    End If
    '*****************************************************
    
    Set MailOutLook = objOutlook.CreateItem(0)  'Late binding method
    
    stSubject = Me.txtUCASEFilename
    stBody = "Hi " & ",<br><br>" & _
             "I am assisting in uploading the drawings and " & _
             "would like to know how to handle the drawings based on the comment " & _
             "file that indicate XXX on the attached file. <br><br>" & _
             "Please let me know when you have a chance.<br><br> " & _
             "Thanks,<br>"
        
    'Change only Mysig.htm to the name of your signature
    SigString = Environ("appdata") & _
                "\Microsoft\Signatures\MySig.htm"
    
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
    
    On Error Resume Next
    
    With MailOutLook
        .BodyFormat = 3      'Late binding in lieu of olFormatRichText
        .To = ConcatRelated("email", "tblEmail", "distributiontype='To'", , ";") '"YourEmail@Email.com"
        .CC = ConcatRelated("email", "tblEmail", "distributiontype='cc'", , ";")
        '.bcc = ""
        .Subject = stSubject 'Me.txtUCASEFilename
        .HTMLBody = stBody & "<br>" & Signature
        '.Send
        .Display  'Use for testing in lieu of .Send
    End With
    On Error GoTo 0
    
    'Add record to email log
    rs.AddNew
    rs!DocumentNumber = Me.DocumentNumber
    rs!SentTo = ConcatRelated("FullName", "tblEmail", "distributiontype='To'", , "; ")
    rs!SentDate = Now()
    rs.Update
    frmProCore_Sub.Requery
Finished:
    Set MailOutLook = Nothing
    Set objOutlook = Nothing
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[03Explorer]

Thank you sxschech!

I ended up with a bad workaround. I pulled out the source code of a formatted email from the outlook incoming message. Pasted that into a text editor (notepad) and then built the html details... here is a snippet of what I got. The message is formatted to be Arial Pt10 with some bullet points and hyperlinks

                txtBody = "<!DOCTYPE html><html><body>"
                txtBody = txtBody & "<div style=""font-family:'Arial';font-size: 14px;"">"
                txtBody = txtBody & "Attached is a proficiency assessment workbook for a client project that has been selected for review. Please note the following:"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "Complete the entire workbook before submitting.</span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'></span></p>"
                txtBody = txtBody & "<ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "Do NOT change the name of the file or the structure of the file-naming convention. The system will not accept your evaluation if the file name is changed.</span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "Do NOT change the formatting of the file in any manner. This will cause the submission to be rejected.</span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "Return the completed workbook to <a href='mailto:Proficiency@sullivancotter.com'>Proficiency@cpompany.com</a>.</span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "If you are unable to assess a specific skill included in the survey, please use N/A as your selection option.</span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "When selecting a rating of 3 or below, please strive to include <b><u>constructive</u></b> feedback in the open comments field to help drive clarity on what the associate can do to improve this skill area. </span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "Note<i>: </i></span></b><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "Growth Roles will be added as the content is finalized.</span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "Questions?</span></b></p>"
                txtBody = txtBody & "<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:'Arial',sans-serif'></span>"
                txtBody = txtBody & "</b></p><ul style='margin-top:0in' type=disc><li class=MsoNormal style='mso-list:l0 level1 lfo1'>"
                txtBody = txtBody & "<span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "Proficiency model process, tool, timeline, etc.:&nbsp; <a href='mailto:HR-ServiceCenter@cpompany.com'>HR-ServiceCenter@cpompany.com</a></span></li></ul>"
                txtBody = txtBody & "<p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><ul style='margin-top:0in' type=disc>"
                txtBody = txtBody & "<li class=MsoNormal style='mso-list:l0 level1 lfo1'><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "Connectivity to network questions:&nbsp; <a href='mailto:ITSupport@company.com'>ITSupport@company.com"
                txtBody = txtBody & "</a>&nbsp; </span></li></ul><p class=MsoNormal><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "</span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:'Arial',sans-serif'>"
                txtBody = txtBody & "Click <a href='[URL unfurl="true"]https://company.sharepoint.com/sites/kc/shared-services/human-resources/prof-development/Pages/proficiency.aspx'>here"[/URL]
                txtBody = txtBody & "</a> for more information (including training videos and reference guides).</span></p></div></body></html>"

I am certain there is a MUCH cleaner coding than this... Quite frankly this really is *IMO* too much coding for what is needed for the output. Again, that is my non expert HTML assumption.

 

[lameid]

For all things html and most things web I recommend w3schools.com.

You can also make a Rich Text Field in an access table, format text there and then read that text out. There is way more flexibility in e-mail than there is in Access RTF (a subset of HTML 1.0 vs. whatever the latest HTML version is... I want to say 5 of this writing) but it is a native way to do it. You can also save Word Docs to HTML and look at the resulting file but I don't remember if there is any extra fluff in a file saved this way or not. Once you get the basics of HTML down, reaching how do I do this thing questions are a lot easier.

 

[strongm]

>Access RTF

Just to be pedantic here - the HTML-based Rich Text support in Access is not RTF. RTF is a somewhat different beast

 

[lameid]

strongm, good point.... Early on I somehow connected Access's "Rich Text" text format to Rich Text Format (RTF) and the idea had not broken. Too bad you weren't there to straighten me out then... It may have saved me a learning curve. Those false connections are always the most obnoxious.

 

[sxschech]

I took the data you provided and stripped out the txtBody stuff and saved it with an html extension then used code to read it into the body.
I attached a screen shot. If this is more or less how it should look, then here is the code to add. You should be able to create your html file visually with
whatever program can save as html. You no longer have to embed and concatenate the html in the vba code in order to have it show up in the email body as long as
the CSS and Class stuff is self contained (as is the case with your sample code).
I tried doing a save as from the website that I got the code from and it doesn't render everything due to the file saving with the CSS and Class stuff in
a separate folder. If using the code in my previous post, this goes after the last DIM

    '---------------------
    'Code to read text file for body
    '[URL unfurl="true"]http://codevba.com/office/read_text_file_into_string_variable.htm#.W3RTfNJKiUl[/URL]
    Dim strFilename As String
    strFilename = "C:\TEMP\tektips.html"
    Dim strFileContent As String
    Dim iFile As Integer: iFile = FreeFile
    Open strFilename For Input As #iFile
    strFileContent = Input(LOF(iFile), iFile)
    Close #iFile
stBody=strFileContent
    '--------------------

You could then modify the code to have variable for the file name so that you don't have to hard code it; allowing you to have
different html files for different email scenarios.

Private Sub btnSendEmail_Click(bodyfile as string)

...
...
strFilename = bodyfile
...