Compile error: Syntax error

[melodramatic]

I'm adding information to the email body of emails that I need to send out, and somewhere, I've obviously butchered it. I'm hoping that someone might be able to tell me what I screwed up here.

What I'm trying to do is add in two hyperlinks, directing the recipient to both a folder and a file. I'm also adding italics to one line, and don't know if maybe that caused my error.

Hoping you can help!

                If Range("D" & WorkLine) = "PQ" Or Range("D" & WorkLine) = "CH" Or Range("D" & WorkLine) = "BG" Then
                    If DCamount >= 100000 Then
                        emailBody = "The attached " & Range("H" & WorkLine) & " is being submitted to you, as its value is " & Format(Range("M" & WorkLine), "$#,###") & "." & vbNewLine & vbNewLine
                    End If
                End If
               
'THE TOP PORTION WORKS FINE FOR ME.  IT'S THIS BELOW CODING OF emailBody that gives me the Compile Error | Syntax error
 
                emailBody = emailBody & "Document control submittal: " & vbNewLine & _
                            "     " & DCN & vbNewLine & " | " & Range("G" & WorkLine) & " | " & Range("I" & WorkLine) & vbNewLine & _
                            "     " & <i>"submitted by " & Environ("username")</i> & vbNewLine & vbNewLine & _
                            "You may view project-specific QA documentation at the following locations:" & vbNewLine & vbNewLine & _
                            "<a href=" & QAfolder & "</a>" & vbNewLine & _ 'QAfolder shows path\
                            "<a href=" & QAfolder & "Document Control\" & Range("X" & WorkLine) & "</a>" 'Range "X" gives complete filename, including extension

 

[Micron]

What I do for line continuation problems is to break off the & _ portion onto one or two lines down then comment the remaining out. If what remains is OK, then the problem lies below. Then at the end of the OK portion I delete until the commented portion gets back into place and work with what's left. In the process, I changed so much to get it to stop being red text that I'm not sure where the fix(es) are, but this seems to be ok:

emailBody = emailBody & "Document control submittal: " & vbNewLine & _
  "     " & DCN & vbNewLine & " | " & Range("G" & WorkLine) & " | " & Range("I" & WorkLine) & vbNewLine & _
  "     <i>submitted by " & Environ("username") & "</i>" & vbNewLine & vbNewLine & _
  "You may view project-specific QA documentation at the following locations:" & vbNewLine & vbNewLine & _
  "<a href=" & QAfolder & "</a>" & vbNewLine & _
  "<a href=" & QAfolder & "Document Control\" & Range("X" & WorkLine) & "</a>" 'Range "X" gives complete filename, including extension

That being said, I often don't bother with line continuation format. I prefer something like

Dim emlBody As String
emlBody = emlBody & "Document control submittal: " & vbNewLine &  "     " & DCN & vbNewLine & " | " & Range("G" & WorkLine) & " | "
emlBody = emlBody & Range("I" & WorkLine) & vbNewLine & "     <i>submitted by " & Environ("username") & "</i>" & vbNewLine
emlBody = emlBody  & vbNewLine & "You may view project-specific QA documentation at the following locations:" & vbNewLine
emlBody = emlBody & vbNewLine & "<a href=" & QAfolder & "</a>" & vbNewLine
'Range "X" gives complete filename, including extension
emlBody = emlBody & "<a href=" & QAfolder & "Document Control\" & Range("X" & WorkLine) & "</a>"

I would then Debug.Print as I go to ensure the output is what I need, then disable the print.

P.S. VBA code tags are better for vba code, but that's just my opinion.

 

[Alex Blakenburg]

I think @Micron will have done more of this than I have but in addition to his code corrections it looks like you might converting from a standard text body to a HTML body.
This means that vbNewLine will also not work.
So you might want to add the code below to the start of your macro and the do a replace for vbNewLine with LineBreak in the code.

  Const LineBreak = "<br/>"

Also if you are outputting using ".Body =" this needs to change to ".HTMLBody =

 

[Micron]

Alex, good catch on the html part. I focused on fixing the syntax error when I should have gone further.

I have done a bit of this, but if I need htmlBody I don't think I ever used vbNewLine because html and vba are not compatible.
It has been a while, but for html (or rtf) email body I always used html syntax (<Br>, </Br> or <P> or </P> and so on). vbNewLine might work with plain text body, but IIRC I used Chr(13) and Chr(10) for that. If there were a lot of those, I'd make a string variable to hold both of them so I didn't have to type that combo over and over again.

 

[Artik]

but IIRC I used Chr(13) and Chr(10) for that. If there were a lot of those, I'd make a string variable to hold both of them so I didn't have to type that combo over and over again.

How about using vbCrLf instead of Chr(13) & Chr(10) ?

Artik

 

[Micron]

If it's a vb constant I don't think it works in a html email body, so that would include vbCrLf - but I can't remember. It was back around 2012 when I was doing this for my job.

 

[RoryA]

No, HTML requires the actual line break tag, as Alex posted earlier.

 

[Micron]

Well, maybe that's true now, but this worked back then. There is other code before and after this, but this would be the relevant parts:

htmHead = "<HTML><HEAD><META HTTP-EQUIV=3D" & Chr(34) & "CONTENT=3D"
htmHead = htmHead & Chr(34) & "text/html; = charset=3DISO-8859-1" & Chr(34) & ">"
htmHead = htmHead & "</HEAD>"
htmBody = "<body><font face=" & Chr(34) & "Arial" & Chr(34) & " size=" & Chr(34)
htmBody = htmBody & "2" & Chr(34) & ">"
htmClose = "</P></font></body></HTML>"

    With objMsg
        .FROM = eml.itemFrom
        .To = eml.itemTo
        .subject = eml.itemSubject
        .HTMLBody = eml.itemTo & vbCrLf & htmHead & htmBody & eml.itemBody & htmClose '<<< note the vbCrLf constant in this line
        .Send
    End With

So it was, or is, possible to use vbCrLf, or am I missing something?

 

[melodramatic]

OK, I've done several things here...

Gone to line by line on setting the emailBody
Replaced all vbNewLine with <br> (to work with the htmlbody)
Changed .Body to .HTMLBody

I'm still having problems with my links, as now I get nothing showing there. Let me play with it a bit more and then report back...

 

[RoryA]

or am I missing something

The vbCrLf in that line wasn't actually achieving anything I don't think. It was only the tags in the content that did anything. Carriage returns and white space are ignored in HTML.