Results 1 to 5 of 5

Thread: Excel VBA to insert cell data into outlook email

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Excel VBA to insert cell data into outlook email

    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.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("H3").Value = "OVERDUE" Then
     sendBook (Range("I3").Text)
    
    End If
     End Sub

    And:

    Code:
    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." (Here is where is want to insert a carriage return and include the library book title found in column A) 
     .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
    Any help would be much appreciated!
    Last edited by Bob Phillips; 2014-04-30 at 08:25 AM. Reason: Addeded code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •