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 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.Configur
") = 2
") = 25
") = "mail.wirelesspartners.org
") = "firstname.lastname@example.org"
") = "Password"
") = False
") = 1
' 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
Do While Not rstEmailList.EOF
Set msgOne = CreateObject("CDO.Message"
Set msgOne.Configuration = cdoConfig
msgOne.to = rstEmailList!Email
msgOne.from = "emSales@WirelessPartners.
msgOne.Subject = "Test HTML Email"
'msgOne.TextBody = "It works just fine "
msgOne.HTMLBody = strHTML
'Clean up objects
Set cdoConfig = Nothing
Set db = Nothing