Access VBA Outlook Mailtiem.Body vs MailItem.HTMLBody

Question

I am Trying to Populate a Series of Generated Emails With Custom Hyperlinks But am Having Trouble in Getting the Hyperlinks to Generate Properly. I Have a User Form that allows the Person Creating the Reports to Enter in the E-Mail Body into a Text Field. This Field is Assigned to a Table.

In My Code I am Pulling those Fields and Parsing them for Keywords like DATE and HYPERLINK and Replacing those Keywords with the Code Generated Numbers.

As I Create Hyperlinks I am Inserting them into the Body String like Below:

Function CreateHyperLink(HyperText, HyperLink) As String

    CreateHyperLink = "<HTML><BODY>"
    CreateHyperLink = CreateHyperLink & "<A href='" & HyperLink & "'>'" & HyperText & "'</A>"
    CreateHyperLink = CreateHyperLink & "</BODY></HTML>"

End Function

But When I insert these as(eBody is an Array of Strings)

 Set oMail = oApp.CreateItem(olMailItem)
 With oMail
    .To = t
    .CC = c
    .HTMLBody = eBody(i)
    .Subject = eSubject(i)
ETC.....

The Hyperlinks Create Properly But I lose all the Spacing and Line Breaks from the Text the User Entered.

If Instead I use

 Set oMail = oApp.CreateItem(olMailItem)
 With oMail
    .To = t
    .CC = c
    .Body = eBody(i)
    .Subject = eSubject(i)
ETC.....

I Keep all the Formating but Lose the Hyperlink. Is There a Best of Both Worlds Solution? I Do not want my Users to have to Type in the entire Email in HTML.

Example Output of Method .htmlBody (Hyperlink is Valid but Missing Format)

The file can be accessed via: 'Click here to access the Report ' Please use the filters to select your...

Example Output of Method .Body (Hyperlink is not Valid but Formated)

The file can be accessed via :

<HTML><BODY><A href='http://FILELOCATION'>'Click here to access the Report '</A></BODY></HTML>  "

Please use the filters to select your...

Is there a Way to add Hyperlinks in .body or a way to Keep Formatting with .HTMLbody?

Sounds like your code is overwriting the existing HTML structure of the email body. Can you capture (in a string printed via `debug.print` or temporarily saved to a file) the before and after views of the email's `HTMLBody`? That will tell you if you're modifying/inserting your new links properly. — PeterT, Apr 13 '15 at 18:13
Thanks for the Reply, I never thought of checking the HTMLbody like that. `
<HTML><BODY><A href='http://HYPERLINK to Report/Report'>'Click here to access the Report '</A></BODY></HTML>

` You are right on your theory, Im going to look and see if I can Add the Hyperlink After I added the Original Formatted Text Thank you for the help! — Jimmy Jazzx, Apr 13 '15 at 18:24
As a Reply To Explain why I choose Peters Answer if anyone Stumbles Upon this in the future. I Choose the Method that Peter Used Over Pauls Answer Even though Both Work and produce Similar Results, Peters Works better with my Currently System. By Populating the Body of the email with the `.body` From the Text box, Outlook is Automatically Generating HTML Tags for me. So that the Format of the Text box is Preserved. Then using `.HTMLbody` to Insert the Hyperlink in the middle of the Populated Body to allow the Hyperlink to Generate Properly without Overwriting the HTML Tags. — Jimmy Jazzx, Apr 14 '15 at 14:29

score 0 · Answer 1 · answered Apr 13 '15 at 18:17

0

If I read your description properly, you are creating a new "HTML" and "BODY" set of tags for each Hyperlink. You should only have 1 HTML and 1 BODY tag in the HTML email that you are generating.

I suggest you update your CreateHyperLink function to this:

Function CreateHyperLink(HyperText, HyperLink) As String

    CreateHyperLink = "<a href='" & HyperLink & "'>'" & HyperText & "'</a>"

End Function

answered Apr 13 '15 at 18:17

Josh Miller

620
3
11

You Are Right That I am Creating too many Tags, But the Above doesn't Fix my Issues as Outlook Tags the Hyperlink in Standard `
` Tags and nothing more. I will have to Add the Tags In AFTER the Mail Item Generates there Tags
– Jimmy Jazzx Apr 13 '15 at 18:31

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

Have a look at Range in Middle of Email Body. My answer there shows how to insert into the middle of an existing .HTMLBody.

edited May 23 '17 at 11:50

Community

1
1

answered Apr 13 '15 at 20:36

PeterT

8,232
1
17
38

Thank you This Solves my Problem, I have Left a Placeholder for the Hyperlink in the code until the Email is Created. Then Used your method to Replace that Placeholder with the Hyperlink! – Jimmy Jazzx Apr 14 '15 at 14:19

score 0 · Answer 3 · answered Apr 14 '15 at 10:26

As Josh, has mentioned. You have to wrap the whole email in a single HTML BODY. So I am using what Josh has already mentioned, but a bit changes to your Form design.

First step. Go to Design view of the Form, make sure the Text Box (where you enter the body of the email) is set to Rich Text and not Plain Text. Rich text automatically wraps your data in proper DIV tags. So your code for creating hyperlink would be,

Function CreateHyperLink(HyperText, HyperLink) As String
    CreateHyperLink = "<A HREF = '" & HyperLink & "'>'" & HyperText & "'</A>"
End Function

Then in the body, you simply use, (I am not sure if you are pre formatting the HTML, but this is how I would go about doing it.)

Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = t
    .CC = c
    .HTMLBody = "<HTML><BODY><FONT FACE='Calibri'>" & eBody(i) & "</FONT></BODY></HTML>"
    .Subject = eSubject(i)
End With

REMEMBER ! eBody(i) is the result of just parsing the Keywords, not formatting. Since the TextBox is a Rich Text box, the formatting is done for you !

Thank you, Yes Am learning a lot about how Access/Outlook Handles Different Text formats. First time Dabbling in HTML. I think my problem was Poorly Explained, Thank you for this Information though it has helped! — Jimmy Jazzx, Apr 14 '15 at 14:22

Access VBA Outlook Mailtiem.Body vs MailItem.HTMLBody

3 Answers3

Linked