Solved

Is it possible to populate a HTML document from an Excel sheet ?

Posted on 2014-01-13
17
334 Views
Last Modified: 2014-01-15
Hi All,

I have an HTML document (index.html) which I use as a mailshot template. I create an email from the document as follows:

1. In Internet Explorer, Select All, Copy and Paste the web page into an Outlook message.
2. Change the name of the recipient (Dear xxxxx) in the body of the text.
3. Add the recipient's email address.
4. Send message

I now want to automate this process from my Mailshot Excel VBA application on the click of a button.

My question is, how can I create a form field or control in my HTML document (similar to a label control in a Word document) which I can populate from my Excel application ?

Thanks
Toco
0
Comment
Question by:Tocogroup
  • 8
  • 8
17 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39775986
Have you ruled out opening the html as text in notepad, copy and paste as text into word.... run it through the mail merge wizard in word, output it as one document and then save it as html again?

Alternatively i think you can have word do an email merge from your html file and your flat file of excel recipients.

finally, 3rd option, use vba code in excel to loop through the recipients list, you should be to read in the html file as a text stream, and replace the bits you need with the values you want , and then send that to an email object in outlook to send it.
0
 

Author Comment

by:Tocogroup
ID: 39776025
Hi,

The email object must be generated from the VBA code within Excel (I've done this with ordinary text emails elsewhere in my application). I want to replace the copy/paste or mailmerge processing with VBA, so your third option is the most appropriate.

How do I.... 'read in the html file as a text stream, and replace the bits you need with the values you want , and then send that to an email object in outlook to send it' ?

I don't want any manual intervention up until the point I want to Send the email.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39776151
I see no reason to use a html document as  a template for emails.

I recommend using CDO in VBA, you can send html as the body of the mail. You can easily construct the html to include the recipient's name. See http://support.microsoft.com/kb/286431 for the general idea.

This is by far the easiest method to automate sending an email.

If you want to go this way, let me know and I'll provide a sample.
0
 

Author Comment

by:Tocogroup
ID: 39776177
Hi MS,

Are there any restrictions in sending the HTML in the body of the email ? I've hand-coded the HTML because I am aware that mail programs are notoriously finicky when it comes to rendering HTML in an email message.

If the HTML body can include images from my web server and hyperlinks then this is the way to go. Do you have a sample ?

Thanks
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39776572
Instructions:

1. Update the values on Sheet1 to reflect real data.
2. Open your html file insert @NAME@ where you want the name.
3. Run the Demo macro.

Unfortunately, you will have to use an existing file with your html (due to a Ms shortcoming; only 24 line continuation characters allowed) .

Attached you will find proof of concept.
Sample.xlsm
test.txt
0
 

Author Comment

by:Tocogroup
ID: 39777011
Ok, I think you're going to have to talk me through this one.
Firstly, I've never seen a sheet with reversed headings before (see attached screenshot).
How do I revert them to 'normal' column headings reading A-Z from left to right ?
Sample-application-Screenshot.jpg
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39777907
Ok, where are you having a problem?
Sample.xlsm
0
 

Author Comment

by:Tocogroup
ID: 39778547
Ok thanks, that's the first problem sorted. Next.....

Do I save my .html file as a .txt file, such as yours ?

Is the User name/password combination on the sheet required for FTP access ?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 26

Expert Comment

by:MacroShadow
ID: 39778678
Do I save my .html file as a .txt file, such as yours ?
No need.

Is the User name/password combination on the sheet required for FTP access ?
The username/password are your email account info for sending the email.
0
 

Author Comment

by:Tocogroup
ID: 39781949
Ok.....next issue.

When I run the macro Demo it returns a 'Run time error 52 - Bad file name or number' on the line...

    Open strFile For Input As #1     ... of the ReturnTextFile function

In debug I can see the variable strFile is pointing at the valid URL...
http://www.tocotraining.co.uk/email/SummarisingYourData/index.html

Is there something I've omitted ? My mailbox Username/Password combination are valid.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39781961
It has to be on your local machine. If the html is on your server you will need a different function.

Replace the ReturnTextFile function with this one, don't forget to change all occurrences of ReturnTextFile with ReturnHtmlFromUrl:

Public Function ReturnHtmlFromUrl (ByVal strURL) As String

    On Error GoTo ErrorHandler

    Dim strError As String
    Dim strResponse As String

    strError = ""
    strResponse = ""

    With CreateObject("MSXML2.XMLHTTP")

        .Open "GET", strURL, False
        .send ""

        If .Status <> 200 Then
            strError = .statusText
            GoTo CleanUpAndExit
        Else

            If .getResponseHeader("Content-type") <> "text/html" Then
                strError = "Not an HTML file"
                GoTo CleanUpAndExit
            Else
                strResponse = .responseText
            End If

        End If

    End With

CleanUpAndExit:

    On Error Resume Next ' Avoid recursive call to error handler

    ' Clean up code goes here
    Set oXMLHTTP = Nothing

    ' Report any error
    If Len(strError) > 0 Then
        MsgBox strError
    Else
        ReturnHtmlFromUrl = strResponse
    End If

    Exit Sub

ErrorHandler:

    strError = Err.Description

    Resume CleanUpAndExit

End Function

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39781987
Thanks.

Ok.....next, I'm getting a compiler error message : 'User-defined type not defined' against the following line.....

Dim cdoMsg As CDO.Message

Is this now redundant ?
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782098
Either add a reference to the CDO library. See the attachment... or change the following line:
Dim cdoMsg As CDO.Message

Open in new window

to
Dim cdoMsg As Object

Open in new window

em.jpg
0
 

Author Comment

by:Tocogroup
ID: 39782435
Hi

Following the Send, it's returning a debug message of...

'At least one recipient is required, but none were found.'

Prior to the Send the variables are set as follows:

strSubject = "Sample letter"
strServer = the full HTML code, including the replaced name
strServer = "smtp.e-mazingnames.com"
intPort = 465
strFromUsername = my mailbox user name
strPassword = my mailbox password
intSendUsing = 2
intAuthenticate = 1
blnUseSSL = True
intTimeout = 10

Any ideas ?
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39782471
1. What is the value of the strTo variable? it should contain a valid email address.

2. The email settings are intended for use with gmail. For your server please check the requirements for:
a. Port : The SMTP Port which must be enabled in your network by ISP or local Firewall
b. SendUsing: Specifies the method used to send messages: '(1) Local SMTP Pickup Service (2) Use SMTP Over Network (3) Use Exchange Server
c. Authenticate: Specifies the authentication mechanism to use when authentication is required to send messages to an SMTP service using a TCP/IP network socket:  (1) None (2) Basic (Base64 encoded) (3) NTLM
d. UseSSL: Indicates whether Secure Sockets Layer (SSL) should be used when sending messages using the SMTP protocol over the network or not.
0
 

Author Closing Comment

by:Tocogroup
ID: 39782816
That's great. I got around the email send problems by amending an existing email procedure I had in another workbook.
The name replace and HTML body display works fine.

Many thanks for your patience and comprehensive explanations.

Regards
Toco
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782868
Glad to have been of assistance.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now