Solved

Building HTML email blaster from MS Access

Posted on 2011-09-23
6
487 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 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

18 Experts available now in Live!

Get 1:1 Help Now