List values from column X in e-mail when cell in column Y is marked

[Falloff]

Hi everyone,

since this is the first time, that I have not found the solution to an issue here in the forum, I thought I'd go ahead and finally post a question myself.
(so first off - thanks for all the previous help)

I am trying to come up with some VBA code that basically does the following:
A colleague marks interesting reports or files with an "X" in column F and, when clicking "Request", automatically sends me an e-mail with the values from column A. The mail basically would look something like this:

"Hello, please send the following:
- Report A
- Report C
- File 12
Thanks."
'Big' question now is, how can I display this text correctly? The code to generate and send the mail seems to be working fine, so we are only talking about the actual text.
What I have so far is this:

Set MailDoc = Maildb.CreateDocument
    
        MailDoc.Form = "Memo"
        Recipient = "mail@xyz.com"
        MailDoc.SendTo = Recipient
        lastRow = Cells(Rows.Count, "F").End(xlUp).Row
        
        If InStr(1, Range("F3:F", lastRow), "X") Then
             
         MailDoc.Subject = "Pending Request"
            MailDoc.Body = _
        "Please send me the following:" & vbCrLf & vbCrLf & Range("F3:F", lastRow).Offset(0, -5).Value
        
        Else
        
        End If
        MailDoc.SaveMessageOnSend = True
        MailDoc.PostedDate = Now()
            On Error GoTo errorhandler1
        MailDoc.Send 0, Recipient

End Sub

If anyone can help, thanks in advance!

 

[mikenelena]

Hi Falloff,

I hope you don't mind. It was easier to write code from scratch than to build off of what you had. I'm not sure if this will be helpful to you, or if you are committed to the code you have, but...

Sub RequestReports()
    Dim lastRow As Long
    Dim i As Long
    Dim reports As String
    
    lastRow = Cells(Rows.Count, "F").End(xlUp).Row
    
    For i = 2 To lastRow ' Assuming row 1 is headers
        If UCase(Cells(i, "F").Value) = "X" Then
            reports = reports & "- " & Cells(i, "A").Value & vbNewLine
        End If
    Next i
    
    If reports <> "" Then
        Dim body As String
        body = "Hello, please send the following:" & vbNewLine & reports & "Thanks."
        Dim recipient As String
        recipient = "your_email_address@example.com" ' Change this to your email address
        
        Dim outlookApp As Object
        Set outlookApp = CreateObject("Outlook.Application")
        Dim outlookMail As Object
        Set outlookMail = outlookApp.CreateItem(0)
        
        With outlookMail
            .To = recipient
            .Subject = "Report Request"
            .body = body
            .Display ' Change to .Send to send the email directly
        End With
        
        Set outlookMail = Nothing
        Set outlookApp = Nothing
    Else
        MsgBox "No reports marked with X.", vbInformation, "Report Request"
    End If
End Sub

If this code meets your requirements, please mark the post as "Solved".

Thanks, and good luck!

...Mike

 

[Falloff]

Hi Mike,
don't mind at all, thanks!
Had to tweak it a bit, since I am using LotusNotes but works like a charm.