I've created a log that tracks our office library resources. When a checked out book becomes overdue, I've added code that automates sending an email to the person who checked out the resource notifying them of the overdue book. The code below works beautifully and i'm able to successfully generate the email and send, but only with the .body set to specific text. I would like to also add a carriage return and insert the title of the book that is past due. Below is the code I'm struggling with.
Any help would be much appreciated!
[SIZE=1][I]Private Sub Worksheet_Change(ByVal Target As Range) If Range("H3").Value = "OVERDUE" Then sendBook (Range("I3").Text) End If End Sub[/I][/SIZE]
[SIZE=1][I]Sub sendBook(theAddy As String) Application.ScreenUpdating = False Application.EnableEvents = False Dim wb As Workbook, wasOpen As Boolean On Error Resume Next wasOpen = True Workbooks(Sheet1).Activate If Err <> 0 Then Set wb = Workbooks.Open("C:\Users\op936sp\Documents\ADMIN\Library\Library Check In-Check Out.xlsx") wasOpen = False Err.Clear Else Set wb = ActiveWorkbook wb.Save End If Dim olApp As Object, olMsg As Object Set olApp = CreateObject("Outlook.Application") Set olMsg = olApp.CreateItem(0) With olMsg .To = theAddy .cc = (my email address) .Subject = "Overdue Library Book Alert" .Body = "Please return the overdue resource listed below to MS 56. If you would like to request an extension, please contact Stephanie Kutchinski at ext. 4686." [SIZE=1][COLOR=#ff0000][B](Here is where is want to insert a carriage return and include the library book title found in column A) [/B][/COLOR][/SIZE] .Send End With If wasOpen = False Then wb.Close True End If Application.ScreenUpdating = True Application.EnableEvents = True Set olApp = Nothing Set olMsg = Nothing End Sub[/I][/SIZE]
Last edited by a moderator: