Need software to perform Excel to HTML mailmerge

  • Thread starter Thread starter M.L.
  • Start date Start date
M

M.L.

Could Excel VBA be used to create a macro that performs a mailmerge
between Excel and an HTML file? For example, I want to set tags in
HTML that will be replaced by corresponding Excel cell data. One HTML
file will be created for each row of Excel data.

<body>
The temperature is <C:23> degrees.
</body>

I'd like that tag replaced with the cell text value in C:23. I would
like image replacement tags to be replaced by the actual image, not
the text in its cell.

Any help here would be appreciated. Thanks.
 
Option Explicit
'reads the full contents of a html file, splits it up in two parts;
'a part until <body> and a part from </body>, puts a new value in between
'and (over)writes the whole bunch to the html file
'can probably be done more compact, but at least i had fun
Public Sub ReadAndWriteHTML()
Dim FSO, htmlFile
Set FSO = CreateObject("Scripting.FileSystemObject")
Set htmlFile = FSO.OpenTextFile("fullpath.html", 1)
Dim htmlRead As String, htmlWrite1 As String, htmlWrite2 As String,
inString As String
htmlRead = htmlFile.ReadAll
htmlFile.Close
'construct your tag here
inString = " The temperature is 60 degrees "
Dim i As Integer, l As Integer, k As Integer
l = Len(htmlRead)
htmlWrite1 = vbNullString
htmlWrite2 = vbNullString
For i = 1 To l Step 1
If Mid(htmlRead, i, 6) <> "<body>" Then
htmlWrite1 = htmlWrite1 & Mid(htmlRead, i, 1)
ElseIf Mid(htmlRead, i, 6) = "<body>" Then
htmlWrite1 = htmlWrite1 & Mid(htmlRead, i, 6)
k = i
i = l
End If
Next i
For i = k To l Step 1
If Mid(htmlRead, i, 7) = "</body>" Then
htmlWrite2 = htmlWrite2 & Mid(htmlRead, i, l)
End If
Next i
htmlWrite1 = htmlWrite1 & inString & htmlWrite2
Set htmlFile = FSO.OpenTextFile("fullpath", 2)
htmlFile.WriteLine (htmlWrite1)
htmlFile.Close
End Sub
 
Option Explicit
'reads the full contents of a html file, splits it up in two parts;
'a part until <body> and a part from </body>, puts a new value in between
'and (over)writes the whole bunch to the html file
'can probably be done more compact, but at least i had fun
Public Sub ReadAndWriteHTML()
Dim FSO, htmlFile
Set FSO = CreateObject("Scripting.FileSystemObject")
Set htmlFile = FSO.OpenTextFile("fullpath.html", 1)
Dim htmlRead As String, htmlWrite1 As String, htmlWrite2 As String,
inString As String
htmlRead = htmlFile.ReadAll
htmlFile.Close
'construct your tag here
inString = " The temperature is 60 degrees "
Dim i As Integer, l As Integer, k As Integer
l = Len(htmlRead)
htmlWrite1 = vbNullString
htmlWrite2 = vbNullString
For i = 1 To l Step 1
If Mid(htmlRead, i, 6) <> "<body>" Then
htmlWrite1 = htmlWrite1 & Mid(htmlRead, i, 1)
ElseIf Mid(htmlRead, i, 6) = "<body>" Then
htmlWrite1 = htmlWrite1 & Mid(htmlRead, i, 6)
k = i
i = l
End If
Next i
For i = k To l Step 1
If Mid(htmlRead, i, 7) = "</body>" Then
htmlWrite2 = htmlWrite2 & Mid(htmlRead, i, l)
End If
Next i
htmlWrite1 = htmlWrite1 & inString & htmlWrite2
Set htmlFile = FSO.OpenTextFile("fullpath", 2)
htmlFile.WriteLine (htmlWrite1)
htmlFile.Close
End Sub
Click to expand...

Wow! Thanks a heap. Unfortunately I don't understand the code since
I'm a newbie at VBA, but I'll certainly study it and try it out.
Thanks again.
 
Back
Top