?
Solved

Manipulating String colors in VBA

Posted on 2006-06-21
6
Medium Priority
?
284 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
  • 3
3 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

864 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