Thread: [Solved] creating HTML code for email
View Single Post
Old 05-17-2021, 08:30 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2019
Join Date: Aug 2014
Posts: 3,560
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold

For multiple messages, as Paul suggests, mailmerge is the obvious approach - see E-Mail Merge Add-in

For individual messages rather than use html codes, the better approach is to use the Outlook word editor. You will need to copy a function from the web page indicated in the code to ensure Outlook is started correctly.

Using this method is simpler as it does not require long strings of html code, and is similar to VBA programming in Word.

Insert the appropriate Excel ranges for name, e-mail address and invoice number in place of the fixed values in the code.

Public Sub CreateEmail()
'Graham Mayor - - Last updated - 18 May 2021
'Requires the code -
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
Dim olApp As Object
Dim olMail As Object        ' Outlook.MailItem
Dim olInsp As Object        ' Outlook.Inspector
Dim wdDoc As Object        ' Word.Document
Dim wdRange As Object        ' Word.Range

    Set olApp = OutlookApp()
    Set olMail = olApp.CreateItem(0)
    With olMail
        .BodyFormat = 2
        .To = ""
        .Subject = "Message Subject"
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set wdRange = wdDoc.Range
        With wdRange
            .collapse 1
            .Font.Color = RGB(0, 0, 0)
            .Font.Size = 11
            .Font.Bold = False

            .Text = "Hi, " & "Name of person" & vbCr & vbCr & _
                    "Here is your invoice # " & "123456"
            .collapse 0

            .Text = " PAID "
            .Font.Color = RGB(255, 0, 0)
            .Font.Size = 16
            .Font.Bold = True
            .collapse 0

            .Text = "more text and end of email"    'signature associated with account is retained.
            .Font.Color = RGB(0, 0, 0)
            .Font.Size = 11
            .Font.Bold = False
        End With
    End With
    Set wdRange = Nothing
    Set wdDoc = Nothing
    Set olInsp = Nothing
    Set olMail = Nothing
    Set olApp = Nothing
    Exit Sub
End Sub
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes

Last edited by gmayor; 05-17-2021 at 11:10 PM.
Reply With Quote