How can I convert a Word document to HTML email (VBA)

[SidCharming]

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). I am attaching a copy of the message that is being sent out. 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!

[Homegrownandy]

What are you using from your access DB? are you putting in fields from your DB or is it the same block of text for everyone of them.

[SidCharming]

What are you using from your access DB? are you putting in fields from your DB or is it the same block of text for everyone of them.

As of now, it is block text. But based on business, it could end up with some DB fields... In that case, I think I could figure out the how-to. It's the initial design that I need help.

Thanks HomeGrownAndy!! =)

[SidCharming]

I was reading another forum where a topic seems to be (or has been) an issue. I have an outlook email that was formatted and sent to me. I copied it and pasted into Word; thinking I can see the HTML code so i can steal it from there. The other forum has conversations that Word messes with HTML enough that it in of itself is plagued with issues (because Word puts some unnecessary stuff in there).

Is that true? If yes... what is a better tool to generate the HTML code (to be put into VBA)? Another thought is I am overcomplicating the issue because I could get the source code from Outlook.

[SidCharming]

I posted the question to Tek-Tips also, looking for insight https://www.tek-tips.com/viewthread.cfm?qid=1788568

[Homegrownandy]

I generate emails in my app, I can show you the code I use:

Private Sub Command73_Click()

    Dim appOutLook As Object
    Dim MailOutLook As Object
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(0) '(olMailItem)
    With MailOutLook
    .Body = Me!Site_Name & " " & Me!Asset_Type & vbCrLf & Me!Address_1 & vbCrLf & Me!Address_2 & vbCrLf & Me!Address_3 & vbCrLf & Me!Postcode & vbCrLf & "lattitude:" & Me!lat & "longitude:" & Me!long & vbCrLf & vbCrLf & vbCrLf & "Hospital Details:" & vbCrLf & Me!Hospital_Name & vbCrLf & Me!Hospital_Address & vbCrLf & Me!Hospital_Postcode & vbCrLf & "Tel: " & Me!Hospital_Telephone
    .Subject = Me!Site_Name & " Details"
    .To = "test"

        '~~> Do what you want
      .Display
   End With
Set appOutLook = Nothing
Set MailOutLook = Nothing
End Sub

[Homegrownandy]

its basically an email that will show an address for place of works. It's generated using fields on the form.

[Minty]

Something like the codes used below will probably help;

        Dim sEmBody  as String
        Dim sHTMLEmBody  as String
 
'email body plain text
        sEmBody = ""
        sEmBody = sEmBody & "Sales Invoice " & sInvNo & " to " & sCustName & vbCrLf & vbCrLf
        sEmBody = sEmBody & "Dear Sir / Madam," & vbCrLf & vbCrLf
        sEmBody = sEmBody & "Please find attached our invoice (reference " & sInvNo & "), your reference " & sCustOrderNo & "." & vbCrLf & vbCrLf
        sEmBody = sEmBody & "Invoices are now sent electronically as PDF files. If this is not the correct email address for invoices or " & vbCrLf
        sEmBody = sEmBody & "if you have any questions regarding the invoice please contact us using the details below."


'email body HTML
        sHTMLEmBody = ""
        sHTMLEmBody = sHTMLEmBody & "<p>" & "Sales Invoice " & sInvNo & " to " & sCustName & "</p>" & vbCrLf
        sHTMLEmBody = sHTMLEmBody & "<p style='Font-Family:Arial'>" & "Dear Sir / Madam," & "</p>" & vbCrLf & vbCrLf
        sHTMLEmBody = sHTMLEmBody & "<p style='Font-Family:Arial'>" & "Please find attached our invoice (reference " & sInvNo & "), your reference " & sCustOrderNo & "." & "</p>" & vbCrLf & vbCrLf
        sHTMLEmBody = sHTMLEmBody & "<p style='Font-Family:Arial'>" & "Invoices are now sent electronically as PDF files. " & vbCrLf
        sHTMLEmBody = sHTMLEmBody & "If this is not the correct email address for invoices or, if you have any questions regarding the invoice, please contact us using the details below." & "</p>"

Use the .HTMLBody = sHTMLEmBody in your outlook email code.

Obviously I've used a couple of String variables to hold customer specific text that is concatenated in with the verbiage.

[SidCharming]

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 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='https://company.sharepoint.com/sites/kc/shared-services/human-resources/prof-development/Pages/proficiency.aspx'>here"
                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 method 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.

[Minty]

Unfortunately, I don't think you can use CSS style sheets, as that would reduce the amount of code quite a lot.

You could store the various paragraph string texts in a table, that would allow you create it another program(dedicated HTML editor), and simply paste it into your storage table, then look up the stored text.
Might save a lot of typing.