Vanishing commas when exporting a report into an HTML email body (1 Viewer)

[ELizabethC]

Hello, all. I wonder if any of you can tell me how to fix an odd little problem I'm having.

I'm running the following code to generate an email from a report.

Function ExportHTML3()
Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

DoCmd.OutputTo acOutputReport, "EmailTemplateUpdated", acFormatHTML, "C:\IMEmail.html"

Open "C:\IMEmail.html" For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.Subject = [[Status]] + " " + [[Incident Reference]] + " - " + [[Title]] + " - " + [[Incident Ranking]]
MyItem.Display

End Function

The code is on the code sheet of a form, where there's a button. The user enters or updates a record on the form and clicks to generate an email, with fields from the current record on the form displayed in the body. So far so good. The code above works very nicely, even taking across the bold formatting on the report.

But there's one thing going wrong. If there are any commas in the text being copied across to the email body, they disapper, along with the space after them. So I go from say "there is a comma, but then" to "there is a commabut then".

I know it's outputting the commas to the HTML file, because I've tried running the process only to that point, then taking a look at the HTML file C:\IMEmail.html. The commas are there. So somehow between there and the email body the commas and the spaces after them are vanishing. So I assume it is something to do with this part:

Open "C:\IMEmail.html" For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1

But I don't really know much VBA and I found that code on the internet, so I can't figure out how it's doing that and if I can stop it. Or is there another way to get the text from the HTML file into the Email body, which brings the bold formatting with it, like the following.

Incident Reference: AA99999

It does this fine at the minute and it's essential for the people I'm making the database for. If anyone knows the answer to this I'll be enourmously grateful. I have Googled myself into a stupor trying to figure it out and come up blank. :banghead:

 

[jdraw]

I have not seen the issue myself. As a start I would recommend you adjust this line
MyItem.Subject = [[Status]] + " " + [[Incident Reference]] + " - " + [[Title]] + " - " + [[Incident Ranking]]

and replace the + with &

eg.

MyItem.Subject = Status & " " & [Incident Reference] & " - " & Title & " - " & [Incident Ranking]

That's really a guess.

Good luck.

 

[ELizabethC]

Thanks, I'll give that a go tomorrow back at work.

 

[ELizabethC]

I changed the line generating the subject. Still works okay. I still have the comma issue though. Time for some more Googling!

 

[ELizabethC]

Well in the end I skipped the report entirely and just generated a string to place directly into the email body. I know a little bit of HTML (probably quite old!) and they are just simple formatting. A little bit of bold for titles, that's about it. Ended up with the below, which works nicely and the emails look neat. And they don't lose their commas! It seems to be a bit faster too.

Function ExportHTML()
Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Dim strNextUpdateTitle
Dim strNextUpdate


Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)


' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If

If [Status] = "Resolved" Then
strNextUpdate = ""
strNextUpdateTitle = ""
Else
strNextUpdate = Left([Next Update-Time], 5)
strNextUpdateTitle = "Next Update: "
End If

strHTML = "<font face=""arial""><strong>Summary:</strong> " & [Title] & "<br /><br />" & _
"<strong>Status:</strong> " & [Status] & "<br /><br />" & _
[Details] & "<br /><br />" & _
"<strong>Impact:</strong> " & [Impact] & "<br /><br />" & _
"<strong>Incident Owner:</strong> " & [Incident Owner] & " - " & [Phone] & "<br /><br />" & _
"<strong>Incident Reference: </strong>" & [Incident Reference] & "<br /><br />" & _
"<strong>Incident Ranking: </strong>" & [Incident Ranking] & "<br /><br />" & _
"<strong>Next Steps: </strong>" & [Next Steps] & "<br /><br />" & _
"<strong>" & strNextUpdateTitle & "</strong>" & strNextUpdate & "</font>"


MyItem.HTMLBody = strHTML
MyItem.Subject = [Status] & " " & [Incident Reference] & " - " & [Title] & " - " & [Incident Ranking]
MyItem.Display

End Function