HTML Emails from Access

[Dave G]

Apologies if this has been covered before - I couldn't find it.

I currently use ASPEmail to create and send HTML emails from an Access
database. The text is personalised and includes embedded graphics, eg
logos and even the recipients photo. Note that everything is embedded,
no attachments are sent. ASPEmail works a treat but is very long winded
to create. The email designs come to me as finished HTML but I have to
extract the source and convert it to a string - not that simple a
process - and write quite a bit of code around it to actually get the
thing to work.

What would be nicer would be to do something like I already do for word
documents, that is, set up a template with bookmarks and use automation
to bring up the document, put in the data fields and print it. Is there
something like this I could use with Outlook?

Or does anyone know if there is a better way to do this? I'm happy to
pay for a 3rd party solution.

Thanks
Dave

[Lyle Fairfield]

Dave G @ K2 wrote:

Apologies if this has been covered before - I couldn't find it.

I currently use ASPEmail to create and send HTML emails from an Access
database. The text is personalised and includes embedded graphics, eg
logos and even the recipients photo. Note that everything is embedded,
no attachments are sent. ASPEmail works a treat but is very long winded
to create. The email designs come to me as finished HTML but I have to
extract the source and convert it to a string - not that simple a
process - and write quite a bit of code around it to actually get the
thing to work.

What would be nicer would be to do something like I already do for word
documents, that is, set up a template with bookmarks and use automation
to bring up the document, put in the data fields and print it. Is there
something like this I could use with Outlook?

Or does anyone know if there is a better way to do this? I'm happy to
pay for a 3rd party solution.

Thanks
Dave

Consider the code below. If it were to be used extensively it would be
wise to rewrite it using parameters for such things as the html file
path. Probably, an error handler should be included which makes certain
the low-level DOS File is closed, even if some other problem terminates
the execution of the code. (In the olden days, unclosed low-level DOS
files could haunt one's computer for a very long time, causing all
sorts of havoc. I don't know if this is still true.) I expect that you
already know that in HTML E-Mail one may have to use absolute hrefs,
srcs etc in img and other tags etc. Alts are a good idea too.

If the HTML file has distinct placeholders, such a "PlaceHolderOne ",
one can do simple Replaces to make each send unique. I have done this
previously when posting code.

Public Sub VerySimpleSendH TMLMailWithCDOS ample()

Dim iCfg As Object
Dim iMsg As Object

Dim FileNumber As Integer
Dim Buffer As String

FileNumber = FreeFile()
Open "C:\Documen ts and Settings\Lyle Fairfield\My
Documents\FFDBA Backup\index.ht ml" For Binary As #FileNumber
Buffer = String(LOF(File Number), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber

Set iCfg = CreateObject("C DO.Configuratio n")
Set iMsg = CreateObject("C DO.Message")

With iCfg.Fields
..Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = 2
..Item("http://schemas.microso ft.com/cdo/configuration/smtpserverport" )
= 25
..Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") =
"smtp.aim.c om"
..Item("http://schemas.microso ft.com/cdo/configuration/smtpauthenticat e")
= 1
..Item("http://schemas.microso ft.com/cdo/configuration/sendusername") =
"eugenevdeb s"
..Item("http://schemas.microso ft.com/cdo/configuration/sendpassword") =
"Sol1dar1ty4Eve r"
..Item("http://schemas.microso ft.com/cdo/configuration/sendemailaddres s")
= "Eugene V Debs <eu*********@ai m.com>"
..Update
End With

With iMsg
..Configuration = iCfg
..HTMLBody = Buffer
..Subject = "Here's Some HTML"
..To = "lf********@cog eco.ca"
..Send
End With

Set iMsg = Nothing
Set iCfg = Nothing

End Sub
****
News clients will introduce extraneous line-feeds in this code.
****
Do I recommend this? I do not! I recommend uploading the HTML file
(personalized or not) to a server and sending a link to that page in
the e-mail. I have code for that too.

[Dave G]

Thanks Lyle

But one of my problems is the subject line. Using either your suggested
code, or the system I'm currently using, I need to convert the raw HTML
into a text string, and that's the bit that takes the time because my
client likes designing some pretty long emails loads of tables, font
changes etc etc. I have it all sussed, putting in graphics and
variables and so on, and it works beautifully. I'm just trying to find
a short cut really.

Dave
Lyle Fairfield wrote:

Dave G @ K2 wrote:

Apologies if this has been covered before - I couldn't find it.

I currently use ASPEmail to create and send HTML emails from an Access
database. The text is personalised and includes embedded graphics, eg
logos and even the recipients photo. Note that everything is embedded,
no attachments are sent. ASPEmail works a treat but is very long winded
to create. The email designs come to me as finished HTML but I have to
extract the source and convert it to a string - not that simple a
process - and write quite a bit of code around it to actually get the
thing to work.

What would be nicer would be to do something like I already do for word
documents, that is, set up a template with bookmarks and use automation
to bring up the document, put in the data fields and print it. Is there
something like this I could use with Outlook?

Or does anyone know if there is a better way to do this? I'm happy to
pay for a 3rd party solution.

Thanks
Dave

Consider the code below. If it were to be used extensively it would be
wise to rewrite it using parameters for such things as the html file
path. Probably, an error handler should be included which makes certain
the low-level DOS File is closed, even if some other problem terminates
the execution of the code. (In the olden days, unclosed low-level DOS
files could haunt one's computer for a very long time, causing all
sorts of havoc. I don't know if this is still true.) I expect that you
already know that in HTML E-Mail one may have to use absolute hrefs,
srcs etc in img and other tags etc. Alts are a good idea too.

If the HTML file has distinct placeholders, such a "PlaceHolderOne ",
one can do simple Replaces to make each send unique. I have done this
previously when posting code.

Public Sub VerySimpleSendH TMLMailWithCDOS ample()

Dim iCfg As Object
Dim iMsg As Object

Dim FileNumber As Integer
Dim Buffer As String

FileNumber = FreeFile()
Open "C:\Documen ts and Settings\Lyle Fairfield\My
Documents\FFDBA Backup\index.ht ml" For Binary As #FileNumber
Buffer = String(LOF(File Number), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber

Set iCfg = CreateObject("C DO.Configuratio n")
Set iMsg = CreateObject("C DO.Message")

With iCfg.Fields
.Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microso ft.com/cdo/configuration/smtpserverport" )
= 25
.Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") =
"smtp.aim.c om"
.Item("http://schemas.microso ft.com/cdo/configuration/smtpauthenticat e")
= 1
.Item("http://schemas.microso ft.com/cdo/configuration/sendusername") =
"eugenevdeb s"
.Item("http://schemas.microso ft.com/cdo/configuration/sendpassword") =
"Sol1dar1ty4Eve r"
.Item("http://schemas.microso ft.com/cdo/configuration/sendemailaddres s")
= "Eugene V Debs <eu*********@ai m.com>"
.Update
End With

With iMsg
.Configuration = iCfg
.HTMLBody = Buffer
.Subject = "Here's Some HTML"
.To = "lf********@cog eco.ca"
.Send
End With

Set iMsg = Nothing
Set iCfg = Nothing

End Sub
****
News clients will introduce extraneous line-feeds in this code.
****
Do I recommend this? I do not! I recommend uploading the HTML file
(personalized or not) to a server and sending a link to that page in
the e-mail. I have code for that too.

[Rick Brandt]

Dave G @ K2 wrote:

Thanks Lyle

But one of my problems is the subject line. Using either your
suggested code, or the system I'm currently using, I need to convert
the raw HTML into a text string, and that's the bit that takes the
time because my client likes designing some pretty long emails loads
of tables, font changes etc etc. I have it all sussed, putting in
graphics and variables and so on, and it works beautifully. I'm just
trying to find a short cut really.

Put the base "template" HTML in a memo

[Rick Brandt]

Dave G @ K2 wrote:

Thanks Lyle

But one of my problems is the subject line. Using either your
suggested code, or the system I'm currently using, I need to convert
the raw HTML into a text string, and that's the bit that takes the
time because my client likes designing some pretty long emails loads
of tables, font changes etc etc. I have it all sussed, putting in
graphics and variables and so on, and it works beautifully. I'm just
trying to find a short cut really.

Put the base "template" HTML in a memo field of a table and insert markers into
it. I use things like {Some_Data_Fiel d_Name}. When constructing the EMail just
use several iterations of the Replace() function to insert your real data and
then stuff the result into the Email.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Lyle Fairfield]

There is no substantive difference between an HTML string and any other
string. We call the one an HTML string because it contains HTML tags;
when this string is sent to a browser, the browser interprets these
tags and shows the results of wonderful tricks the instructions for
which are determined by the tag.
We can save the string as a file, or in a sufficiently large field. We
can retrieve the string and we can send that to an e-mail program. The
only difference between having the e-mail message rendered as HTML and
having it rendered in plain text is the instruction we give to the
e-mail program.
Almost all coding languages have a replace function which can be used
to modify any string, including an HTML string.

[Larry Linson]

"Dave G @ K2" wrote

I need to convert the raw HTML
into a text string, and that's the bit
that takes the time because my
client likes designing some pretty
long emails loads of tables, font
changes etc etc.

Help me understand: I have always been under the impression that "raw HTML"
_IS_ a text string.

Do you mean you need to find just the _content_ and extract it from the
formatting markup as a plain text string?

Larry Linson
Microsoft Access MVP

[Dave G]

I'll try to explain - HTML is indeed a text string, but if you look at
a web page, then view source, then copy and paste the source into an
Access module and assign it to a string variable, then the mail program
fails. It fails because you need to take care of all the places where
quote marks are used in normal html

eg html may look like this: <table width="33">
assign that to a text variable s gives
s = "<table width="33">"

but to make it work you need
s = "<table width=""33"">"

Similarly:
s = "<font color=#" & "123456>"

OK, that's not too hard but with a lot of complex html it becomes a
real pain to have to go through it all and make the changes. I'm trying
to find an alternative method as what I'm given is a perfect finished
page of html.

Dave

Larry Linson wrote:

"Dave G @ K2" wrote

> I need to convert the raw HTML
> into a text string, and that's the bit
> that takes the time because my
> client likes designing some pretty
> long emails loads of tables, font
changes etc etc.

Help me understand: I have always been under the impression that "raw HTML"
_IS_ a text string.

Do you mean you need to find just the _content_ and extract it from the
formatting markup as a plain text string?

Larry Linson
Microsoft Access MVP

[Rick Brandt]

Dave G @ K2 wrote:

I'll try to explain - HTML is indeed a text string, but if you look at
a web page, then view source, then copy and paste the source into an
Access module and assign it to a string variable, then the mail
program fails. It fails because you need to take care of all the
places where quote marks are used in normal html

eg html may look like this: <table width="33">
assign that to a text variable s gives
s = "<table width="33">"

but to make it work you need
s = "<table width=""33"">"

[snip]

You do not need to do this. I assign long HTML markup to string variables
all the time and I don't have to do anything about the quotes in the HTML.
If you are *building* the string in your code then you do have this issue,
but if you pull the HTML from a file or database record then it is not an
issue. Same is true of long complex SQL strings. If I pull them from a
database field then I don't have to worry about all of the usual quote
delimiting that would be required were I constructing the string inside the
code routine.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Dave G]

Aha !!
I've just realised something - the html source I receive from the
designer has quote marks around every parameter eg <table width="33">.
I now discover that these quote marks are not needed. Apparently they
are good practice, but not essential. So if I get rid of all quote
marks withing the html then my problems are solved. Even better, the
designer is going to strip them all out for me - big sigh of relief.

Dave

Rick Brandt wrote:

Dave G @ K2 wrote:

I'll try to explain - HTML is indeed a text string, but if you look at
a web page, then view source, then copy and paste the source into an
Access module and assign it to a string variable, then the mail
program fails. It fails because you need to take care of all the
places where quote marks are used in normal html

eg html may look like this: <table width="33">
assign that to a text variable s gives
s = "<table width="33">"

but to make it work you need
s = "<table width=""33"">"

[snip]

You do not need to do this. I assign long HTML markup to string variables
all the time and I don't have to do anything about the quotes in the HTML.
If you are *building* the string in your code then you do have this issue,
but if you pull the HTML from a file or database record then it is not an
issue. Same is true of long complex SQL strings. If I pull them from a
database field then I don't have to worry about all of the usual quote
delimiting that would be required were I constructing the string inside the
code routine.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com