Need software to perform Excel to HTML mailmerge

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.
 
M

moi

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
 
M

M.L.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract specific information from the body of outlook mail to an Excel File using VBA 0
Html Tags for Specific Words in a Cell 1
Generating a excel document 1
Excel Send Lotus Notes Mails With VBA Macro Excel 0
Search for a sum! 4
gridlines toggled off when importing html table 0
Help with special Excel mail merge... 1
Hello...This thread is a continuation of one from 2003...pasted below: 2

Top