creating HTML code for email

gmayor · #5 05-17-2021, 08:30 PM

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.

Code:

Public Sub CreateEmail()
'Graham Mayor - https://www.gmayor.com - Last updated - 18 May 2021
'Requires the code - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'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
        .Display
        .To = "someone@somewhere.com"
        .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
lbl_Exit:
    Set wdRange = Nothing
    Set wdDoc = Nothing
    Set olInsp = Nothing
    Set olMail = Nothing
    Set olApp = Nothing
    Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using VBA to find html img code, and insert images dynamically	noslenwerd	Word VBA	3	01-02-2020 02:14 PM
how to add voting buttons in email content html code	nikyc	Outlook	1	11-21-2019 11:50 PM
Macro Help Swapping HTML Code	pclark2	Word VBA	2	02-11-2019 03:25 PM
Search and replace/insert HTML code into Master File using tags	dave8555	Excel	2	02-23-2014 03:51 PM
Strange HTML code inside an e-mail	Joostdegrote	Outlook	0	09-13-2010 07:57 AM