Format formula for body text code-get syntax error

[howard]

I have the following code below

 strBody = "Hi " & Sheets("Consolidated Auto").Range("S2") & vbNewLine & vbNewLine & _
            "Attached, please find " & Sheets("BTR1").Range("A1") & vbNewLine & vbNewLine & _
            "These amount to " & Sheets("BTR1) &format(Range("G1"), "R#,##0.00") & "vbNewLine & vbNewLine & _
            "Please follow up on matters and give me feedback" & vbNewLine & vbNewLine & _
            "Regards" & vbNewLine & vbNewLine & _
            "Howard"

I am trying to get the formula to generate the format value in cell G! to generate the value as for eg R 1,110,415.75 correct

  "These amount to " & Sheets("BTR1) &format(Range("G1"), "R#,##0.00") & "vbNewLine & vbNewLine & _

I get a syntax error


It would be appreciated if someone could assist me

 

[jkpieterse]

"These amount to " & Sheets("BTR1") & format(Range("G1"), "R#,##0.00") & vbNewLine & vbNewLine & _

 

[NdNoviceHlp]

HTH Howard...

"These amount to " & Format(Sheets("BTR1").Range("G" & 1).Value, "R#,##0.00") & vbNewLine & vbNewLine

Dave

 

[howard]

Thanks for the response

when running the macro, I get object does'nt suppot this object or method

Kindly test & corrrect

 strBody = "Hi " & Sheets("BTR1").Range("S2") & vbNewLine & vbNewLine & _
            "Attached, please find " & Sheets("BTR1").Range("A1") & vbNewLine & vbNewLine & _
           "These amount to " & Sheets("BTR1") & Format(Range("G1"), "R#,#0.00") & vbNewLine & vbNewLine & _
            "Please follow up on these matters and give me feedback" & vbNewLine & vbNewLine & _
            "Regards" & vbNewLine & vbNewLine & _
            "Howard"

See Full code below

 Sub Email()

With Sheets("BTR1")

If .Range("G1") = 0 Then

Exit Sub


Else

End If

End With

Dim File As String, strBody As String

Application.ScreenUpdating = False

Application.DisplayAlerts = False

File = Environ$("temp") & "\" & Format(Range("Q2"), "mmm-yy ") & Format(Now, "dd-mmm-yy h-mm-ss") & ".xlsx"

strBody = "Hi " & Sheets("BTR1").Range("S2") & vbNewLine & vbNewLine & _

"Attached, please find " & Sheets("BTR1").Range("A1") & vbNewLine & vbNewLine & _

"These amount to " & Sheets("BTR1") & Format(Range("G1"), "R#,#0.00") & vbNewLine & vbNewLine & _

"Please follow up on these matters and give me feedback" & vbNewLine & vbNewLine & _

"Regards" & vbNewLine & vbNewLine & _

"Howard"


ActiveWorkbook.Save

Sheets("BTR1").Copy

With ActiveWorkbook

.SaveAs Filename:=File, FileFormat:=51

.Close savechanges:=False

End With

With CreateObject("Outlook.Application").CreateItem(0)

.Display

.to = Join(Application.Transpose(Sheets("BTR1").Range("R2:R5").Value), ";")

.Subject = Sheets("BTR1").Range("A1")

.body = strBody

.Attachments.Add File

'.Send

End With

Kill File

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

 

[howard]

Thanks for the help Dave. My apologies that I overlooked this earlier

Your code works perfectly

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Be safe. Dave

 

[jkpieterse]

Duh, such a stupid answer by me in #2. Apologies

 

[howard]

Duh, such a stupid answer by me in #2. Apologies

No problem -It happens to all of us from time to time

 

[howard]

These amount to " & Format(Sheets("BTR1").Range("G" & 1).Value, "R#,##0.00") & vbNewLine & vbNewLine

Hi Dave I have a similar type problem with another macro below, where nothing is being generated in the body of the email

 strBody = strBody & "Attached Please find " & Range("B4") & Format(Sheets("Summary").Range("C" & 1).Value, "mmm-yyyyy") & vbNewLine & vbNewLine

C1 contains the month and year

It would be appreciated if you could amend this

 

[NdNoviceHlp]

I Think U had too many "y"'s The cells have to have strings, so use the CSTR function. HTH. Dave

strBody = strBody & "Attached Please find " & CStr(Sheets("Summary").Range("B4").Value) _
      & CStr(Format(Sheets("Summary").Range("C" & 1).Value, "mmm-yyyy")) & vbNewLine & vbNewLine