Solved

VBA 2003-2007 Outlook HTMLBody Problems

Posted on 2010-09-09
14
1,235 Views
Last Modified: 2012-06-27
Our office recently upgraded to MS Office 2007. This change however created some problems with existing VBA scripts. In particular ones used to automate emails from access. The code no longer inserts the entire body of the email, but only first instance MailOutLook.HTMLBody = "message".
Every subsequent paragraph is followed by MailOutLook.HTMLBody = MailOutLook.HTMLBody & "Additional info".
In the past this has reset MailOutLook.HTMLBody to include the previous string and the new one. However, in 2007 it will not reset MailOutLook.HTMLBody or include any of the additional strings.

Here is some sample code from the problem.
 
Dim MailOutLook As Outlook.MailItem

MailOutLook.HTMLBody = "Message"
MailOutLook.HTMLBody = MailOutLook.HTMLBody & "Table Headers"
Do Until rs.EOF
MailOutLook.HTMLBody = MailOutLook.HTMLBody & "Table column data"
rs.MoveNest
Loop
MailOutLook.HTMLBody = MailOutLook.HTMLBody & "Closing Remarks"

Open in new window


The code above is obviously a shortened version, but will only display the "Message" following MailOutLook.HTMLBody and not any of the tables or other paragraphs  further down. I can only assume that this has to do with some odd between the two versions of office, but I have no idea where to look or how to fix it.

Thanks,
0
Comment
Question by:Lee-H
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 12

Accepted Solution

by:
ErezMor earned 400 total points
ID: 33642773
without refferring to possible changes or the real cause of your problem (which i dont know), better coding approach would be building an entire string (or mail message in this case), and only then transporting it to outlook
so add
dim strMeessage as string
at the top, concatenate it all in to it (strMessage=strMessage & "bla bla bla..." works great)
then call outlook only once:
MailOutLook.HTMLBody = strMessage
0
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 100 total points
ID: 33644608
Hi, Lee-H.

There must be something else that's wrong.  Outlook 2007 does not handle the code you posted any differently than 2003.  A loop is a loop and concatenating a string is handled in exactly the same fashion.  Neither of those operations are specific to Outlook.  They are general operations of VBA.  I'd set a breakpoint at the beginning of the loop and step through it to see what's going on.
0
 

Author Comment

by:Lee-H
ID: 33648199
There is  a lot of stuff inside the code that I cant share, but when I get back to work on Monday I will remove all of the data that can't be shared and post the whole code. Thanks guys. I will also give the break a try.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33649486
i doubt it's this simple but i noticed that your code might be misspelled.  

rs.MoveNest
* rs.MoveNext

i cant know if that was a mistake when copying to this site but thought id mention it.
if it is true that it is just a simple spelling error i would compile your database as well as correcting the spelling.
0
 

Author Comment

by:Lee-H
ID: 33662453
Okay very sorry for the delay. But I can only access this database at the office due to its sensitivity. Anyway, I took everything out of the original code that lends any hint as to the contents of the email and identities.
 This peice of code worked fine in 2003 and generated the entirety of the email. However, it now stops at MailOutLook.HTMLBody = "Dear " & FullName & ",<br><br>" and will not continue the loop from there.
Thanks,
Private Sub Command26_Click()



 Dim Recipient As String

 Dim Forward As String

 Dim Message As String

 Dim lblProject As String

 Dim rs As DAO.recordSet

 Dim db As database

 Dim sql As String

 Dim PIName As String

 Dim RecCount As Integer

 Dim p1 As String

 Dim p2 As String

 Dim p3 As String

 Dim p4 As String

 Dim p5 As String

 Dim p6 As String

 Dim p7 As String

 Dim p8 As String

 Dim p9 As String

 Dim Sname As String

 Dim FName As String

 Dim FullName As String

 Dim LName As String

 Dim EmailSubject As String

 Dim Iname As String

 

 

 Sname = Me.txtPIName

 

' Fname = Split(Sname, ",")

 'Replace(Sname, ",") = Fname

 'Fname = Replace(Sname, "*,", "")

 FName = Split(Sname, ",")(1)

 LName = Split(Sname, ",")(0)

 FullName = FName & " " & LName

 Iname = Left(FName, 2) & ". " & LName

 

 

    Dim appOutLook As Outlook.Application

    Dim MailOutLook As Outlook.MailItem

    Set appOutLook = CreateObject("Outlook.Application")

    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    

PIName = Me.txtPIName

' SQL query to find which records belong to the specified PI

sql = "SELECT tblImport.[Child Flex Value], tblImport.[Child Value Description], [F&AGeneral].[FA Earned], [F&AGeneral].[FA Distrib], [F&AGeneral].PI, [F&AGeneral].[DEAN/DEPT], [F&AGeneral].OVPR FROM [F&AGeneral] INNER JOIN tblImport ON [F&AGeneral].Project = tblImport.[Child Flex Value] WHERE (((tblImport.[Child Pi S E mail Address])='" & Me.Email_address & "'));"



On Error Resume Next

' Email Recipients

Recipient = Me.Email_address

Forward = Me.cc_address & "; " & Me.cc2_address





'Open the recordset

Set db = CurrentDb

Set rs = db.OpenRecordset(sql)



' Mail Introduction

MailOutLook.HTMLBody = "Dear " & FullName & ",<br><br>"



' Message Body

p1 = "text"

p3 = "paragraph 2"

p4 = "paragraph 3"

p5 = "paragraph 3"

p6 = "paragraph 4"

p7 = "paragraph 5"

p8 = "paragraph 6"

p9 = "Final Paragraph"

p10 = "CONFIDENTIALITY NOTE" & Iname



' The following builds the body message.

MailOutLook.HTMLBody = MailOutLook.HTMLBody & p1 & "<br><br>" & p2 & "<br><br>" & p3 & "<br>" & "<DIR>" & p4 & "<br>" & p5 & "<br>" & p6 & "</DIR>" & "<br>" & p7





' Table Opening and Headers

MailOutLook.HTMLBody = MailOutLook.HTMLBody & "<font size='8'>" & "<table width=350 border=1 cellpadding=5 style= ' border-collapse:collapse; border-color:black; border-style:Solid; border-width: thin '><tr><th>Project:</th><th width='15%'> Description:</th><th>FY09 F&A</th><th>F&A to be Distributed</th><th>OVPR 25%</th><th width>Dean/Dept 10%</th><th width>PI 10%</th></tr>"

'Add Records to the tbale.

   Do Until rs.EOF

   

     MailOutLook.HTMLBody = MailOutLook.HTMLBody & _

                    "<tr><td align='center'><font size='-2'>" & rs![Child Flex Value] & "</font></td><td align='left'><font size='-2'>" & rs![Child Value Description] & "</font></td><td align='right'><font size='-2'>" & FormatCurrency(rs![FA Earned], 2, True, True, True) & "</font></td><td align='right'><font size='-2'>" & FormatCurrency(rs![FA Distrib], 2, True, True, True) & "</font></td><td align='right'><font size='-2'>" & FormatCurrency(rs!OVPR, 2, True, True, True) & "</font></td><td align='right'><font size='-2'>" & FormatCurrency(rs![Dean/Dept], 2, True, True, True) & "</font></td><td align='right'><font size='-2'>" & FormatCurrency(rs![pi], 2, True, True, True) & "</font></td></tr>"

    'MailOutLook.HTMLBody = MailOutLook.HTMLBody & "<tr><td>" & rs![Child Flex Value] & "</td><td>" & rs![Child Value Discription] & "</td><td>" & FormatNumber(rs![FA Earned], 2, True, True, True) & "</td><td>" & FormatNumber(rs![FA Distrib], 2, True, True, True) & "</td><td>" & rs!OVPR & "</td><td>" & rs![Dean/Dept] & "</td><td>" & rs!pi & "</td></tr>"



        rs.MoveNext

        Loop

rs.Close

db.Close



    

    

' Close the table

MailOutLook.HTMLBody = MailOutLook.HTMLBody & "</table>" & "</font>"





'Closing Notes

MailOutLook.HTMLBody = MailOutLook.HTMLBody & "<b><font size='-1'>" & p8 & "</font></b>" & "<br><br>" & p9 & "<br><br>" & Me.Rep_Name & ", " & Me.Rep_Title & "<br>" & "Sponsored Programs Accounting" & "<br>" & Me.Rep_Email & "<br>" & Me.Rep_Phone & "<br><br>" & "<font size='-2'>" & p10 & "</font>"



' Message Routing Information

    MailOutLook.Display

    MailOutLook.To = Recipient

    MailOutLook.CC = Forward

    MailOutLook.Subject = EmailSubject



DriverExit:

    On Error Resume Next

    Set MailOutLook = Nothing

    Set appOutLook = Nothing

    Exit Sub

    

End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:conagraman
ID: 33662576
is your database compiled?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33664864
You say it stops and won't continue.  Are you getting an error message?  
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:Lee-H
ID: 33665240
Sorry let me clarify. The email opens, but it does not write anything past Dear First Name & Last Name,
There are several additional paragraphs that should populate when the script runs.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33665443
Did you try the breakpoint I suggested?
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33665457
is your database compiled?
0
 

Author Comment

by:Lee-H
ID: 33665883
Yes the database is compiled, its running on a MS SQL server. I also tried the break point. I am going to try using strMessage=strMessage that ErezMor suggested. Perhaps if you use that loop first instead of the other that I was using it will work properly.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33667114
try removing all of the "On Error Resume Next" comments
this might help point out where the problem is. also

as bluedevil already pointed out. changing to a string that is built dynamically isn't going to make a difference.
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 33680524
i'm sorry to defer, but i wouldnt underestimate the importance of good coding
it's nothing less than the first step to do before even considering anything else when trying to solve an obscure error
rewrite your code to build the whole string, only then send it (once) to outlook.
this is a simple and common task outlook automation provides AND many experts have enough experience in, should you still need more guidance
0
 

Author Closing Comment

by:Lee-H
ID: 33682450
Thanks for the help this is exactly what I needed to do to make things work again. I am sorry I have been so busy running around fixing all of the other problems we have had with the upgrade that I barley had time to heed any of your advice. This is an inherited database and I would love to redo a lot of it but I harldey ever have a dull moment. Thanks again, all of you, your help was fantastic.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data analyst 3 50
Outlook importing calendar entries 6 66
Export Query data to excel file 14 37
Exchange 2010 SP3 and Outlook 2003 7 31
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

19 Experts available now in Live!

Get 1:1 Help Now