Send email integrating cell data into Subject and Body, with attached invoice

Hello,

I got some code online a while ago and started tweaking to my needs (as much as possible) , but being completely new to Macros, I'm stuck.

Every month I get a list of past due customers, and part of my job is making sure they receive a reminder email, with the details of what they owe, and following up with a second and even third reminder if they don't pay after the first one.

The spreadsheet I receive from corporate contains
- Account #
- Account Name
- Sales Rep's ID number
- Original Due date
- Minimum amount due
- Date they hit 30 days, 45 days, and 60 days past due (each in own column)
- Payments and adjustments and the dates we received
- Total due (which is the minimum plus the current month's services)

Then I add:
- Client's email
- Sales rep's email
- A calculation of how many days past due they are
- And three columns for 1st, 2nd, and 3rd notifications to indicate when I contacted them

Here is what I want the script to do, in laymen's terms:

1) Look in the 1st, 2nd, and/or 3rd Reminder Notification columns. If empty, assess whether the date in the 30, 45, or 60 day columns are equal or later than today's date. If so, initiate the sending of the email. (ie, if 1st Reminder field is empty, look at 30 day date, if 1st Reminder is filled but 2nd Reminder is empty, look at 45 day date, and so on).
2) Email specs
- Use the contents of "Client's email" as the "to" address
- Use the contents of "Reps's email" as the "cc" address
- Format the Subject line so it says "Reminder - account for {Client's acct # and client's acct name} is {days} past due"
- Format the Body so it says
"Dear {account name} - Thank you for being a valued customer"
"Line one text about if you've already paid, disregard"
"Line two text stating the account is now {days} past due. Please remit {Minimum due} to avoid service interruption."
"If you have questions, please see your attached statement, or call us at 555-555-5555"
"Thank you"

3) OPTION 1 - Attach pre-saved PDF file, the path for which can be found in column on spreadsheet named "Attachment File Path". (for this method, the only manual input I would need is to save each client's invoice separately using a pre-determined file-naming convention. Then run the macro so Excel can send the emails. This is not the ideal method, but at this point I'm desperate and will do anything)

OPTION 2 - Instead of saving PDF invoices into a folder, the other alternative is I could copy/paste each invoice into a new Workbook, adding each invoice to a new sheet. So for example, the invoice for the customer on Row 10 of the past due spreadsheet, could correspond to the Workbook with the invoices (let's call the filename "PastDueInvoices.xlsx"), Sheet 10 (to maintain consistency). The customer on Row 11, their invoice would be on Sheet 11, and so forth.... In this case, have the Macro copy the contents of the corresponding sheet, and paste that into the email body along with the text above.

OPTION 3 - The two top ways would work, but would still involve me personally being involved in the process each day. Better than nothing, but here is what would rock my world if it's possible: Copy the account number from the first column, open a browser window to www.example.com (the exact URL is accessible only on our server so if I gave it to you, it wouldn't do you any good), and paste the account number into the field named "newzware id" and 'click' Submit. Then Select All, Copy, and Paste directly into the Body of the email, or if that's not possible, paste into workbook sheet, then convert that sheet to email body, or if not possible, convert sheet to PDF and add as attachment.

4) After the email is sent, add today's date into the corresponding 1st, 2nd, or 3rd notification column accordingly. (this part of the code I pretty much had figured out using someone's template).

The code I have currently is:

Sub SendReminders()
Option Explicit

Public Sub SendReminderNotices()
' ****************************************************************
' Define Variables
' ****************************************************************
Dim wkbReminderList As Workbook
Dim wksReminderList As Worksheet
Dim lngNumberOfRowsInReminders As Long
Dim i As Long

' ****************************************************************
' Set Workbook and Worksheet Variables
' ****************************************************************
Set wkbReminderList = ActiveWorkbook
Set wksReminderList = ActiveWorkbook.ActiveSheet

' ****************************************************************
' Determine How Many Rows Are In the Worksheet
' ****************************************************************
lngNumberOfRowsInReminders = wksReminderList.Cells(Rows.Count, "A").End(xlUp).Row

' ****************************************************************
' For Any Items That Don't Have A Date In Columns 19 or 20 or 21,
' Check To See If The Reminder Is Due.
'
' If Reminder Is Due, then Send An Email.
' If Successful, Log The Date Sent in Column 19 or 20 or 21
' ****************************************************************

For i = 2 To lngNumberOfRowsInReminders
' ****************************************************************
' First Reminder Date Check
' ****************************************************************
    If wksReminderList.Cells(i, 19) = "" Then
        If wksReminderList.Cells(i, 7) <= Date Then
            If SendAnOutlookEmail(wksReminderList, i) Then
                wksReminderList.Cells(i, 19) = Date 'Indicate That Reminder1 Was Successful
            End If
        End If
    Else
' ****************************************************************
' Second Reminder Date Check
' ****************************************************************
        If wksReminderList.Cells(i, 20) = "" Then
            If wksReminderList.Cells(i, 8) <= Date Then
                If SendAnOutlookEmail(wksReminderList, i) Then
                    wksReminderList.Cells(i, 20) = Date 'Indicate That Reminder2 Was Successful
                End If
            End If
        End If
    End If
    
    ' ****************************************************************
' Third Reminder Date Check
' ****************************************************************
        If wksReminderList.Cells(i, 21) = "" Then
            If wksReminderList.Cells(i, 9) <= Date Then
                If SendAnOutlookEmail(wksReminderList, i) Then
                    wksReminderList.Cells(i, 21) = Date 'Indicate That Reminder3 Was Successful
                End If
            End If
        End If
    End If
Next i

End Sub

Private Function SendAnOutlookEmail(WorkSheetSource As Worksheet, RowNumber As Long) As Boolean
Dim strMailToEmailAddress As String
Dim strSubject As String
Dim strCc As String
Dim strBody As String
Dim OutApp As Object
Dim OutMail As Object

SendAnOutlookEmail = False

strMailToEmailAddress = WorkSheetSource.Cells(RowNumber, 16)
strCc = WorkSheetSource.Cells(RowNumber, 17)
strSubject = "FloridaKeys.com" & (Sheet1.Cells(i, 24)) & "Day Accounting Reminder: #" & (Sheet1.Cells(i, 1)) & (Sheet1.Cells(i, 2))
End Sub
 
strBody = "Thank you" & (Sheet1.Cells(i, 2)) & "for being a valued customer." & vbCrLf & _
          "This is an automated email reminder.  If you have already sent payment, please disregard." & vbCrLf & _
          "" & vbCrLf & _
          "Your account is now past due by" & (Sheet1.Cells(i, 24)) & "days. To avoid service interruption, please remit payment for" & (Sheet1.Cells(i, 15)) & vbCrLf & _
          "Your invoice is attached.  Please remit payment to 555 Ourdrive Street, Key West FL 33040, or call 555-555-5555 ext 5, to pay by phone." & vbCrLf & _
          "If you have questions, please let us know." & vbCrLf & _
          "" & vbCrLf & _
          "Thank You"

' ****************************************************************
' Create The Outlook Mail Object
' ****************************************************************
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon "Outlook"
Set OutMail = OutApp.CreateItem(0)

' ****************************************************************
' Send The Email with High Priority Status
' ****************************************************************
On Error GoTo ErrorOccurred
With OutMail
    .Importance = 2
    .To = strMailToEmailAddress
    .Subject = strSubject
    .Body = strBody
    .Send
End With

' ****************************************************************
' Mail Was Successful
' ****************************************************************
SendAnOutlookEmail = True

Continue:
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Exit Function

' ****************************************************************
' Mail Was Not Successful
' ****************************************************************
ErrorOccurred:

Resume Continue
End Function[/INDENT]


End Function

Problem 1 - what I have even here is not really working. I keep getting compile errors and all sorts of weird issues. I don't really know what I'm looking at on most of it, so it's possible I wacked it up pretty bad.

Problem 2 - It only initiates the email and saves the notification. I do not know the best way to go about copying/pasting or saving the invoice as an attachment on the email. Please advise on the best way to do this.


Thank you for your help.Past Due Reminders List.xlsm
Karrie