Solved

Building HTML email blaster from MS Access

Posted on 2011-09-23
6
494 Views
Last Modified: 2012-08-14
I am using MS ACCESS 2007 to build a simple automated email tool to cycle through a list of customers and present them a nicely formatted email rather than paying a vendor to do so.

My experience using VBA in access is beginner to intermediate.  I have some code now that works great but it is very static in nature and I would love it pull the HTML source from an object on a form where I would use the form object to build the HTML code and then be able to pull the HTML source from that object and assign it to a string in this example...Another way would be fine but I am trying to avoid too many manual steps to perform...trying to make this easy for a non-tech user to perform the email blast.  (I am traveling so I will not be able to have a pseudo real-time conversation.  Thank you for you time and patience.

One last note...I did find that MS Access has an Active X control called HTMLEditor Class.  I inserted that in my form but was unable to find a way to reference the HTML Source that it builds (nevermind the fact that I could not actually get a return line in that object...even when pressing control+enter).

The code I am using is as follows:

Private Sub cmdSendEmail_Click()
Dim cdoConfig
Dim msgOne
Dim rst As Object
Dim db As Database
Dim rstEmailList As Recordset
Dim Counter As Integer
Dim strHTML As String
Dim EnsureEmailSend As Integer
Dim EmailList As String

EmailList = "qryEmailAddressesTest"

'This is for emailing to a group of users
'Can be used with a remote server, ergo this does not need to be run on our Mail server
EnsureEmailSend = MsgBox("Ready to Send Email?", vbOKCancel, "WARNING!")
If EnsureEmailSend = 2 Then Exit Sub ' 2 = Cancelled

Set cdoConfig = CreateObject("CDO.Configuration")

With cdoConfig.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") = "mail.wirelesspartners.org"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "me@them.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Password"
   
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
   
    .Update
End With


' Build HTML for message body.
strHTML = "<HTML><HEAD>"
strHTML = strHTML & "<b>This is supposed to be HTML formating in this email.  This line should be bold</b></br></br>"
strHTML = strHTML & "This should be a new line </br></HEAD>"
strHTML = strHTML & "</br><BODY>"
strHTML = strHTML & "<Fial Color=#ff0000 Size=5>This should be a different Color on new line</FONT></br></br>"
strHTML = strHTML & "<FONT Size=ONT Face=Ar3>This should be different font size <FONT Size=2></B>and this should be smaller yet. (new line)</br></br>"
strHTML = strHTML & "<FONT Size=3><B>Nothing fancy here, just another line, font 3. </B><FONT Size=2>And again, smaller size (New line)</FONT></br>"
strHTML = strHTML & "</BODY></HTML>"

Set db = CurrentDb()
Set rstEmailList = db.OpenRecordset(EmailList)
rstEmailList.MoveFirst
Do While Not rstEmailList.EOF
    Set msgOne = CreateObject("CDO.Message")
    Set msgOne.Configuration = cdoConfig
    msgOne.to = rstEmailList!Email
    msgOne.from = "emSales@WirelessPartners.org"
    msgOne.Subject = "Test HTML Email"
    'msgOne.TextBody = "It works just fine "
    msgOne.HTMLBody = strHTML
    msgOne.send
    rstEmailList.MoveNext
Loop 'rstEmailList

'Clean up objects
'rstEmailList.Close
Set cdoConfig = Nothing
Set db = Nothing

End Sub
0
Comment
Question by:Expert-In-The-Making
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 84
ID: 36587168
First: Access does not contain any ActiveX controls, and in fact most ActiveX controls you'll find on your machine are NOT compatible with Access. The items you see on the ActiveX listing are just controls that have been added to your machine at some point by some process.

If you're looking to add some sort of an HTML edit control to Access, you might look into this: http://xstandard.com/. I've not used it, but several people have indicated that it works very well with Access.

There's also the Lebans stuff: http://lebans.com/htmleditor.htm

His stuff is no longer in active development, so you might find them lacking in some areas.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36588634
I am trying to figure out what your one direct question is?

<I would love it pull the HTML source from an object on a form where I would use the form object to build the HTML code and then be able to pull the HTML source from that object and assign it to a string in this example.>

?
0
 

Author Comment

by:Expert-In-The-Making
ID: 36593348
I am looking for a way to create an email tool that is dynamic for the message where it uses HTML (or XHTML).  In the code above, it is static. I know I could make a textbox on a form and reference the that textbox object's value and it would be dynamic but it would not be HTML and it would not be very formatible.  1st question)  Is there an object in MS ACCESS that allows you to format the text allthewhile building the HTML code that represents the formatted text in the object?

2nd question)  IF there is, how do I reference the HTML source code of that object so I can assign it to a string where it would be used in the code above on the line where I assign strHTML to msgOne.HTMLBody

3rd question) if there is no such object in MS Access, how would I go about making my task easier in building the value of strHTML as HTML code representing a desired format that I can build in some HTMLEditor?

I am open to several solutions but I wanted to do my best in keeping this process user friendly.

LSMConsulting, I am unsure how to use that tool and be able to access that objects HTML Source code (how to reference the HTML source code value).  
0
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 36595028
< 1st question)  Is there an object in MS ACCESS that allows you to format the text allthewhile building the HTML code that represents the formatted text in the object?>

No.

<2nd question)  IF there is, how do I reference the HTML source code of that object so I can assign it to a string where it would be used in the code above on the line where I assign strHTML to msgOne.HTMLBody>

See above

<3rd question) if there is no such object in MS Access, how would I go about making my task easier in building the value of strHTML as HTML code representing a desired format that I can build in some HTMLEditor?>

There is no simple way to build HTML code in Access. The xStandard control I suggested seems to be quite simple and easy to use - you just drop it on a form, and then allow the end user to build their email, and then use the Value property to retrieve the formatted data from that control.



0
 

Author Comment

by:Expert-In-The-Making
ID: 36601227
LMSConsulting - You are a lifesaver.  This is a fine solution.  Thanks a ton!
0
 

Author Closing Comment

by:Expert-In-The-Making
ID: 36601253
LMSConsulting pointed me in the right direction.  Quite honestly, they did not need to give specific details on how to actually bring in the object or get into any more details as the solution was quite simple.  Knowing about the object and what it could be used for is where all the value was at.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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