I'm using excel vba to send emails to multiple recipients but not sure why im getting an "Argument not optional" error. Does this mean I can't choose my email body text based on different conditions? Is there a way to fix this?
The error i get highlights my "msg.HTMLBody" line.
Sub sendemails()
'Declares our variables:
Dim OA As Object
Set OA = CreateObject("Outlook.Application")
Dim msg As Outlook.MailItem
Dim paymentduedate As Date
Dim duedatenumber As Long
Dim todaysdate As Date
Dim todaysdatenumber As Long
Dim x As Long
'For loop to go through each row (x) in spreadsheet:
lastrow = Sheets("reportcsv").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
'turns paymentduedate into a number:
paymentduedate = Cells(x, 6).Value
duedatenumber = paymentduedate
Cells(x, 24).Value = duedatenumber
'turns todaysdate into a number:
todaysdate = Date
todaysdatenumber = todaysdate
Cells(x, 25).Value = todaysdatenumber
'turns remindersentdate into a number:
remindersentdate = Cells(x, 7).Value
remindersentnumber = remindersentdate
Cells(x, 26).Value = remindersentnumber
'gets 2weeks before duedatenumber:
TwoWeeksb4Due = duedatenumber - 13
'gets 1week after duedatenumber:
OneWeekAFTERDue = duedatenumber + 7
'If todaysdate is between TwoWeeksb4Due And todaysdatenumber then send reminder:
Dim jobrefvariable As String
If (todaysdatenumber >= TwoWeeksb4Due And todaysdatenumber < duedatenumber)
Set msg = OA.CreateItem(OAMailItem)
jobrefvariable = Cells(x, 4).Value
DueDate = Cells(x, 6).Value
Salutation = Cells(x, 2).Value
msg.Subject = "Invoice Ref " & jobrefvariable
msg.HTMLBody = "<html><body>Kindly Reminder</br>" _
& "<br>Dear " & Salutation & "<br></br>" _
& "<br>I hope you are doing well.<br></br>" _
& "<br>Please arrange payment for your upcoming order (invoice attached) by<b> " & Format(DueDate, "dd/mm") & ". </b><br></br>" _
& "<br><b>Our bank details; XXXXXXX, account number XXXXXX sort code XX-XX-XX. Please quote the job number, " & jobrefvariable & ", as a reference.</b><br></br>" _
& "<br>We look forward to receiving your payment.<br></br>" _
& "<br>Kind regards,<br></br>" _
& "<br><BODY style=font-size:13.5pt;font-family:Raleway><font color=DarkOrange> FIRSTNAME LASTNAME</font></body></br>" _
& "<font color=orange>Accounts Assistant</font><br></br>" _
& "<br>XXXXXXXXXX</br>" _
& "<br>XXXXXXXXXXXXXX</br>" _
& "<br>XXXXXX</br>" _
& "<br>XXXXXXX</br>" _
& "<br>XXX XXX</br>" _
& "<br>XXXX XXX XXXX</br>" _
& "<br><font color=Blue><u>accounts@XXXXXX.com</u></font></br>" _
& "<br><font color=Blue><u>www.XXXXX.com</br>" & "</u></font></body></html>"
msg.display
ElseIf todaysdatenumber = duedatenumber Then
Set msg = OA.CreateItem(OAMailItem)
jobrefvariable = Cells(x, 4).Value
DueDate = Cells(x, 6).Value
Salutation = Cells(x, 2).Value
msg.Subject = "PROFORMA - Main Hire Invoice Ref " & jobrefvariable
msg.HTMLBody = "<html><body>Kindly Reminder</br>" _ '<-- The error i get highlights this line...
& "<br>Dear " & Salutation & "<br></br>" _
& "<br>We have not received any payment yet.<br></br>" _
& "<br>Please arrange payment for your upcoming order (invoice attached) by <b>3pm today " & Format(, "dd/mm") & " </b>at the latest.<br></br>" _
& "<br><b>Our bank details; XXXXXXX, account number XXXXXX sort code XX-XX-XX. Please quote the job number, " & jobrefvariable & ", as a reference.</b><br></br>" _
& "<br>We look forward to receiving your payment.<br></br>" _
& "<br>Kind regards,<br></br>" _
& "<br><BODY style=font-size:13.5pt;font-family:Raleway><font color=DarkOrange> FIRSTNAME LASTNAME</font></body></br>" _
& "<font color=orange>Accounts Assistant</font><br></br>" _
& "<br>XXXXXXXXXX</br>" _
& "<br>XXXXXXXXXXXXXX</br>" _
& "<br>XXXXXX</br>" _
& "<br>XXXXXXX</br>" _
& "<br>XXX XXX</br>" _
& "<br>XXXX XXX XXXX</br>" _
& "<br><font color=Blue><u>accounts@XXXXXX.com</u></font></br>" _
& "<br><font color=Blue><u>www.XXXXX.com</br>" & "</u></font></body></html>"
msg.display
ElseIf todaysdatenumber = OneWeekAFTERDue Then
Set msg = OA.CreateItem(OAMailItem)
jobrefvariable = Cells(x, 4).Value
DueDate = Cells(x, 6).Value
Salutation = Cells(x, 2).Value
msg.Subject = "Invoice Ref " & jobrefvariable
msg.HTMLBody = "<html><body>Dear " & Salutation & "</br>" _
& "<br>We have not received any payment yet. Invoice attached is now 7 days overdue.<br></br>" _
& "<br>Please arrange payment for your upcoming order (invoice attached) as soon as possible to avoid cancellation/late delivery.<br></br>" _
& "<br><b>Our bank details; XXXXXXX, account number XXXXXX sort code XX-XX-XX. Please quote the job number, " & jobrefvariable & ", as a reference.</b><br></br>" _
& "<br>We look forward to receiving your payment.<br></br>" _
& "<br>Kind regards,<br></br>" _
& "<br><BODY style=font-size:13.5pt;font-family:Raleway><font color=DarkOrange> FIRSTNAME LASTNAME</font></body></br>" _
& "<font color=orange>Accounts Assistant</font><br></br>" _
& "<br>XXXXXXXXXX</br>" _
& "<br>XXXXXXXXXXXXXX</br>" _
& "<br>XXXXXX</br>" _
& "<br>XXXXXXX</br>" _
& "<br>XXX XXX</br>" _
& "<br>XXXX XXX XXXX</br>" _
& "<br><font color=Blue><u>accounts@XXXXXX.com</u></font></br>" _
& "<br><font color=Blue><u>www.XXXXX.com</br>" & "</u></font></body></html>"
msg.display
Else
Cells(x, 10) = "No"
Cells(x, 10).Font.Bold = False
Cells(x, 10).Font.ColorIndex = xlAutomatic
Cells(x, 10).Interior.Pattern = xlNone
End If
Next x
End Sub