Solved

Manipulating String colors in VBA

Posted on 2006-06-21
6
279 Views
Last Modified: 2010-04-30
MS Access application generate emails.   Applications builds the email message body programmatically.  

dim messagebody, starttext, endtext as string
dim startdate as string

startdate =  'value assigned based by program logic'

Messagebody =  starttext & startdate & endtext

The requirement is to manipulate the font characteristics of the startdate field.  For example that field should appear in an alternate color and in bold.

Please advise
0
Comment
Question by:rafavro
[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
6 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 125 total points
ID: 16958554
Are you creating e-mails using the HTMLBody property of the Outlook.Application.MailItem object?

Here's some sample code that demonstrates how to make the "StartDate" variable colo[u]red Red & in Bold...

' Start of Code...

Option Explicit

Public Const olImportanceHigh                           As Long = 2&
Public Const olImportanceLow                            As Long = 0&
Public Const olImportanceNormal                         As Long = 1&

Public Enum intSave_Display_Send
  intSave = 1
  intDisplay = 2
  intSend = 4
End Enum
Public Sub Send_Email(Optional ByVal strTo As String = "experts_fp" & "@" & "yahoo.co.uk;", _
                      Optional ByVal strCc As String = "", _
                      Optional ByVal strBcc As String = "", _
                      Optional ByVal strSubject As String = "Subject", _
                      Optional ByVal strBody As String = "Body", _
                      Optional ByVal blnHTMLBody As Boolean = True, _
                      Optional ByVal lngImportance As Long = olImportanceNormal, _
                      Optional ByVal blnReturn_Receipt As Boolean = True, _
                      Optional ByVal intProcess As intSave_Display_Send = intDisplay)
 
' ----------------------------------------------------------------------------------------------
' Experts Exchange Question:
' http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21894721.html
' Manipulating String colors in VBA
'
' Copyright (c) 2006 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 22 June 2006
' ----------------------------------------------------------------------------------------------

  Dim lngErr_Number                                     As Long
  Dim objOutlook_Application                            As Object
  Dim objOutlook_MailItem                               As Object
 
  On Error Resume Next
 
  Const olMailItem                                      As Long = 0&

  Set objOutlook_Application = Nothing
  Set objOutlook_MailItem = Nothing
 
  Set objOutlook_Application = GetObject(, "Outlook.Application")
 
  lngErr_Number = Err.Number
 
  On Error GoTo Err_Send_Email
 
  If lngErr_Number <> 0& Then
     Set objOutlook_Application = CreateObject("Outlook.Application")
  End If
 
  If Not (objOutlook_Application Is Nothing) Then
     Set objOutlook_MailItem = objOutlook_Application.CreateItem(olMailItem)
  End If
           
  If Not (objOutlook_MailItem Is Nothing) Then
     objOutlook_MailItem.To = strTo
     objOutlook_MailItem.CC = strCc
     objOutlook_MailItem.BCC = strBcc
     objOutlook_MailItem.Subject = strSubject
     
     If (blnHTMLBody) Then
        objOutlook_MailItem.HTMLBody = strBody
     Else
        objOutlook_MailItem.Body = strBody
     End If
     
     objOutlook_MailItem.Importance = lngImportance
     objOutlook_MailItem.ReadReceiptRequested = blnReturn_Receipt
     
' Add Attachments here...

'     objOutlook_MailItem.Attachments.Add "c:\file1.txt"
'     objOutlook_MailItem.Attachments.Add "c:\file2.jpg2"
     
' etc.
     
     Select Case (intProcess)
     
         Case (intSave)
             objOutlook_MailItem.Save
             
         Case (intDisplay)
             objOutlook_MailItem.Save
             objOutlook_MailItem.Display
             
         Case (intSend)
             objOutlook_MailItem.Send
             
         Case Else
         
     End Select
  End If

Exit_Send_Email:

  On Error Resume Next
 
  Set objOutlook_MailItem = Nothing
  Set objOutlook_Application = Nothing
 
  Exit Sub
 
Err_Send_Email:

  MsgBox "Error #" & CStr(Err.Number) & vbCr & vbCrLf & Err.Description, _
         vbExclamation Or vbOKOnly, _
         "Test E-mail"
         
  Resume Exit_Send_Email
 
End Sub
Public Sub Test()

  Dim EndText                                           As String
  Dim MessageBody                                       As String
  Dim StartDate                                         As String
  Dim StartText                                         As String
 
  StartText = "(Start Text) "
  StartDate = Format$(Now(), "Long Date")
  EndText = " (End Text)"
 
  MessageBody = StartText & "<font color=red><bold>" & StartDate & "</bold></font>" & EndText
 
  Call Send_Email("experts_fp" & Chr$(64) & "yahoo.co.uk", _
                  "", _
                  "", _
                  "Q_21894721", _
                  MessageBody, _
                  True, _
                  olImportanceNormal, _
                  True, _
                  intDisplay)
 
End Sub

' ...End of Code


BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16958557
Sorry... got the VBA correct, but not the HTML! :)

Please replace this line:

MessageBody = StartText & "<font color=red><bold>" & StartDate & "</bold></font>" & EndText

With:

MessageBody = StartText & "<font color=red><b>" & StartDate & "</b></font>" & EndText


Thanks.

BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 17294954
Hi Dan,

I believe my code addresses the question, and hence should be considered as an acceptable answer.

Thank you.

BFN,

fp.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

717 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