VBA Code for Bold Text & Hyperlink

**stu_C** · 10-13-2023, 01:29 AM

Hi all

I have got some VBA code that opens up outlook, inputs all the subject, To, and body of the email etc
I ideally trying to do it so certain text is bold how would I do this?
also I want a CLICK HERE link that is a hyper link rather than showing the whole website address any ideas?

the reason everything is done through VBA is because due to our security measures I was unable to use an actual template but only a blank one.

I posted on the Access Programmers but still having issues
https://www.access-programmers.co.uk.../#post-1892577

Set myOlApp = CreateObject("Outlook.Application")

Set myitem = myOlApp.CreateItemFromTemplate(CurrentProject.Path & "\TemplateFiles\EmailToHR.oft")

myitem.Subject = UCase("HR REPORT COMPLETED - " & "[" & [Forms]![FRM_TBLALL_FullDetails].[Form]![LblRef] & "]")

myitem.To = UCase([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![CmbSupervisor].Column(0))

myitem.display

myitem.body = "The HR Report has now been completed. USBs containing the download report will now be placed in HR Secure storage for your collection under the Ref numbers below." & Chr(13) & Chr(10) & _

"HR Report Reference Numbers:" & Chr(13) & Chr(10) & _ MAKE BOLD TEXT

"Working Copy - " & [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblWorkingCopyRef] & Chr(13) & Chr(10) & _ MAKE THE REF NUMBER SHOW BUT AS A HYPER LINK

"Backup Copy - " & [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef] & Chr(13) & Chr(10) & _ MAKE THE REF NUMBER SHOW BUT AS A HYPER LINK

"Your USB BitLocker password is:" & Chr(13) & Chr(10) & _ MAKE BOLD TEXT

[Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![BitLockerPassword] & Chr(13) & Chr(10)

"If you have any issues / queries please contact the your Local HR hub." & Chr(13) & Chr(10)

**June7** · 10-13-2023, 01:55 AM

Same request: Please post code between CODE tags.

You were given examples of code and have not shown application of suggestions.

**Minty** · 10-13-2023, 02:00 AM

@Stu put all that string stuff into variables, and build it outside the mailing bit.
It will add clarity and assist with debugging.

What happens when you use this:

Code:

Dim sBody         as string
Dim sLF         as string
Dim sTo         as string
Dim sSubject    as string


sLF = Chr(13) & Chr(10) 


sTo = [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![CmbSupervisor].Column(0))


sSubject = "HR REPORT COMPLETED - [" & UCase([Forms]![FRM_TBLALL_FullDetails].[Form]![LblRef]) & "]"


sBody = "The HR Report has now been completed. USBs containing the download report will now be placed in HR Secure storage for your collection under the Ref numbers below." & sLF
SBody = sBody & "<B> HR Report Reference Numbers:</B>" & sLF
SBody = sBody & "Working Copy - " & [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblWorkingCopyRef] & sLF     ' MAKE THE REF NUMBER SHOW BUT AS A HYPER LINK
SBody = sBody & "Backup Copy - " & [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef] & sLF        ' MAKE THE REF NUMBER SHOW BUT AS A HYPER LINK
SBody = sBody & "<B>Your USB BitLocker password is:</B>" & sLF             '& _ MAKE BOLD TEXT
SBody = sBody & Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![BitLockerPassword] & sLF 
SBody = sBody & "If you have any issues / queries please contact the your Local HR hub." & sLF




Set myOlApp = CreateObject("Outlook.Application")
myOlApp.Session.Logon
Set myitem = myOlApp.CreateItem(0)     ' myOlApp.CreateItemFromTemplate(CurrentProject.Path & "\TemplateFiles\EmailToHR.oft")


With myitem
    .Body = olFormatHTML
    .Subject = sSubject
    .To =  sTo
    .HTMLbody = sBody
    .display
end with

I haven't attempted to get your hyperlinks working at this point, but that is doable.

I'm not sure why you are opening a template when you say it is blank, that makes no sense, so I have simply created a fresh email.

**June7** · 10-13-2023, 02:21 AM

If you want cr lf in HTML text, Chr(13) & Chr(10) is not going to do that. Use <br> tag.

Example of what can be done:

Code:

        body = "<table style='text-align:right;border:1px solid black;font-family:calibri;border-collapse:collapse;padding:15px'><tr style='background:yellow;mso-highlight:yellow'>" & _
            "<th>No</th><th>Client</th><th>Date</th><th>PR</th><th>Currency</th><th>Amt</th></tr>" & _
            "<tr><td>12345</td><td>Jones</td><td>12Dec2017</td><td>ABC</td><td>US</td><td>2000</td></tr>" & _
            "<tr><td>67890</td><td>Davis</td><td>12Dec2017</td><td>ABC</td><td>US</td><td>1000</td></tr></table>"
        body = "<HTML style='font-family:calibri'><Body><font face='calibri'><a href='C:\Users\Owner\June\Forums'><img SRC=C:\Users\Owner\June\DOT\Lab\Editing\LABDB.png></a><br>" & _
            "<font color='red' size='1'>&nbsp;&nbsp;&nbsp;&nbsp;READ THIS EMAIL COMPLETELY</font><br>" & _
            body & _
            "<br><b>Please confirm or I will kick you.</b><br>" & _
            "</font></Body></HTML>" & _
            "<table><tr bgcolor='yellow'>THIS WILL CREATE BLACK TEXT WITH YELLOW BACKGROUND</tr></table>" & _
            "<span style='background:yellow'>ALSO BLACK TEXT YELLOW BACKGROUND</span>"

**Minty** · 10-13-2023, 02:34 AM

Originally Posted by June7

If you want cr lf in HTML text, Chr(13) & Chr(10) is not going to do that. Use <br> tag.

DOH - of course - at least only need to change it in one place...

**stu_C** · 10-16-2023, 05:07 AM

thank you! this worked!
quick question how do I force a specific font to this?

Originally Posted by Minty

@Stu put all that string stuff into variables, and build it outside the mailing bit.
It will add clarity and assist with debugging.

What happens when you use this:

Code:

Dim sBody         as string
Dim sLF         as string
Dim sTo         as string
Dim sSubject    as string


sLF = Chr(13) & Chr(10) 


sTo = [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![CmbSupervisor].Column(0))


sSubject = "HR REPORT COMPLETED - [" & UCase([Forms]![FRM_TBLALL_FullDetails].[Form]![LblRef]) & "]"


sBody = "The HR Report has now been completed. USBs containing the download report will now be placed in HR Secure storage for your collection under the Ref numbers below." & sLF
SBody = sBody & "<B> HR Report Reference Numbers:</B>" & sLF
SBody = sBody & "Working Copy - " & [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblWorkingCopyRef] & sLF     ' MAKE THE REF NUMBER SHOW BUT AS A HYPER LINK
SBody = sBody & "Backup Copy - " & [Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef] & sLF        ' MAKE THE REF NUMBER SHOW BUT AS A HYPER LINK
SBody = sBody & "<B>Your USB BitLocker password is:</B>" & sLF             '& _ MAKE BOLD TEXT
SBody = sBody & Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![BitLockerPassword] & sLF 
SBody = sBody & "If you have any issues / queries please contact the your Local HR hub." & sLF




Set myOlApp = CreateObject("Outlook.Application")
myOlApp.Session.Logon
Set myitem = myOlApp.CreateItem(0)     ' myOlApp.CreateItemFromTemplate(CurrentProject.Path & "\TemplateFiles\EmailToHR.oft")


With myitem
    .Body = olFormatHTML
    .Subject = sSubject
    .To =  sTo
    .HTMLbody = sBody
    .display
end with

I haven't attempted to get your hyperlinks working at this point, but that is doable.

I'm not sure why you are opening a template when you say it is blank, that makes no sense, so I have simply created a fresh email.

**June7** · 10-16-2023, 12:03 PM

Example in post 4 shows applying font with a couple of methods.

https://www.w3schools.com/tags/tag_font.asp

**stu_C** · 10-17-2023, 02:41 AM

Thank you!
one final thing!, the hyper link that the first part is standard and the second part of the address is the ref number of the case, I cannot seem to get this to work any suggestions?

I can get the REF number to show up in the Email as a hyperlink but when the link is clicked the only part of the hyperlink showing in the address bar is http://corp/ViewDetails.asp?Headerid=

for example the address should be
http://corp/ViewDetails.asp?Headerid=ER123456

Code:

sBody = sBody & "<B>Backup Report - </B>" & "<a href=http://corp/ViewDetails.asp?Headerid= & Mid([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef]>"_
 & UCase([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef]) & "</a>" & sLF

**Minty** · 10-17-2023, 03:02 AM

You aren't concatenating your lines correctly.

My advice - stop trying to use the _ continuation mark on such long lines, and break them into more manageable chunks:

Code:

sBody = sBody & "<B>Backup Report - </B><a href=http://corp/ViewDetails.asp?Headerid= " & Mid([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef]
sBody = sBody & ">" & UCase([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef]) & "</a>" & sLF

Also a Debug.Print sBody in your code before creating the email would have shown you what your string looked like, and highlighted the error.

So, if it still isn't right (the above is air code), add the Debug.Print and post up what the string is in the immediate window, and your new code.

**stu_C** · 10-17-2023, 04:21 AM

Hello
the text just comes up in red with the error, Complie error: Expected: list sepetor or )

Originally Posted by Minty

You aren't concatenating your lines correctly.

My advice - stop trying to use the _ continuation mark on such long lines, and break them into more manageable chunks:

Code:

sBody = sBody & "<B>Backup Report - </B><a href=http://corp/ViewDetails.asp?Headerid= " & Mid([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef]
sBody = sBody & ">" & UCase([Forms]![FRM_TBLALL_FullDetails]![SSFRM_TBLALL_StaffDetails].[Form]![LblBackupCopyRef]) & "</a>" & sLF

Also a Debug.Print sBody in your code before creating the email would have shown you what your string looked like, and highlighted the error.

So, if it still isn't right (the above is air code), add the Debug.Print and post up what the string is in the immediate window, and your new code.

**Minty** · 10-17-2023, 04:26 AM

Originally Posted by stu_C

Hello
the text just comes up in red with the error, Complie error: Expected: list sepetor or )

So what happened to :

Originally Posted by Minty Wrote

So, if it still isn't right (the above is air code), add the Debug.Print and post up what the string is in the immediate window, and your new code.

If you want help, you have to give us a clue.

**stu_C** · 10-17-2023, 04:36 AM

sorted it!, i forgot to put the , 3, 8) at the end of the mid

VBA Code for Bold Text & Hyperlink

Thread Tools

VBA Code for Bold Text & Hyperlink

Similar Threads

make the text in the field bold

Set Label Text To Bold In VBA

How to bold text within concatenate ?

How to create a message box with bold text

How do I Bold the first Text in ListBox

Posting Permissions