Solved

Reference a variable in HTML when sending an email from Access 2013

Posted on 2014-03-12
3
592 Views
Last Modified: 2014-03-12
I have an Access (Access 2013) form which I use to send email out to about 350 people.  I put the html in a text box to send it out.  The VBA is as follows

-------------------
Private Sub cmdSend_Click()
        Dim mess_body As String
        Dim appOutLook As Outlook.Application
        Dim MailOutLook As Outlook.MailItem
        Dim rst As ADODB.Recordset
        Dim vCount
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
        Set rst = New ADODB.Recordset
        Set rst.ActiveConnection = CurrentProject.Connection
           
        rst.CursorType = adOpenKeyset
        rst.LockType = adLockOptimistic
        rst.Source = "EmailSend"
        rst.Open options:=adCmdTable
                   
        vCount = 0
                   
            rst.MoveFirst
            Do Until rst.EOF
            Set appOutLook = CreateObject("Outlook.Application")
            Set MailOutLook = appOutLook.CreateItem(olMailItem)
            With MailOutLook
                .BodyFormat = olFormatRichText
                vEmail = rst("InternalEmail")
                vName = rst("Name")
                .To = vEmail
               
                .Subject = Me.Mess_Subject
             
                .HTMLBody = Me.mess_text
                    If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
                        .Attachments.Add (Me.Mail_Attachment_Path)
                    End If
                    '.DeleteAfterSubmit = True   'This would let Outlook send th note without storing it in your sent bin
                .Send
                'txtStatus.Value = "Email Gone"
                PauseIt 30
                'txtStatus.Value = "Next one"
                rst.MoveNext
               
            End With
            vCount = vCount + 1
            txtEmailCount = vCount
            Loop
           
            'MsgBox MailOutLook.Body
            Exit Sub
           
email_error:
            MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
            Resume Error_out
Error_out:
   
End Sub
------------------------------

The name of the text box is mess_text.  I would like to be able to add the name of the person the email is going to so I need to know if you can put a variable in the html code so that each time it loops and gets the email address it can also get the name.  Here is the html:

------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

<html>

<title>Phi Kappa Tau</title>

<head><center>
<img src="http://www.mediafire.com/imgbnc.php/fb4efed6d4c73da74f16d97c149249714d0fef645b94f90cace8e5612696bb2d4g.jpg" border="0">
</center>
</head>

<body>
<p>
<table width="80%" height="100%" align="center">
<tr><td><font face="sans-serif">

Dear Beta Omega Alum,
<p>

<p>
Faithfully Submitted,<br>
Scott Palmer

<p>
<BR CLEAR="ALL" />
<hr>
<p>
<table width="100%" align="center">
<font face="Sans-serif">
<tr><td align="center">
<a href="mailto:Webmaster@phitaualumni.org">Webmaster</a> |
<a href="http://phitaualumni.org/index.php">Phi Tau Alumni</a> | <a href="http://phitaualumni.org/AlumniWeekend.htm">Alumni Weekend</a>

</tr></td>
</font>

</table>

</body>

</html>
----------------------------

Thanks,
Scott
0
Comment
Question by:ScootterP
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
So instead of "Dear Beta Omega Alum", you'd like it to read "Dear John Smith"?

If so, then you could use a "token" in the HTML, and replace that after you get the name. It looks like your VBA code already gets the name here:

 vName = rst("Name")

If so, then change your HTML to something like this:

table width="80%" height="100%" align="center">
<tr><td><font face="sans-serif">

Dear <<name>>,
<p>

<p>

Open in new window

And then do this:

Dim sBody As STring
sBody = Me.mess_text
sBody = Replace(sBody, "<<name>>", vName)
.HTMLBody = sBody
0
 

Author Comment

by:ScootterP
Comment Utility
Where do I put the additional code?
0
 

Author Closing Comment

by:ScootterP
Comment Utility
I messed with it for awhile and got it to work.

Thank you very much,
Scott
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

763 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

15 Experts available now in Live!

Get 1:1 Help Now