By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,115 Members | 919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,115 IT Pros & Developers. It's quick & easy.

HTML Emails from Access

P: n/a
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

Jun 9 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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 VerySimpleSendHTMLMailWithCDOSample()

Dim iCfg As Object
Dim iMsg As Object

Dim FileNumber As Integer
Dim Buffer As String

FileNumber = FreeFile()
Open "C:\Documents and Settings\Lyle Fairfield\My
Documents\FFDBABackup\index.html" For Binary As #FileNumber
Buffer = String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber

Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")

With iCfg.Fields
..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp.aim.com"
..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
..Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"eugenevdebs"
..Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"Sol1dar1ty4Ever"
..Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= "Eugene V Debs <eu*********@aim.com>"
..Update
End With

With iMsg
..Configuration = iCfg
..HTMLBody = Buffer
..Subject = "Here's Some HTML"
..To = "lf********@cogeco.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.

Jun 9 '06 #2

P: n/a
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 VerySimpleSendHTMLMailWithCDOSample()

Dim iCfg As Object
Dim iMsg As Object

Dim FileNumber As Integer
Dim Buffer As String

FileNumber = FreeFile()
Open "C:\Documents and Settings\Lyle Fairfield\My
Documents\FFDBABackup\index.html" For Binary As #FileNumber
Buffer = String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber

Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")

With iCfg.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp.aim.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"eugenevdebs"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"Sol1dar1ty4Ever"
.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= "Eugene V Debs <eu*********@aim.com>"
.Update
End With

With iMsg
.Configuration = iCfg
.HTMLBody = Buffer
.Subject = "Here's Some HTML"
.To = "lf********@cogeco.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.


Jun 10 '06 #3

P: n/a
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
Jun 10 '06 #4

P: n/a
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_Field_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
Jun 10 '06 #5

P: n/a
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.

Jun 10 '06 #6

P: n/a
"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
Jun 10 '06 #7

P: n/a
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


Jun 14 '06 #8

P: n/a
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
Jun 14 '06 #9

P: n/a
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


Jun 14 '06 #10

P: n/a
Dave G @ K2 wrote:
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.


How are they sent? If they are sent as files then certainly stripping
off quotation marks is unnecessary. In some cases quotation marks are
essential.
You can read the contents of a text file and assign it to a string
variable without worrying about quotation marks. Copying the string
from a View Source utility is inefficient.

Jun 14 '06 #11

P: n/a
They are sent as html files, straight out of Dreamweaver I believe. I
open them in a browser to see what they look like. Then I view the
source and copy and paste it to a module and start work on it. Anyway,
not to worry. I've got it all working fine now - so thanks to everyone

Dave

Lyle Fairfield wrote:
Dave G @ K2 wrote:
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.


How are they sent? If they are sent as files then certainly stripping
off quotation marks is unnecessary. In some cases quotation marks are
essential.
You can read the contents of a text file and assign it to a string
variable without worrying about quotation marks. Copying the string
from a View Source utility is inefficient.


Jun 15 '06 #12

P: n/a

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


Take a look at this tool:

FMS Total Access Emailer
http://www.fmsinc.com/Products/Emailer/index.asp

I haven't personally used this tool, but I have taken a look at some
other of their products and they have some pretty useful tools.

Greg

Jun 15 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.