Macro converted to Code but has error

[kingdoz]

Hi All, I have converted a macro to code as I need to add acExportQualityPrint so my PDF that gets emailed retains good quality. I would have just kept it as a Macro (if anyone knows how to do it within Macro) but by efault the Macro doesnt have the option to change quality on sendobject.

So I converted to a module (below) but I get a Compile Error when I run it. I dont know much about VBA so am stuck. Any help?
Thanks

Option Compare Database
'------------------------------------------------------------
' INVOICEFOREMAIL
'
'------------------------------------------------------------
Function INVOICEFOREMAIL()
On Error GoTo INVOICEFOREMAIL_Err
    With CodeContextObject
        DoCmd.GoToRecord , "", acNext
        DoCmd.GoToRecord , "", acPrevious
        DoCmd.OpenReport "InvoiceEmail", acViewPreview, "", "[Invoice No]=[Forms]![Invoices]![Invoice No]"
        DoCmd.SendObject acReport, "", "PDFFormat(*.pdf)", .Email, .Email2, .Email3, .Email_Subject, "Dear " + .Title + " " + .FirstName + " " + .LastName + "," + "  "
Please Print Attached Invoice Number" + .Invoice + ".
Our payment terms are BY RETURN unless pre-agreed. We appreciate speedy payment & thank you for your custom.
Accounts Department ", True, """
    End With
 
INVOICEFOREMAIL_Exit:
    Exit Function
INVOICEFOREMAIL_Err:
    MsgBox Error$
    Resume INVOICEFOREMAIL_Exit
End Function

 

[Uncle Gizmo]

Change the first line, this one: Option Compare Database

To two Lines:
Option Compare Database
Option Explicit

 

[kingdoz]

Thanks for that but unfortnately im still getting the compile error syntex message.
for your reference, I have colour coded exactly how it looks. Maybe that helps?


Option Compare Database
Option Explicit

'------------------------------------------------------------
' INVOICEFOREMAIL
'
'------------------------------------------------------------
Function INVOICEFOREMAIL()
On Error GoTo INVOICEFOREMAIL_Err
With CodeContextObject
DoCmd.GoToRecord , "", acNext
DoCmd.GoToRecord , "", acPrevious
DoCmd.OpenReport "InvoiceEmail", acViewPreview, "", "[Invoice No]=[Forms]![Invoices]![Invoice No]"
DoCmd.SendObject acReport, "", "PDFFormat(*.pdf)", .Email, .Email2, .Email3, .Email_Subject, "Dear " + .Title + " " + .FirstName + " " + .LastName + "," + " "
Please Print Attached Invoice Number" + .Invoice + ".
Our payment terms are BY RETURN unless pre-agreed. We appreciate speedy payment & thank you for your custom.
Accounts Department. ", True, """
End With

INVOICEFOREMAIL_Exit:
Exit Function
INVOICEFOREMAIL_Err:
MsgBox Error$
Resume INVOICEFOREMAIL_Exit
End Function

 

[pbaldy]

Your string ends at the end of the SendObject line, so the part beginning with Please is not contained within it. Access doesn't know what to make of it, thus the compile error. You need to keep concatenating literal text with field references as you did at the beginning of that string. Given its length, I'd create a string variable and then use that in the SendObject.

 

[kingdoz]

Thanks Pbaldy but I still cant get my head around it. No matter how many commas I add its still breaks. Any more help on how it should look? I really am stuck on this.
Thanks very much.

 

[kingdoz]

OK I fixed it as below; and the compile error message is now gone, only to be replaced with a new one.

"an expression you entered is the wrong datatype for one of the arguments"..

Any ideas?


Option Compare Database
Option Explicit
'------------------------------------------------------------
' INVOICEFOREMAIL
'
'------------------------------------------------------------
Function INVOICEFOREMAIL()
On Error GoTo INVOICEFOREMAIL_Err
With CodeContextObject
DoCmd.GoToRecord , "", acNext
DoCmd.GoToRecord , "", acPrevious
DoCmd.OpenReport "InvoiceEmail", acViewPreview, "", "[Invoice No]=[Forms]![Invoices]![Invoice No]"
DoCmd.SendObject acReport, "", "PDFFormat(*.pdf)", .Email, .Email2, .Email3, .Email_Subject, "Dear " + .Title + " " + .FirstName + " " + .LastName + "," + " " + "Please Print Attached Invoice Number" + .Invoice + "." + " Our payment terms are BY RETURN unless pre-agreed. We appreciate speedy payment & thank you for your custom." + "Accounts Department. ", True, """"""
End With
INVOICEFOREMAIL_Exit:
Exit Function
INVOICEFOREMAIL_Err:
MsgBox Error$
Resume INVOICEFOREMAIL_Exit
End Function

 

[pbaldy]

Which line? You may have to temporarily comment out the On Error line. Offhand, the only thing that jumps out is the format argument, which should probably be acFormatPDF.

 

[kingdoz]

Well i played with it a bit more and seem it doesnt like
.Email2, .Email3,

I basically took out everything after that...
Weird

 

[pbaldy]

Were those populated with valid email addresses? Not sure if it would like it if they were Null. You'd probably need to use the Nz() function or some other method to handle that.