Solved Compile error one line below a line that works (1 Viewer)

[dgreen]

My code to generate an email from meeting notes is getting close. I want to add are the Rich Text formatted notes from the field Me. Key_Items_Discussed.

At this point, adding in Me. Key_Items_Discussed to the HTMLbody string causes the code to error (won't compile). Pull that field out and it compiles.

                    .Subject = Me.Event & " (" & Format(Me.Start_Date, "yyyy.mm.dd") & ")"
                    .HTMLBody = "Team, See the attachment reference <b>" & Me.Event & "</b>.  If you have any questions please let me know." & vbNewLine & Me.Key_Items_Discussed & vbNewLine & Signature

The below is what the email looks like, with me pasting in the field afterwards, which is my work around.

 

[Micron]

Usually the offending part is highlighted (or at least the entire line) and there is something about the error message that provides a clue, yes?
Maybe we could know that?

Or, IIRC, I never got vbNewLine to work with email - at least not when using CDO. I stuck to html encoding and had no more issue.
So that's my guess based on a pretty much what is otherwise a total lack of information.

 

[dgreen]

It highlights me.Event with a Compile error. Which is weird since the same me.event is on the subject line (which fired just prior to the HTML line) and it works fine.
Also what you see below in the screen capture includes the commented out line, which works but I just wanted to improve it with the text from Me.Key_Items_Discussed.

Usually the offending part is highlighted

I'll follow up on this in the am.

 

[plog]

This is unkosher in a couple of ways. HTML is not Rich Text is not Visual Basic. Your trying to mash 2 types of formatting into a third one.

The vbNewline is an easy conversion to HTML ("<p>" or "<br>"). The Rich Text is not so simple. I'm sure theres some module out there that you can run that string through and it will spit out something HTML compatible, or at least strip the Rich Text formatting.

I think you need to build your .HTMLBody string piecemeal--just get something to work then add to it until you can find the offender. Start with just a literal string like this:

.HTMLBody = "Team, See the attachment reference <b>"

When that works try this:

.HTMLBody = "Team, See the attachment reference <b>"& Me.Event

When that works, add the next piece. Divide and conquer.

 

[June7]

Then I suggest true source of error is Me.Key_Items_Discussed since line without it works. Sometimes error message does not identify actual cause.

 

[dgreen]

Yes, that is the error. Anyone have a way to insert that data field within an email body?

true source of error is Me.Key_Items_Discussed since line without it works

 

[June7]

I did a test with vbNewLine and PlainText() function. Code compiles and runs, however vbNewLine doesn't translate in the HTML body construct.

 

[dgreen]

What does your code look like? I tried the below and that errored as well at the same point.

.HTMLBody = "Team, See the attachment reference <b>" & Me.Event & "</b>.  If you have any questions please let me know." & PlainText(Me.Key_Items_Discussed) & Signature

I did a test with vbNewLine and PlainText() function. Code compiles and runs, however vbNewLine doesn't translate in the HTML body construct.

 

[June7]

Just tagged onto end of code already had in general module.

.HTMLBody = "Test HL: <a href='C:\Users\" & Environ("USERNAME") & "\Desktop\Shortcut to Main in Umpires.accdb.MAF'>Link description here</a>" & vbNewLine & Forms!Rates.Tst

I removed PlainText() function and everything still works and, lo and behold, the vbNewLine does carry through as well as the RichText formatting.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Micron]

The message implies that Access thinks Event is a method or data member of ME. I checked but it is not in Allen Browne's reserved list but I would never use it in code like that. I realize it doesn't seem to be the error at play, but still... I guess the next thing is to substitute a literal string for Me.Key_Items_Discussed and see what happens.

 

[dgreen]

@Micron

I must not understand what vbNewLine does. It apparently doesn't create a new line in the HTML string. The following code (yours and mine) generated everything on one row (until it got to the signature and it put it in the right spot.

.HTMLBody = "Test HL: <a href='C:\Users\" & Environ("USERNAME") & "\Desktop\Shortcut to Main in Umpires.accdb.MAF'>Link description here</a>" & vbNewLine & "Team, See the attachment reference <b>" & Me.Event & "</b>.  If you have any questions please let me know." & vbNewLine & Signature

Here's the snip it from the email that was generated with the attachment showing on top and the top portion of the signature block on the bottom.

Putting in a string works. Now how to get the Long text field in? At this point I'd take a plaintext conversion, which I've tried and failed to implement.

 

[dgreen]

By the way, thanks for showing how to generate a hyperlink. That's going to be useful.

Just tagged onto end of code already had in general module.

 

[Minty]

I would strongly suggest creating your HTML content as a separate string variable. Then simply reference that in your outlook code.
It removes any ambiguity as to where the error occurs and you can then debug.print it out and dump it into a suitable HTML reader to see what it looks like without having to rely on creating an email just to see it.

vbNewLine is a constant that VBA recognises as Chr(34) or something similar, not an HTML formatting code.
You want <b> or <p> as line or paragraph feeds.

Edit - Outlook is a poor renderer of HTML as well, check it in another email reader to see what it really could look like.

 

[Micron]

The following code (yours and mine)

I don't think I provided any? Regardless, as I said, it should work if you drop the vbNewLine thing. I don't see the problem with using Outlook for testing vs some other file seeing as how your final result will be an email anyway. No point in getting something to work only to find that the email isn't rendered the same way, and you're not pestering anyone with emails anyway. Whether or not you create a string variable and some other file to test with, this should work if I understand the goal:
.HTMLBody = "Test HL: <a href='C:\Users\" & Environ("USERNAME") & "\Desktop\Shortcut to Main in Umpires.accdb.MAF'>Link description here</a><P>Team, See the attachment reference <b>" & Me.Event & "</b>. If you have any questions please let me know.<P>" & Signature

 

[Micron]

Maybe you would find this style of coding long strings easier to read?

Dim strBody As String
....
strBody = "Test HL: <a href='C:\Users\" & Environ("USERNAME") & "\Desktop\Shortcut to Main in Umpires.accdb.MAF'>
strBody = strBody & Link description here</a><P>Team, See the attachment reference <b>" & Me.Event
strBody = strBody & "</b>.  If you have any questions please let me know.<P>" & Signature

.HTMLBody = strBody

If not, at least maybe line continuation character style.

 

[Minty]

@Micron - Your second post was what I was trying to get to.
I have had some strange things happen in Outlook automation when referring directly to Access objects. I can't remember the exact scenario, but it caused a strange error.
As a result, I got in the habit of always sticking them into holding variable first.

 

[dgreen]

Thanks for the push to adjust the code.

I now have the email body string (.HTMLbody = strBody) wrapping to the next line <p>, included a hyperlink to the dynamic SharePoint folder based on the specific event <a href > </a> and the meeting highlights which were stored in another table (Dlookup).

And it's possible that all of these improvements wouldn't have happened if I had figured out that I just needed to do the DLookup of the Key_Items_Discussed which was in a different table than the Event_ID.

This is good.