I got it! Had to Frankenstein some different codes together, but I got it to work. Check it out below (I used the full code which also pulls from a master file in excel to populate the data).
Thanks for the help!
Code:Sub Follow_Up() Dim c As Range, f As Range, source As Worksheet, master As Worksheet, s As String, StrBody As String Dim Fldr As Outlook.Folder Dim olMail As Variant Dim olReply As Outlook.MailItem Dim olItems As Outlook.Items Dim i As Integer Dim IsExecuted As Boolean Set source = Worksheets("Request List") Set master = Worksheets("Master List") Set olApp = New Outlook.Application For Each c In source.Range("A2", source.Cells(source.Rows.Count, "A").End(xlUp)) With c If IsEmpty(.Offset(, 3).Value) = True Then GoTo NextC If .Offset(, 4).Value <> True Then GoTo NextC If .Offset(, 6).Value <> "NO" Then GoTo NextC Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = Session.GetDefaultFolder(olFolderInbox) IsExecuted = False Set olItems = Fldr.Items olItems.Sort "[Received]", True For i = 1 To olItems.Count Set olMail = olItems(i) If InStr(olMail.Subject, "Document Request: " & c.Offset(, 1).Value) > 0 Then If Not olMail.Categories = "Executed" Then Set olReply = olMail.replyall With olReply StrBody = "<BODY style=font-size:11pt;font-family:Calibri>Hello " & "<br>" & _ "<p>Following up with the below. May you please advise?" & _ "<p>Thank you," & vbCrLf & vbCrLf & "<br>" & _ "<p>" & Session.CurrentUser.Name .HTMLBody = StrBody & .HTMLBody emailReady = True .Display c.Offset(, 6).Value = "YES" 'Source sheet sent, YES. c.Offset(, 7).Value = Date 'Source sheet, Date sent. c.Offset(, 8).Value = Session.CurrentUser.Name End With Exit For olMail.Categories = "Executed" IsExecuted = True End If End If Next i End With NextC: Next c On Error Resume Next Set olMail = Nothing Set olApp = Nothing End Sub
Reply With Quote
Bookmarks