skutchinski
New member
- Joined
- Apr 29, 2014
- Messages
- 3
- Reaction score
- 0
- Points
- 0
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.
And:
Any help would be much appreciated!
Code:
[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]
And:
Code:
[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: