If the answer to the above is yes, then this should work:
Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Outlook.MailItem
Dim signature As String
EmailTo = Worksheets("E-mail Sheet").Range("B26")
Dim Found As Range
EmailTo = Worksheets("E-mail Sheet").Range("B26")
Set Found = Sheets("Current Clients").Columns("C").Find(What:=Sheets("E-mail Sheet").Range("A26").Value, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Found Is Nothing Then
If IsEmpty(Found.Offset(, -2)) = False Then
If MsgBox(Range("A26").Value & vbNewLine & vbNewLine & "An item request has been sent on: " & Found.Offset(, -2).Value & vbNewLine & _
"Items were recieved on: " & Found.Offset(, -1).Value & vbNewLine & vbNewLine & "See Current Clients page for more information" & vbNewLine & vbNewLine _
& "Do you want to continue?", vbYesNo) = vbNo Then
Exit Sub ' stop macro if user selects No
End If
End If
End If
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.display
signature = .htmlBody
.To = Worksheets("E-mail Sheet").Range("B26")
.CC = Worksheets("E-mail Sheet").Range("B19")
.BCC = ""
.Subject = "Updating Credit File - " & Worksheets("E-mail Sheet").Range("D26")
.htmlBody = "Dear " & Worksheets("E-mail Sheet").Range("C26") & "," & "<br><br>" & _
"We are requesting the following information to bring your credit file up to date." & "<br><br>" & Worksheets("E-mail Sheet").Range("B2") _
& Worksheets("E-mail Sheet").Range("C2") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B3") & Worksheets("E-mail Sheet").Range("C3") _
& "<br><br>" & Worksheets("E-mail Sheet").Range("B4") & Worksheets("E-mail Sheet").Range("C4") & "<br><br>" & Worksheets("E-mail Sheet").Range("B5") _
& Worksheets("E-mail Sheet").Range("C5") & "<br><br>" & Worksheets("E-mail Sheet").Range("B6") _
& Worksheets("E-mail Sheet").Range("C6") & "<br><br>" & Worksheets("E-mail Sheet").Range("B7") _
& Worksheets("E-mail Sheet").Range("C7") & "<br><br>" & Worksheets("E-mail Sheet").Range("B8") _
& Worksheets("E-mail Sheet").Range("C8") & "<br><br>" & Worksheets("E-mail Sheet").Range("B9") _
& Worksheets("E-mail Sheet").Range("C9") & "<br><br>" & Worksheets("E-mail Sheet").Range("B10") _
& Worksheets("E-mail Sheet").Range("C10") & "<br><br>" & Worksheets("E-mail Sheet").Range("B11") _
& Worksheets("E-mail Sheet").Range("C11") & "<br><br>" & Worksheets("E-mail Sheet").Range("B12") _
& Worksheets("E-mail Sheet").Range("C12") & "<br><br>" & Worksheets("E-mail Sheet").Range("B13") _
& Worksheets("E-mail Sheet").Range("C13") & "<br><br>" & Worksheets("E-mail Sheet").Range("B14") _
& Worksheets("E-mail Sheet").Range("C14") & "<br><br>" & Worksheets("E-mail Sheet").Range("B15") _
& Worksheets("E-mail Sheet").Range("C15") & "<br><br>" & Worksheets("E-mail Sheet").Range("B16") _
& Worksheets("E-mail Sheet").Range("C16") & "<br><br>" & Worksheets("E-mail Sheet").Range("B17") _
& Worksheets("E-mail Sheet").Range("C17") & "<br><br>" & "If possible please provide us this information by " & Worksheets("E-mail Sheet").Range("A2") _
& "<br><br>" & "If you have any questions please contact " & Worksheets("E-mail Sheet").Range("A19") & "at " & Worksheets("E-mail Sheet").Range("C19") _
& "<br><br>" & "Sincerely," & "<br><br>" & signature
.display
.SendUsingAccount = OutApp.Session.Accounts.Item(2)
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Bookmarks